Accounting differs depending on locations, industries, and professions. In some areas, the appropriate way to display negative numbers is in brackets. However, Excel has its default way of displaying negative numbers which may not match your style. Luckily you can change or customize how to show negative numbers in an an Excel spreadsheet.
In this tutorial, I will show you different ways in which you can display negative numbers in parentheses in an Excel
Display negative numbers in brackets using Excel Formatting
Excel has predesigned formats for negative numbers. Luckily, some of the predesigned number formats include negative numbers in brackets. Here are the steps to follow.
1. Highlight the cells or the range in which you wish to display negative numbers in brackets
2. Right-click on the selection and select format cells (You can also use the shortcut ctrl+1 to open the format cells menu)
3. Click on the number under the number tab
4. Select an option that suits your need for negative numbers from the list
5. Click on okay to apply changes
How to display negative numbers in brackets and in red
Displaying negative numbers in parentheses is cool. But adding red color makes it more cooler and more distinct. This means users can easily differentiate between the positive and negative numbers.
1. Select the cells
2. Right-click on one cell in the selection and click on format cells
3. Under the number Tab select Number
4. Select the option with a negative number in brackets but in red.
5. Press okay to apply changes
By using a custom code to display negative numbers in brackets
There is a custom code that you can use to display negative numbers in Excel in Brackets.
1. First select the range or cells containing negative numbers.
2. Right-click on any number within the selection and select format cells
3. Select Custom under the number tab
4. Type the following code under the section written type
#,##0.00;(#,##0.00)
5. Press OK to apply the changes
As you can see from the image above the custom code displays the selected negative numbers in brackets.
Using VBA code
1. Enable the Developer tab if not already visible: Go to File > Options > Customize Ribbon and check the box next to “Developer”.
2. Access the Visual Basic Editor (VBE): Click on the “Developer” tab, then click “Visual Basic” to open the VBA editor.
3. Insert a Module: Right-click on any existing module or in the Project Explorer pane, select Insert > Module.
4. Copy and Paste the Code: Copy the code below and paste it into the newly inserted module.
Sub DisplayNegativeNumbersInBrackets()
Dim cell As Range
For Each cell In Selection
If IsNumeric(cell.Value) Then
If cell.Value < 0 Then
cell.NumberFormat = "#,##0_);(#,##0)"
End If
End If
Next cell
End Sub
5. Close the VBA Editor and return to Excel.
6. Select the Range where you want the formatting applied.
7. Run the Macro: Go to the “Developer” tab, click “Macros,” select “DisplayNegativeNumbersInBrackets,” and click “Run.”
Check the Result: The selected range should display negative numbers in brackets according to the specified format.
Download the Excel Practice Sheet
Change negative number formats in Windows
If you want negative numbers to appear in parentheses not only in Excel but all other programs then you need to adjust this setting at the Windows level. Here is how to do it.
1. Open the control panel. (Search the word control panel then click it from the list of options.)
2. Select the clock, and region setting (In some windows it may be labeled Clock, Language, and Region)
3. Click change, date, time, or number formats.
4. Next, click on the additional settings button
5. Click on the negative number drop-down and select the option in the brackets
6. Click on the currency tab and apply a similar setting to the negative currency format setting
7. Click apply then ok to apply the changes.
8. Lastly restart Excel. You will notice that from now on the negative numbers appear in brackets by default.
Final Thoughts
You can display negative numbers in many different ways thanks to Excel’s advanced customization. If you are required to display it in brackets or in brackets and red then this tutorial has covered comprehensively on how to do it. If you are an advanced user you can try using the VBA method that we have discussed. However, the rest methods of formatting cells can be done even by a complete beginner.