Display a Negative Number as Positive
With a simple custom format code, we can display negative numbers as positive…but…why would we want to? So that we can simplify our formulas and make our workbooks more reliable of course. Let’s check it out.
Objective
Before we get to the mechanics, let’s confirm our goal. We have a worksheet, in this case a little balance sheet, as shown below.
As you can see, the accumulated depreciation is a negative value. That is, the stored value is a negative number. This means that we can use our friend the SUBTOTAL function to compute the current assets subtotal, the fixed assets subtotal, and total assets.
But, what if we wanted to display accumulated depreciation as a positive value instead of a negative value? Well, if we simply change the stored value to a positive number then our formulas will break, specifically, the SUBTOTAL function that computes the fixed assets subtotal will add depreciation instead of subtract it. We don’t like that option because we LOVE our SUBTOTAL function and don’t want to replace the fixed assets subtotal formula with one that adds and subtracts individual cell references. Is there another option? Yes, and this is why we may want to use a simple custom format code to display a negative stored value as a positive number.
Custom Format
The Format Cells dialog box (Ctrl+1) allows us to format cells with a wide variety of formats. The Number tab includes numerous built-in formats, including Number, Currency, Accounting, Percentage, and many more, as shown below.
At the bottom of the list you’ll find Custom. The Custom option allows us to enter a custom format code. These codes are what you’d expect, for example you could use $#,###.00 to show a currency symbol, a comma as the thousands separator, and force the display of two decimals.
Custom format codes actually support four segments, and, each segment tells Excel how to format different kinds of values. The first segment applies to positive numbers, the second to negative numbers, the third to zero values, and the fourth to text strings. You separate each segment with a semi-colon. For example, here is a format code that tells Excel to format positive numbers with no decimals, and to enclose negative numbers with parentheses.
#,###;(#,###)
Here is a code for formatting negative values with a leading dash.
#,###;-#,###
And, here is how to format negative numbers without parentheses or a dash…that is…as a positive number.
#,###;#,###
Here is how it looks in the Format Cells dialog box.
If we apply this custom format code to our accumulated deprecation cell, we don’t have to change any formulas and we can see the updated balance sheet below.
Our balance sheet still uses the SUBTOTAL function to compute the fixed assets subtotal because the accumulated deprecation value is stored as a negative number but displayed as a positive number. We did it…yay!
Please consult the Excel help system for more information about additional custom format codes. And, if you have any other custom formatting tricks, please share by posting a comment below…thanks!
Additional Resources
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Our training programs start at $29 and will help you learn Excel quickly.
I LOVE these tips. They are often great time-savers for me! I’m a heavy Excel user, a CPA, and I am embarrassed to say that I it seems I have been ignorant about much of the functionality of Excel. Can’t thank you enough!
Welcome 🙂
This is great to know! Can a formula like this be used for the opposite – showing positive numbers as negative?
Sue – yes, absolutely! The format code for that would be (#,###);(#,###), or, -#,###;-#,###.
Thanks,
Jeff
I have learnt something new today. Thanks Jeff!
Hi Jeff,
I was searching gross sum (negative balance should also need to add in total) & I found this article.Great tip.Also your blog is also awesome…
Thanks,
Utkarsh
Thx 🙂
Jeff,
How can this custom format be applied so that zeros are displayed as a dash that is centered like the accounting format? By using # , ### ; # , ### ; – I get the dash aligned to the right.
Thanks,
Tom J
If you’d like the dash to be placed in the same position as the accounting format (towards the middle of the cell), you can update the custom format as follows:
#,###;#,###;- ??
There are other formatting codes used in the accounting number format, and they easy to reverse engineer by opening the Format Cells dialog, selecting a built-in Accounting format, and then clicking the Custom category. The codes used in the selected format are displayed, and then you can tweak as needed.
Hope this helps!
Thanks
Jeff
Man…disregard…I’m so STUPID (Chris Farley head jerk). Accounting format aligns to the right! I just need to add a space after the custom formating and you can’t tell the difference.
Tom…that is funny man! And glad you got it!
Thanks
Jeff
I have a negative number in a cell, like -1.232 , and I want to use custom format cell to make it look like B=-1.232, but when I type “B=” #.### (number in that cell can be positive and negative) the minus sign goes in front of B like -B=1.232. Can you help? Thank you
Hey Mirko
Yes this turned out to be a pretty easy fix:) The number format is “positive;negative;zero” so when you type in your code, that just told Excel what to do with positive numbers. All you need to do is repeat it so it works the desired way for +/- numbers: “B=”#,###;”B=”-#,###
Let me know how that works out for you,
Kurt LeBlanc
Jeff,
My EBITA has turned negative, however it shows positive b/c of the formatting. How to fix? Changing the formatting to #,###, (#,###) or Accounting Format did not resolve it.
Thanks,
Tom
Hey Tom
You simply have the formats separated by a comma. It should be a semi-colon.
Try that and let me know,
Kurt LeBlanc
Thanks Kurt
The Format Cells box actually does have the semi-colon.
Hey Tom
So were you able to fix the issue with the negative EBITDA?
Kurt LeBlanc
Not yet. It still appears as if we made a profit.
I’m not sure if the issue results from the SUBTOTAL formula or the formatting.
Tom
It’d be the formatting…Try “$#;($#)”
It’s positive semi-colon negative semi-colon zero for the custom number format, so you can write your own formats.
Let me know if that helps,
Kurt LeBlanc
It’s been a while since the last comment, so I’m hoping for the best!
Is there a way to format cells to show “0” in place of a negative number? I’m creating a sheet for employees, basically to show commissions/spiffs earned off jobs. Obviously on a job with no profit or with a loss, there would be no earning- so I would like it to automatically change to 0.
Marcia … yes, and to do so, just use a code of 0, for example, a custom code like this:
#,###;0
Hope it helps, thanks!
Jeff