One of the most common formatting requests is how to get Excel negative numbers in brackets (and red) instead of just a negative sign (-) in front of the number.
The actual issue is not an Excel issue but rather at a Windows level but you can make the change either at a Windows level (in which case all programs using numbers will change) or at an Excel level.
Negative Number in Brackets across entire computer (including Excel)
This needs to be changed at a Windows level. One word of caution is that you may have some programs that need the alternate format. So if you make this change, test some of your other programs or at least remember this change if you suddenly experience problems in other programs.
- Go to your Control Panels,
- Choose the Clock, Language and Region option.
- Choose Additional Settings
- On the Numbers tab click on the Negative Numbers format and choose the option with the brackets (1.1). DON’T CLICK OK YET!
- Go to the Currency tab and choose the same option with brackets(R1.1).
- Then click OK and apply until you are out of settings.
When you go back to Excel you will see that the negative numbers now have brackets around them and in the Format Cells box, under Numbers you now have an option for red and in brackets (if it doesn’t look like this, you may need to restart your computer).
From now on Excel, and your other programs, should have this as an option.
Negative numbers in brackets but only in Excel
Alternatively, you change the formatting of the cell to show negative numbers within brackets, but you will need to go into the custom formats option (as Excel uses the Regional settings as its default unless told otherwise).
The easiest way to do this is to create a Custom Format.
As shown below, you can
- Highlight the cells you want to change (note that negatives have a – at the moment),
- Click on Format Cells,
- Choose Custom and paste the following code in the Type section (this assumes you want the negative numbers red as well. If you don’t just delete the [Red])
# ##0_ ;[Red](# ##0)
Each character has a meaning but in general you can just paste this in and you should see the same as below.
You can now use this format, but please note that as this is a custom format, you will need to recreate it in any new spreadsheets. If you want it permanently available you will need to make the permanent change mentioned above.
If you found this useful you may enjoy the Excel Tools course we offer. Learn about all the available tools in Excel. Practical, real life examples to improve your use of Excel.