How to Show Negative Numbers in Excel (5 Effective Methods)

In this article, we are going to cover some easy and effective tricks on how to show negative numbers in Excel. We can simply use negative numbers for our calculation in an Excel sheet, but sometimes it’s the users’ preference to show negative numbers in a different way.

Any number which is less than 0 (zero) is a negative number. Usually, this type of number refers to decreasing or lowering in quantity. So, we use negative numbers to show loss in business or decrease in temperature etc. Negative numbers have a huge usage in practical life.

In the following sections of this article, you will see some different ways to show negative numbers in Excel. You can see a preview of how you can show negative numbers in an Excel sheet in the following image.

Here, we showed the negative numbers with red font color.

Formatting Negative Numbers

Negative numbers are described usually by putting a negative sign (-) at the beginning of a number in an Excel workbook. But Excel also allows you to format the negative numbers on the basis of how you wish to show them on your worksheet. While entering negative values on the worksheet, you just have to put the (-) sign at the start.. And then you can change it as per your choice like parenthesis and color.

How to Show Negative Numbers in Excel: 5 Effective Methods

In the dataset, you will see cash amounts in USD regarding some financial statements like Initial Balance, Operating Activities, Investing Activities etc for three months. The dataset contains some negative values as you’ve seen in the introduction. Let’s go through the different methods of showing negative numbers differently.

1. How to Show Negative Numbers in Excel with Parentheses

There are some built-in formats in Excel to show negative numbers differently. One of them is showing them with parentheses.

To show negative numbers with parentheses,

• First, select the data range containing negative numbers.
• After that, click on the drop down icon in the Numbers group (Marked as 2).

• After the Format Cells window appears, select Number.
• You can see some options for Negative Numbers. Select the one with parentheses. Other formats can also be selected as per users’ preference.
• Next, Click OK.

Finally, you can find all the negative numbers are in parentheses (highlighted with light blue-gray color). Thus you can show negative numbers with parentheses.

Read More: How to Add Negative Numbers in Excel

2. Custom Formatting Negative Numbers to Differentiate from Other Numbers

We can add brackets to negative numbers and make the negative numbers red by using default formatting options. Moreover, We can also apply Custom Formatting to show negative numbers differently. Let’s have a look at the procedure below.

Excel offers 8 different color supports for user-defined number types. The names of these colors are: [Black], [White], [Red], [Green], [Yellow], [Magenta], [Cyan] and [Blue]. Color names need to be enclosed in brackets.

So select Custom and copy the code below in the Type section.

After that, click OK. You can find other codes to format the negative numbers.

Note: The part before the semicolon in the code is to format the positive numbers and the other one after the semicolon is to format the negative numbers.

Read More: How to Count Negative Numbers in Excel

3. How to Show Negative Numbers in Excel with Accounting Format

We can also show negative numbers differently using the Accounting Number format. This can be done within a moment.

To convert the numbers in the dataset to Accounting Number Format, just select the data range with numbers and select this formatting from the Number group. You can also find this formatting from the drop down options of the Number group.

The negative numbers are shown differently by the Accounting Format (cells highlighted with light blue-gray color).

Read More: Excel Formula to Return Zero If Negative Value is Found

4. Applying Conditional Formatting on Negative Numbers

Conditional Formatting offers a variety of options to format cells in an Excel sheet based on conditions. In this section, we will format negative numbers using this feature.

To initiate Conditional Formatting feature with a new rule, follow the instructions below.

• Select the data range first.
• Next, select Home >> Conditional Formatting >> New Rule.

After that the New Formatting Rule window will appear. Please follow the video below to understand the process that has been done to format the negative numbers.

The following steps were taken to format negative numbers with a color and a font style.

• First, select the Rule type ‘Format only cells that contain’.
• Next, Edit the Rule Description. As we want to format the negative numbers, we Format only cells that have values less than zero.
• After clicking on the Format button, set a Font Style and Color of your choice. You can also format the cells containing negative numbers with a Fill Background

Finally, the negative numbers in the data range will be shown with the Italic Font Style and Red color.

Here’s another video showing that if any of the numbers in the range is inserted as negative, it will be automatically formatted.

5. Using VBA to Change Font Color of  Negative Numbers

Excel VBA can also be an effective tool to show negative numbers with fancy fonts and color. First, we need to open the VBA window. Press Alt + F11 to open it.

• When the VBA window opens, select Insert >> Module to open a VBA Module.

