As per our financial modelling course we highly recommend that negative numbers are clearly shown as such and we prefer brackets around a negative number and that the negative numbers are in red.
There are several options to achieve this and a few other useful options related to negative numbers in Excel. Below we show how to do it:
- via format and custom format, and
- using conditional formatting for more complex options
Table of contents
Change the Format of the negative numbers to red
In general it should be really easy.
- Highlight the relevant cells
- Click on the Format Cells option
- Choose Numbers
- Choose the last option which is in red and may be in brackets of have a negative sign (use this method rather than the second one which just changes the colour but has no sign).
When you click OK, all the negative numbers will be in red. If you want red brackets then look at the Excel negative numbers in brackets post.
Would you prefer another colour rather than red for the negative numbers?
What happens if you don’t like red for your negative numbers in Excel. You can change this in the Custom Format.
Do the steps above, but once you click on the Numbers and the red format, instead of clicking OK, immediately click on the Custom option as shown below.
Excel will show you the code it uses to create a red negative. Note the word [Red] in the type box. You can change this to another colour e.g. [Blue] and [Green] and a negative number will be shown in that colour.
If we chose [Blue] the negative numbers would look like this. Positive numbers would be in black and negative numbers in blue.
Show negative numbers in red and positive numbers green or blue
Do you want negative numbers to be red and positive numbers to be green or blue? A small tweak of the above code will achieve this.
By adding the word [Blue] to the front of the code, you are telling Excel to change the positive numbers to be blue and the negative to be red. You could use the word Green as well. You can copy the code below into your type box.
[Blue]# ##0_ ;[Red]-# ##0\
Achieve the same thing with Conditional Formatting
Conditional formatting is a brilliant tool that does what is says, it formats a cell based on some condition that you can specify.
The added benefit over the method above is that you can have a different condition e.g. bigger than 10% instead of just positive or negative numbers, and you can change the format in more ways than just the font of the numbers.
It is well worth learning more about conditional formatting, but to achieve the same thing as above your can:
- Highlight the cells,
- Click on Conditional Formatting on the Home tab
- Choose the Highlight Cells Rule,
- Choose Less Than
You now need to decide ‘Less Than’ what and how must the formatting change when the condition is met. In this case you would put 0 in the block and click on Custom Format.
What pops up next is your normal Format Cell options. For the most part you can change the format in almost any way. Note that in this case we have just specified the font colour to become red, but note that we could change the number format, the borders or even fill the cells.
If you want to change the positive numbers to become say green, you will just repeat the above process (remember to highlight the same cells), and choose Greater Than.
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.
Related
Changing formats in Excel in one go