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.

Sample Balance Sheet by Jeff Lenning

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.

Format Cells dialog by Jeff Lenning

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.

Custom Format by Jeff Lenning

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.

Updated Balance Sheet by Jeff Lenning

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

This article was written by Jeff Lenning

19 comments:

  1. Donna Smith
    Reply

    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
      Reply

      Welcome 🙂

  2. Sue Bell
    Reply

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

    1. jefflenning Post author
      Reply

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

  3. Lalith Kumar Nimmala
    Reply

    I have learnt something new today. Thanks Jeff!

  4. Utkarsh
    Reply

    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
      Reply

      Thx 🙂

  5. Tom J
    Reply

    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
      Reply

      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
    Reply

    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
      Reply

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

  7. Mirko
    Reply

    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
      Reply

      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
    Reply

    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
      Reply

      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
        Reply

        Thanks Kurt

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

        1. Kurt LeBlanc
          Reply

          Hey Tom

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

          Kurt LeBlanc

  9. Tom
    Reply

    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
      Reply

      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

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.