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.

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!

Another Approach

You can change the negative numbers to positive numbers with following steps:

1. Enter number -1 in a blank cell, copy it, then select the negative numbers, if the negative cells are not adjacent, select the first negative one, then hold Ctrl key, and select the others.
2. After selecting all of the negative numbers, Right-click to pop out the right-click menu and click Paste Special... from the menu.

See screenshot:

And a Paste special dialog box will be displayed, select All option from Paste, select Multiply option from Operation, click OK. See screenshot:

The all selected negative numbers will be converted into positive numbers. Delete the number -1. See screenshot:

Using VBA code to convert all negative numbers of a range to positive

As an Excel professional, also you can run the VB code to change the negative numbers to positive numbers.

1. Select the range which containing the negative numbers you want to convert into positive, then click Developer > Visual Basic.
2. There will be a new window displayed. Click Insert > Module, then input the following codes in the module:

Sub Positive
Dim Cel As Range
For Each Cel In Selection
If IsNumeric(Cel.Value) Then
Cel.Value = Abs(Cel.Value)
End If
Next Cel
End Sub

The above code use the ABS function to reverse the value of the cells selected. Then click RUN button to run application, and all the selected negative numbers will be changed into positive numbers.