# 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!

1. Donna Smith

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!

1. jefflenning Post author

Welcome 🙂

2. Sue Bell

This is great to know! Can a formula like this be used for the opposite – showing positive numbers as negative?

1. jefflenning Post author

Sue – yes, absolutely! The format code for that would be (#,###);(#,###), or, -#,###;-#,###.
Thanks,
Jeff

3. Lalith Kumar Nimmala

I have learnt something new today. Thanks Jeff!

4. Utkarsh

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

1. jefflenning Post author

Thx 🙂

5. Tom J

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

1. jefflenning Post author

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

6. Tom J

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.

1. jefflenning Post author

Tom…that is funny man! And glad you got it!
Thanks
Jeff

7. Mirko

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

1. Kurt LeBlanc

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

8. Tom

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

1. Kurt LeBlanc

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

1. Tom

Thanks Kurt

The Format Cells box actually does have the semi-colon.

1. Kurt LeBlanc

Hey Tom

So were you able to fix the issue with the negative EBITDA?

Kurt LeBlanc

9. Tom

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.

1. Kurt LeBlanc

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