How to Show Negative Numbers in Excel (5 Ways)

Method 1 – Using Parenthesis

STEPS:

Select the data range containing negative numbers.
Click on the Number Format icon in the Number group.

As a result, the Format Cells dialog box will appear.
In the Format Cells dialog box:
- Choose a Number from the Category.
- Select the option with parentheses.
- Click OK.

Look at the dataset. All the negative numbers are in parentheses.

Negative Numbers Showed with Parenthesis

Note: We can also avail of the Format Cells using the keyboard shortcut Ctrl+1.

Method 2 – Using Custom Formatting

STEPS:

Select the cells containing negative numbers.
Press Ctrl + 1 to open the Format Cells dialog box.
Select Number tab > Category > Custom.
Insert the target custom format in the Type box. I entered $#,##0_);[Magenta]($#,##0) to show the negative numbers in brackets and color them Magenta.
Click OK.
All the negative numbers inside the dataset are in parentheses and colored in Magenta.

Negative Numbers Shown in Different Font Color and Currency Format

Note: In $#,##0_);[Magenta]($#,##0) custom format, 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. 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.

Method 3 – Using Accounting Formatting

STEPS:

Select the whole data range containing negative numbers.
Click on the Home tab > Number group > Accounting Number Format icon.

Now you can see the negative numbers are inside the first bracket in the Accounting format.

Negative Numbers Showed in Accounting Format

Method 4 – Applying Conditional Formatting

STEPS:

Select the data range first.
Select Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog box, select the rule type Format only cells that contain.
Edit the rule description as follows:
- For the 1st empty field, select Cell Value from the drop-down list.
- For the 2nd empty field, select less than from the drop-down list.
- For the 3rd empty field, put 0 in it.
Click on the Format button to open the Format Cells dialog box.
In the Format Cells dialog box:
- Go to the Font tab.
- Set a Font Style and Color of your choice. Here, I have selected an Italic font style and Red color.
- Click OK inside the Format Cells dialog box.
Click OK inside the New Formatting Rule dialog box.
The negative numbers in the data range are shown in the italic font style and red color.

Here’s an image showing that if any numbers in the range are inserted as negative, it will be automatically formatted.

Automatic Output for Conditional Formatting

Method 5 – Using VBA

5.1 Using VBA Subroutine

STEPS:

Open the VBA window by pressing Alt + F11 on your keyboard.
Inside the VBA window, select Insert > Module.

Enter the code below inside 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

Click on the Run Sub button.

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

5.2 Assigning VBA Macro to a Button

STEPS:

Select Developer tab > Insert.
Choose Form Controls > Button.
Drag a rectangle somewhere in the Excel sheet.
The Assign Macro window will pop up.
Select the Macro you want to run with this button and click OK.
Give a name to this button. In this case, the assigned name is Display Negative Numbers.
Select the data range with numbers (C5:E10).
Click on the Button.
You will see the negative numbers in red font.

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

Although Excel can show negative numbers with brackets, it may occur to some users that the negative numbers cannot be shown with parentheses in Excel. The reason is that the ($1,234.10) option isn’t available.

STEPS:

Open the Control Panel.
Choose the Change date, time, or number formats.
The Region window will pop up.
Inside the Region window, select Additional settings.
A new window named Customized Format will pop out.
Inside the Customized Format window:
- Select the format (1.1) for the Negative number format.
- Click OK.
Close the Excel file and reopen it.