How to Make Negative Numbers Red in Excel (3 Ways)

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 file 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).

Make Negative Numbers Red, Excel, Conditional Formatting

How to perform conditional formatting? Select values where you want to apply the formatting and click the triangle in the Conditional Formatting, in Home tab. Click on “New Rule”.

Where is Conditional Formatting command in Excel?

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:

conditional formatting excel

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 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:

Format Cells dialog box, Excel

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:

Edit Formatting Rule dialog box in Excel

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.

How to Make Negative Numbers Red in Excel

I have selected as in the image:

Stop If True, Excel, Conditional Formatting

“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:

Make Negative Numbers in Red, Excel

Try changing values to see what happens. 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:

How to make negative numbers in Red in Excel

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:

How to open the format cells dialog box in Excel

You will get a screen like this:

How to Make Negative Numbers Red in Excel

Select as shown in the image, and in “Symbol”, select “None”. Click on “OK” and check results.

How to Make Negative Numbers Red in Excel

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.

How to Make Negative Numbers Red in Excel

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.

Format Cells dialog box, Excel

Select “Custom” and you will see the 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 comma as 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 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.

After selecting “OK”, you will see the results:

How to Make Negative Numbers Red in Excel

Read More


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply