Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Getting Rid of Negative Zero Amounts.

# Getting Rid of Negative Zero Amounts

Written by Allen Wyatt (last updated March 26, 2021)
This tip applies to Excel 2007 and 2010

When you are creating a worksheet, and you format your cells to display information just the way you want, you may notice that you end up with "negative zero" amounts. Everyone learned in math classes that zero is not a negative number. So why does Excel show some zero amounts as negative?

The reason is because your formatting may call for displaying less information than Excel uses internally for its calculations. For instance, Excel keeps track of numbers out to fifteen decimal places. If your display only shows two decimal places, it is possible that a calculated value could be very small, and when rounded show as zero. If the calculated value is something like –0.000001325, then the value would show with only two digits to the right of the decimal point as –0.00. The negative sign shows, of course, because the internal value maintained by Excel is below zero.

There are a couple of ways you can solve this problem. The first is to simply round the calculated value to the desired number of decimal places. For instance, assume that this is your normal formula—the one that results in the "negative zero" values:

```=SUM(A3:A23)
```

You can round the value in the cell by simply using the following formula instead:

```=ROUND(SUM(A3:A23),2)
```

This usage results in the value being rounded to two decimal places. In this way you should never end up with another "negative zero" value.

Another solution preferred by some people is to force Excel to use the same internal precision as what you have displayed in your worksheet. Just follow these steps:

1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 display the File tab of the ribbon and click Options.)
2. At the left side of the dialog box click Advanced.
3. Scroll through the list of options until you can see the When Calculating this Workbook group. (See Figure 1.)
4. Figure 1. The advanced options of the Excel Options dialog box.

5. Make sure the Set Precision As Displayed check box is selected.
6. Click on OK.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9901) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Getting Rid of Negative Zero Amounts.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Changing Page Number Format

Need your page numbers to not appear as regular Arabic numerals? Here's a way to get them to appear in a different ...

Discover More

Drawing Simple Objects

Want to draw a few simple shapes or lines on your worksheet? It really is simple; here's how to do it.

Discover More

Changing Page Number Format

Need your page numbers to not appear as regular Arabic numerals? Here's a way to get them to appear in a different ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

##### More ExcelTips (ribbon)

Applying Table Formats

Want to format your data tables in a hurry? It's easy to do if you use the built-in table formatter provided in Excel.

Discover More

When you use the sorting tool, Excel tries to automatically figure out if your data includes a header row or not. Here ...

Discover More

Setting Vertical Alignment

Excel allows you to adjust not only the horizontal alignment of values in a cell, but also the vertical alignment. This ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 4 + 0?

2022-01-22 14:31:13

J. Woolley

My Excel Toolbox includes the PrecisionAsDisplayed() function to return the status of a workbook's calculation precision (TRUE if 'AsDisplayed' or FALSE if 'Standard'). For example:
="Precision is "&IF(PrecisionAsDisplayed(),"'AsDisplayed'","'Standard'")
My Excel Toolbox also includes the TogglePrecision macro to switch between the two possibilities.
Finally, the PrecisionToggleLink([Friendly_Name],[Screen_Tip]) function uses SuperLink to create a hyperlink that runs TogglePrecision when triggered. Optional Friendly_Name will be displayed; default is "Toggle Calculation Precision". The PrecisionAsDisplayed() function can be part of Friendly_Name and/or Screen_Tip. For example:

2022-01-19 15:25:32

Gary

This makes sense -IF- I was entering numbers with more than 2 digits of precision or if I was doing a calculation that might result in more digits, but I am not. For example, enter the following numbers:

7.46
16.06
17.79
17.23
17.81
17.25
17.83
17.84
17.28
17.87
17.30
17.89
10.39

Then, sum all 13 numbers (210.00), subtract 199.61, then subtract 10.39. That should result in 0, but instead, Excel returns -1.42109E-14 or -.00000000000001421085. Why is Excel storing and using precision that was never entered? I'd like to trust that 2+2 = 4, but with Excel, one can never be sure that is the case. This is a bug. Interestingly, if you just subtract 210 (as opposed to 199.61 and 10.39), you get 0. Both calculations should return zero!

