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