For all Excel's marvelous ability to arrange, relate, and slice and dice data, it can be remarkably difficult about displaying the results according to your preferences. One confounding problem is getting Excel to ignore any negative figures and simply call them "zero," even though in real-world applications, sometimes that's all negative figures really mean. There are two ways to make Excel display "0" instead of any negative number.
In Formulas
Video of the Day
Step 1
Write a formula to evaluate data. For example, if you're subtracting A1 from B1 and showing the results in C1, type "=(B1-A1)", minus the quotes, into cell C1.
Video of the Day
Step 2
Add "MAX" to the formula to designate the maximum answer allowed: "=MAX(B1-A1)". This is only part of the formula; if you press "Enter" at this point, you will get an error message.
Step 3
Insert the amount for the maximum number allowed as the formula result -- in this case, zero: "=MAX(0,B1-A1)". Any results that would normally be negative become 0, not just as display text, but as value. If the formula result of B1-A1 is -2, once you use the MAX function, it actually becomes zero, and if you add 5 to it, the result will be 5, not 3.
Formatting
Step 1
Enter all data as usual, including negative numbers, or open a worksheet containing the data you want to use. Select all the cells you want to display only as positive numbers or zeros.
Step 2
Click on the number format drop-down and choose "More Number Formats…."
Step 3
Choose "Custom" in the left-hand pane.
Step 4
Type "##;"0";0", without the outer quotation marks, into the field labeled "Type:", overwriting any symbols already in that field. Click "OK" to return to the spreadsheet, where all negative numbers will now display instead as "0" while retaining their actual values. If a cell value is -2, it will display as 0, but if you add 5 to it, it will become 3, not 5.