Since Excel is used by people in different professions and across industries, Excel allows you to show numbers in many different formats.
When working with financial data and creating accounting reports, one common number format that needs to be used is to show negative numbers in brackets (parentheses).
While the default setting in Excel is to show negative numbers with a minus sign, it’s quite easy to change the format to show negative numbers in brackets/parenthesis.
In this tutorial, I will show you different ways to show negative numbers in brackets. I’ll also cover all the other formatting options that are available to you (such as adding or removing the – or changing the color of the negative or positive numbers)
This Tutorial Covers:
ToggleShow Negative Numbers in Brackets Using In-Built Formats
Excel already has some inbuilt number formats that will show the negative number in brackets.
Below I have a data set where I have the profit values for different stores in column B, and I want to show the negative values in brackets.
Here are the steps to do this:
- Select the cells that have the numbers (you can select the entire range of cells, not just the negative numbers)
- Click the ‘Home’ tab
- In the ‘Number’ group, click on the dialog box launcher icon (small arrow at the bottom right part of the group)
- In the ‘Format Cells’ dialog box, within the ‘Number’ tab, select ṭhe third option in the Negative Numbers list (the one that shows the number in the bracket as is in black color)
- [Optional] Specify the decimal numbers you want to show. In case you don’t want decimal numbers, make the value 0 (it’s 2 by default)
- Click OK
The above steps would change the format of the cells so that the negative numbers are now shown within parentheses (as shown below).
In the ‘Format Cells’ dialog box, you also get the option to have the negative numbers in brackets as well as change the color of these negative numbers to Red.
If you want to show negative numbers in red color within brackets, select the fourth option in step 4.
There is a possibility that you may not see the same ‘Negative numbers’ formatting options as I’ve shown in the screenshots above. Some systems have settings where it does not show the option to format negative numbers to show them in brackets. Instead, it shows the options with the minus sign. In such as case, You will have to change your system settings – as covered later in this tutorial.
Also read: How to Make Negative Numbers Show Up in Red in Excel
Show Negative Numbers in Brackets by Creating a Custom Format
While the above system does allow you to show negative numbers in brackets (in black or red color), in case you need more control on how these numbers should be shown, you can create your own custom number format.
For example, you may want to show the negative numbers in brackets along with a minus sign (or show them in orange/brown color instead of red).
Below, I have a dataset where I have negative numbers and I want to show the negative numbers in brackets along with the minus sign.
Below are the steps to do this:
- Select the cells that contain the negative numbers that you want to format
- Click the ‘Home’ tab
- In the ‘Number’ group, click on the dialog box launcher
- In the ‘Format Cells’ dialog box, within the ‘Number’ tab, select the ‘Custom’ option
- In the ‘Type’ field, enter the below custom format
0;- (0)
- Click OK
The above steps would change the formatting of the cells and the negative numbers would now be shown in brackets along with a minus sign.
Let me quickly explain how custom number formatting works, and then I will give you some examples of other formats you can use.
In Excel, you can enter the following types of data:
- Positive Number
- Negative Number
- Zero
- Text
For any cell, you can specify the format for any of the above data types. This means that I can specify how the positive numbers should be shown in a cell, how the negative numbers should be shown, and how zero and text values should be shown.
And below is how the custom number format should be created
<Format for Positive Number>;<Format for Negative Number>;<Format for Zero>;<Format for Text>
Note that each of these formats is separated by a semi-colon.
If you don’t specify the format for any of these four data types, the ‘General’ format would be used.
In our example, we use the format 0;- (0)
Where:
- Positive Number format – 0 – This means that the number should be shown as entered
- Negative Number format – – (0) – This means that the number should be shown as entered within parentheses
- Zero Format – since this is not specified, it will be taken as General
- Text format – since this is not specified, it will be taken as General
In this case, since we have made the negative number format as – (#,##0), any negative number would be displayed in a bracket with a negative sign outside the bracket.
Now that you have a basic understanding of how custom number formatting works in Excel, here are some more formats that you can use:
Showing the numbers with thousand separators, and negative numbers in parentheses
#,##0.00;(#,##0.00)
Showing the negative numbers in red color and in parenthesis
#,##0.00;[Red](#,##0.00)
Showing the negative numbers in red color and in parenthesis with a minus sign
#,##0.00;[Red]- (#,##0.00)
Showing the negative numbers in red color positive numbers in Green color
[Green]#,##0.00;[Red]- (#,##0.00)
Also read: Remove Parentheses in Excel
Number Format Not Showing the Parnetheses Option? How to Fix!
When you open the Format Cells dialog box, and select the Number option you see the option to show negative numbers in brackets (as shown below).
In case you don’t see these options, it could be because of the settings in your system.
Here is how you can change the settings so it shows you the options to format cells so that negative numbers are shown within parentheses:
- Open Control Panel on your system
- Click on the ‘Change date, time, or number formats’ option
- In the Region dialog box, click on the ‘Additional Settings’ button
- In the Numbers tab, change the Negative Number format (from the drop-down)
- Click OK
- Click OK
The above steps would change your system’s setting and you will be able to see the formatting option to show negative numbers in brackets in the Format Cells dialog box.
In this tutorial, I showed you how to change the format of the cells to show negative numbers in brackets. I also covered how you can customize the format to add a minus sign along with the brackets as well as change the color of the negative numbers.
I hope you found this tutorial useful.
Other articles you may also like:
- How to Sum Only Positive or Negative Numbers in Excel (Easy Formula)
- Change Negative Number to Positive in Excel [Remove Negative Sign]
- Color Negative Chart Data Labels in Red with a downward arrow
- How to Sum by Color in Excel (Formula & VBA)
- How to Remove Cell Formatting in Excel (from All, Blank, Specific Cells)
- How to Filter Cells with Bold Font Formatting in Excel
- How to Display Numbers as Fractions (Write Fractions in Excel)
- Add Parentheses Around Text in Excel