2021-03-26 10:01:27

David James Bonin

I've done something similar for a similar reasons, but in an entirely different application.

On some of the scorecards I produced, I show goal and result values.
- If the result meets the goal, I shade the result cell green.
- If the result is within, say, 10% of goal, I shade the result cell yellow.
- If the result is more than 10% short of goal, I shade the result cell red.

Depending on my spreadsheet, I may do the shading using conditional formatting or VBA.

For my readers' ease of use and to forestall questions about errors, I round goals and results to only the number of digits displayed before I compare them. That means the shading is applies based on the number displayed, and not the number in full precision.

Why do I do this? Because if the goal is exactly 2 (displayed as 2.00) and the result is 1.999820 (also displayed as 2.00), the result essentially meets the goal and I want it to be shaded green. Otherwise I get questions from readers about why a result that looks like 2.00 is shaded as if it isn't 2.00.

2020-05-27 16:07:12

Donald

Thanks ! This was very helpful.

2019-08-23 12:05:11

BrianM

Thanks for resolving this 'problem' of negative zeros! I've tried all sorts of approaches, none of which were simple, and I wouldn't want to use a convoluted number format. Strangely (to me!) the amounts I was using were all simple currency amounts, personally input with no trailing zeros, yet a calculation of A1-B1 which produces a zero answer often display this as a negative, i.e. -0.00. (But sometime it didn't!)

Evidently there were a few trailing zeros somewhere, so your 'rounding' solution seems to be ideal.

Thanks again.

2019-01-14 08:26:33

Lew

Thank you, Allen, for helping to solve what was an annoying problem. Your article is excellent.

2015-10-04 10:29:59

Joe

A quick word to the wise about using "Set Precision As Displayed". I was annoyed with some calculations not giving true zero on one of my cost tracking spreadsheets, so I turned on "Set precision as displayed", with formatting set to show 2 decimal places and all was good. However a couple of months ago I opened the spreadsheet and all the decimal parts of my costs (the pence) had reset to zero (even when looking in the formula bar). I still don't know if this was a bug or I did something silly (quite possible), but I'm sure it is connected with having turned on "Set Precision as Displayed" as it is the only way I know of for Excel to lose this level of precision. Fortunately I had backups which meant I could rebuild the sheet, but it took a couple of hours to move the later additions over to the backed up sheet. I now just round the results of calculations to 2 decimal places instead.

2015-09-29 21:13:27

Neil

Sir Andrew,

This comes about as the calculation is either exactly 0.0000000000 so you get the hyphen or if it is say 0.000000001 then will be displayed as 0.00 if you are using the standard comma style format.

The simplest fix is to use a round function, for example =round(value, 9). I usually use round to a large number of decimals as it can be useful to see 0 as it may indicate an error (say if the result is 0.1 and I am expecting 0.00000).

The issue mainly occurs when you are subtracting - is often caused by numbers being exact in decimal but in binary it may not be. For example 0.1 in decimal when written as binary is 0.00011001100110011001100110011001100110011001100110011001100110...

This can be an issue across many applications - not confined to Excel.

2015-09-29 11:59:10

Sir Andrew Thomas

This is slightly related, but still different. I have the experience that zero results of one or another calculation, including additions and subtractions, show sometimes as a hyphen and sometimes as a zero, even though the cells are all formatted the same. Does anyone know why or how to fix this? Is this the result of their being some amount way past the decimal as in the above article?

2015-09-29 10:34:28

E-Ted-E

Micky A: you didn't go wrong. That is what checking the "Set Precision as Displayed" function does. All values are "remembered" by Excel as exactly the value(s) shown. So the value that had been negative .00000123 is now set to be zero, which is not negative.

NOYB: you are way too "absolute" in your opinion. There are valid reasons some values are expressed in specific levels of "rounding". Briefly, the values must not imply a level of precision not supported by the data &/or by the equipment.
If you measure a board as 4.4 cm by 2.3 cm, the area of the board is 10.1 sq-cm. To say it is 10.12 sq-cm suggests you are sure it is not 10.14, nor 10.10 - which you do NOT know, as your tools are not that precise.

2014-08-08 08:05:13

Bryan

Ok I wrote up a whole big rant about how you were wrong, and then I realized you weren't talking about *significant digits*! (Precision has never been relevant for any of my work, since any calculators or programs I've worked with have dealt with it well enough to not cause problems; significant digits, however, are trickier to deal with in Excel, and are something I've been concerned with more).

I still think you are wrong though, just in a different way.

Excel already does the math the way you think it should. In fact, it does one better: it keeps 15 significant digits for any calculation. The only thing it doesn't do is DISPLAY the number of digits you think it should... except when it does. Try typing "=3.0403 + 0.45" into a cell formatted as General and see what displays. 4 decimals, right? Now type "=3.0403 * 0.45" into a General cell -- you'll get the proper 6.

Now there are some cases where it won't *show* all the decimals. It's hard to repeat but there's some sort of algorithm that starts to round when the number of decimals exceeds the width of the cell, but it won't mess up your calculations. Make the format "0.0" or "#,##0", or even "Elephants", and perform the same test as above. You will see a different number (3.5/1.4, 3/1, and Elephants/Elephants, respectively), but now reference that cell in another calculation in another cell: you get the correct result!

In conclusion, I have no idea what you are talking about when you say that Calculator works better than Excel. Even if I did, saying MS can't figure it out doesn't make sense, since they make both programs...

2014-08-07 16:08:48

NOYB

Regarding addition, subtraction, and multiplication there is one and only one correct result precision.

Addition and subtraction always results in exactly the same decimal point precision as that of the operand that has the greatest precision. Period. No if's and or buts.

Multiplication always results in exactly the decimal point precision of the sum of the precision of the operands. Period. No if's and or buts.

Any application that produces results with precision level other than this is just plan wrong and injecting errors into calculations. Especially when those results are also used as is in more downstream calculations. Continuing to compound the error.

It is pathetic that a company such as Microsoft cannot do this correctly in a business class product such as Excel after all these years.

Even The lowly Windows Calculator can do this correctly.

The very least Microsoft could do is provide an option setting and/or function to produce result precision for these operators accordingly.

Can't believe people in the business, and especially engineering disciplines are so accepting of this and willing to always have to be working around it. It's pathetic mathematical nonsense.

2014-08-06 07:11:07

Bryan

NYOB: That sort of functionality would be a complete change to how precision is done, and since every discipline does precision in a different way, if MS picked one, many people would complain that the wrong one was picked.

The fact of the matter is, if the value in the cell is -0.0000123 and Excel shows -0.0000, this isn't "wrong", just somewhat counterintuitive if you don't understand what's going on.

That being said, I agree with Micky; I can't reproduce the error when using "Number" format. If you use a custom formatting of "0.00000;-0.00000;0.00000", then I get the behavior Allen described... but that's because I basically told Excel to give me the "error".

2014-08-05 15:46:47

NOYB

Microsoft could alleviate a significant part of this issue by checking the operator and applying some simple rules accordingly.

If the operator is add or subtract then the result precession should the same as the operand that has the greatest precession. For instance. The precession of 3.0403 + 0.45 should be 4 decimal places.

If the operator is multiply then the result precession should be the sum of the precession of the operands. For instance. The precession of 3.0403 x 0.45 should be 6 decimal places.

I'm sure a math wizard could figure out appropriate rules for other operators as well.

Even the lowly Windows calculator knows better than to display zero as a negative value. Try it with some of your Excel calculations that result in a negative zero.

2014-08-03 11:38:37

Michael (Micky) Avidan

This is very strange ...
I just checked the above, on four versions of 'Excel' ["2003/2007/2010/2013"] Whilethe 'Set Precision As Displayed' check box is NOT selected.
Summing a range of cells ended up with: -0.00000123 and after reducing the amount of decimals to 5 - it displayed a value: 0.00000 without(!) Minus sign.
What or where did I went wrong ?
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

##### This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.