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.
Note: We can also avail of the Format Cells using the keyboard shortcut Ctrl+1.
Read More: How to Add Negative Numbers in Excel
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.
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.
Read More: How to Put a Negative Number in Excel Formula
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.
Read More: Excel Formula to Return Zero If Negative Value is Found
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.
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.
![Negative Numbers Formatted by VBA](https://www.exceldemy.com/wp-content/uploads/2022/08/Negative-Numbers-Formatted-by-VBA.png)
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.
Hopefully, this will solve your problem of negative numbers not showing in the Excel file.
Read More: How to Make a Group of Cells Negative in Excel
Download the Practice Workbook
You can download the practice book using the button below.
Related Articles
<< Go Back to Negative Numbers in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!