• Copy the code below in the Module.

Sub ShowNegativeNumbers()
Dim mn_rng As Range
For Each mn_rng In Selection
    If mn_rng.Value < 0 Then
        mn_rng.Font.Color = vbRed
    Else
        mn_rng.Font.Color = vbBlack
    End If
Next mn_rng
End Sub

The Macro returns the Red color font for the negative numbers from a selected range that contains a set of numbers. Positive numbers and zero will be shown in Black. A VBA If Statement is used to identify a number whether it’s positive or negative.

• Now, go back to your sheet, select the data range containing numbers (C5:E10) and press Alt + F8 to open the Macro
• Select the Macro named ShowNegativeNumbers as it is our current Macro and Run.

After running the Macro, you will see the negative numbers in the Red color font.

You can also assign this VBA code to a button to operate this code conveniently. Please follow the video below to see how to assign a code to a button.

The following steps are shown in the video:

• Select Insert from the Developer
• Next, choose Button from the Form Controls
• Drag a rectangle somewhere in the Excel sheet and then the Assign Macro window will pop up.
• Select the Macro you want to run with this button and click OK. In this case, the name of the Macro is ShowNegativeNumbers.

Now, give a name to this button and follow the video to see the procedure of using this button.

Video: Using Button to Show Negative Numbers with Color

We did the following commands in the video.

• First, select the data range with numbers (C5:E10).
• Next, just click on the Button. The name of our button in this case is Display Negative Numbers.

Thus, you can easily show the negative numbers differently using Excel VBA.

How to Convert Negative Number to Positive in Excel

Sometimes, we may need to ignore negative values to show the magnitude of a number. There are several ways to do that, but we will use the ABS function for this task.

To convert these negative numbers, we copied the data table excluding the number values. Then we inserted a formula beside the Initial balance mark, pressed Enter and dragged the Fill Handle icon to AutoFill the lower cells.

The ABS function returns the distance of any number from zero in the number line. Hence, it converts any number both positive and negative to positive.

Thus, you can convert negative numbers to positive using the ABS function.

How to Make All Negative Numbers Zero in Excel

It may be required to ignore negative values or portray them as no result in a dataset. So we use a formula to return zero if the number is negative in that case. The function that will help us to do this job is the MAX function.

To convert these negative numbers, we copied the data table excluding the number values. Then we inserted a formula beside the Initial balance mark, pressed Enter and dragged the Fill Handle icon to AutoFill the lower cells.

The MAX function returns maximum values between multiple values or a set of numbers. Here, we compare 0 with the other data of the table. As negative numbers are less than 0, the MAX function returns 0 whenever it finds a negative number.

Thus, you can convert negative numbers to zero using the MAX function.

What to Do When Negative Numbers Aren’t Showing with Parentheses in Excel

Excel can show negative numbers with brackets but It may occur to some users that the negative numbers cannot be shown with parentheses in Excel. The reason for that is the ($1,234.10) option isn’t available. To solve this problem, follow the steps below.

• Open the Control Panel.

• Choose the Change date, time, or number formats.

• After that, the Region window will pop up, select Additional settings… from this window.

• From the Customize Format window, select the format (1.1) for the Negative number format.
• Click OK.
• Close the Excel file and reopen it.

Hopefully this will solve your problem of negative numbers not showing in the Excel file.

Things to Remember

While working on the methods, make sure you follow some instructions below.

• The numbers should not be in Text.
• You cannot type the name of any color while Custom Formatting the negative numbers.
• Select the data range while applying the VBA.

Download Practice Workbook

You can download the practice book from the button below.

Conclusion

In a nutshell, we can conclude that you will learn some easy and simple tricks to show negative numbers in Excel differently so one can identify them. To present negative numbers in a different way, we used the Number Format feature, Conditional Formatting, VBA, etc. If you have any questions or feedback regarding this article, please share them in the comment section.

Related Articles

• How to Change Positive Numbers to Negative in Excel
• [Fixed!] Excel Not Adding Negative Numbers Correctly
• How to Apply Formula for Positive and Negative Numbers in Excel
• How to Make a Group of Cells Negative in Excel
• How to Put a Negative Number in Excel Formula
• Excel Formula If Cell Contains Negative Number
• Excel Formula to Return Blank If Cell Value Is Negative
• How to Sum Negative and Positive Numbers in Excel
• How to Convert Negative Value to Positive in Excel Using Formula
• Excel Negative Numbers in Brackets and Red