Kawser
Sometimes, it is important to mark cells that have some specific values. Most often, users need to differently mark negative and positive values. In this article, we will see how to make negative numbers red in Excel.
Negative numbers can be made red in Excel in three ways. You can use conditional formatting – the most used approach. You can also use Built-In Formatting System (Format Cells dialog box) and Custom Number Formats.
So, let’s start with the first one.
3 Ways to Make Negative Numbers Red in Excel
1) Using Conditional Formatting
We do not need some complex Excel files to show how this is done. Just open Excel and type in some values (make sure to have positive and negative ones). I created a table like this (you don’t have to do the formatting things).
How to perform conditional formatting? Select values where you want to apply the formatting and click the triangle in the Conditional Formatting, in the Home tab. Click on “New Rule”.
This can be done in so many ways, but I will show you the most flexible way that you can use – defining a formula for the rule, so you can apply that for any other formatting that you need.
You will get a screen like this:
Select the last option – “Use a formula to determine which cells to format”.
In the field for the formula, in this case, I typed =A3<0. It means that if A3 or the current cell is greater than 0, it will be formatted in a defined way. The formula without a $ symbol will work on any cell if you apply it to some wider range. If you add $ signs in certain places, it works according to rules for absolute and relative references.
To define the format when the rule is “True”, click on “Format”.
You will get a screen like this:
In the “Color:” field, select the color that you want (in this case, it will be red). Also, you can select any other formatting that you would like to apply. After clicking “Ok”, you will get the previous screen, with the selected format preview:
Press “OK”. You will get a screen like this: In the “Applies to” field, you can select cells where you want to apply the rule.
I have selected as in the image:
“Stop if true” mark should be checked if you want the rule to stop working once the condition says “True”. If you want this condition to keep working if you change values, you will leave this checkbox blank.
Click on “Apply”. If you did everything correctly, you should get a result like this:
Try changing values to see what happens. The other two ways of highlighting negative values will be shown below.
2) Using Built-in Functionality
If you for some reason prefer this way of formatting, you can do it by performing the following steps. I have copied the data from the previous example:
What you should do is select the range where you want the format to apply. Go to “Home” tab, close to “Number”, you have the downright arrow. Click the arrow:
You will get a screen like this:
Select as shown in the image, and in “Symbol”, select “None”. Click on “OK” and check results.
There is another way how you can perform this formatting – make negative cells red. It will be explained in the following lines:
3) Using Custom Formats
Excel, as a tool that was first used for accounting, makes a significant difference between negative and positive values. That is the reason why you can set a special format for negative cells.
I have copied the previous example in the file just next to previous examples, in order to use them for showing how to apply custom formats.
From the drop-down menu, where it in most cases says “General” – select “More number formats…” which is the last option that you will see.
Select “Custom” and you will see a screen like the one above.
In the entry field, you should type in #.##0,00;[Red]-#.##0,00
The blue part is showing the format of positive values. The red one is showing the format of the negative values. The colored formula for the format defines what the number will look like when you enter it. The current format is showing that there will be a point as a thousand separator, and a comma as a decimal separator. Also, you can define the number of decimal spaces that you would like to have. You define it by entering 0 after the decimal comma in the format. If you would like to specify a color for the format, you should enter it in [] brackets before the format. For example, if you would like to make positive numbers blue in this case, you would need to write: [Blue]#.##0,00;[Red]-#.##0,00. Actually, you can select any color that you want – Any color has its unique name, so, for example, if you type [Color13], it will provide some purple color. And, the last part of the custom format is the currency. You can add a currency sign if you want. You can add it before or after the number. So, the formula can look like this:
€#.##0,0000;[Red]€-#.##0,00
Or like this:
#.##0,0000€;[Red] -#.##0,00€
Also, you can define spaces if you want.
➥Related: Excel Formula to Change Cell Color Based on Date
After selecting “OK”, you will see the results:
Further Readings
- Excel Conditional Formatting Based on Date in Another Cell
- Excel conditional formatting based on another cell text [5 ways]
- How to Compare Two Columns in Excel For Finding Differences
- Excel Alternating Row Color with Conditional Formatting [Video]
- Excel Conditional Formatting Based on Another Cell
- Excel Conditional Formatting Formula
