This simple technique for showing negative numbers in red in Excel will assist you in making your reports and Excel dashboards stand-out more….
Showing negative numbers in Red in Excel
In general, unless your client or manager prefers differently, it tends to be standard practice to show negative numbers in red in Excel reports and dashboards. The reason is fairly obvious as it makes it a lot clearer for the users to differentiate between positives and negatives and that can be especially important on reports or dashboards that end up a bit crowded.
For example, this…
…is much more effective at highlighting negatives than the standard way:
How to show negative numbers in red in Excel
In order to show negative numbers in red in Excel the technique is simple, first off select the range of cells that you want to convert:
Next right click the mouse within the selected area and from the pop-up menu select “Format Cells” from the list:
As we are looking for negative values the data must be numeric, so that means you must ensure the category from the “Format Cells” options box is either “Number” or “Currency”. In this example I have used currency:
When you have selected either “Number” or “Currency” you can then choose how Excel will display the values. In order to make show negative numbers in red you need to select the correct option within the “Negative Numbers” category:
As you can see from the options in the “Negative numbers” box you have 2 unique ways of showing negative numbers as red in Excel, either with the minus sign in front of the number, i.e. -£100 or without the minus sign, i.e. £100.
My preference is to include the minus(negative) sign in front of the number as that tends to be the most common way people are used to seeing negatives displayed, it also removes any potential concerns the user might have that the value has just been formatted in red incorrectly.
When you have made your choice click on “OK” and the values will update:
Now you are showing negative numbers in red in your Excel report!
It’s worth adding that the cells you have selected now retain that format so if you were to alter a positive to a negative in the range then that would also take the “red” formatting on. For example if I change January from £1000 to -£1000 it will show automatically like so:
Now you can produce more effective looking Excel reports and dashboards with negative numbers in Red.