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

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

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.

21 Comments

  1. Donna Smith on June 13, 2015 at 4:49 am

    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!

    • jefflenning on June 13, 2015 at 6:23 am

      Welcome 🙂

  2. Sue Bell on June 15, 2015 at 11:32 am

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

    • jefflenning on June 19, 2015 at 12:56 pm

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

  3. Lalith Kumar Nimmala on August 23, 2015 at 9:58 pm

    I have learnt something new today. Thanks Jeff!

  4. Utkarsh on January 3, 2016 at 8:46 am

    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

    • jefflenning on January 3, 2016 at 9:38 am

      Thx 🙂

  5. Tom J on January 21, 2016 at 10:39 am

    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

    • jefflenning on January 21, 2016 at 10:47 am

      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 on January 21, 2016 at 11:04 am

    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.

    • jefflenning on January 21, 2016 at 11:07 am

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

  7. Mirko on October 26, 2016 at 3:12 am

    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

    • Kurt LeBlanc on October 26, 2016 at 7:10 am

      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 on November 2, 2016 at 10:14 am

    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

    • Kurt LeBlanc on November 2, 2016 at 11:26 am

      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

      • Tom on November 2, 2016 at 11:48 am

        Thanks Kurt

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

        • Kurt LeBlanc on November 3, 2016 at 6:02 am

          Hey Tom

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

          Kurt LeBlanc

  9. Tom on November 3, 2016 at 6:38 am

    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.

    • Kurt LeBlanc on November 3, 2016 at 6:44 am

      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

  10. Marcia on October 4, 2017 at 2:22 pm

    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.

    • Jeff Lenning on October 4, 2017 at 2:26 pm

      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

Leave a Comment