Negative numbers in Red in Excel?

Want to learn how to Differentiate Negative Numbers in Excel? You’ve come to the right place!

Whenever you deal with a large set of numbers, it’s a good practice to differentiate the negative numbers from the positive ones. Like the conventional way of representing negative numbers, even in Excel, negative numbers are prefixed with the “–“ symbol (Negative sign). But, this might not be enough when we deal with a lot of numbers. In such situations, formatting the negative numbers with a different color like red (universally used to represent negative numbers) will be useful.

There are 2 ways in which we can make negative numbers stand out in Excel:

Conditional Formatting
Number formatting

Let’s take an example and understand how each of these techniques works.

1. Using Conditional Formatting

Excel has the capability to change the formatting of a cell based dynamically based on the content of the cell and this capability is referred as “Conditional Formatting”. Using this let’s try to make all negative numbers appear in red.

Open a blank workbook in Excel
Enter a few random numbers including few negative numbers in Column A. Refer to the below screenshot if need be
Now we’ll try to make changes in the formatting of the cell (i.e. by giving the cell a light red fill and changing the font color to red). The condition we will check is whether the value in a cell is less than 0 or not
Select the range of cell which contains the numbers. In the above case I created, I’ll select A2:A20
Then, Go to Home > Conditional Formatting > Highlight Cell Rules > Less Than
In the pop-up, enter 0 in the first box and select “Light Red Fill with Dark Red Text” in the second box. You can select a different formatting if needed
Click OK to apply the formatting, you’ll notice that the all the cells containing negative numbers are now formatted

This is how conditional formatting can be used to format negative numbers. There are a lot of different formatting options and a wide variety of conditions which can be used as a part of conditional formatting. I’ll cover that in detail in some other article in the future

2. Using Number Formatting.

The second method of changing the formatting of the negative numbers would be to change the Number format of the cells. We’ll explore how this can be done in the following steps

I’ve entered another set of random numbers including negative numbers in Column C as shown in the screenshot below
Now, we’ll select cell range C2:C20
Click on the more options button in the Number section of the Home tab as shown in the screenshot below:
In the pop-up click on the Category Number on the left and choose either the second or the fourth option from the right as highlighted in the screenshot below, the second option changes the font color to red and removes the “-“ symbol from the number and the fourth option retains the symbol and changes the color to red
Click OK to apply the selected formatting. This will automatically round off the decimal places to 2 digits. If this needs to be changed, you have an option with name “Decimal places:” in the pop-up
The changing of Decimal places can also be done by clicking on Decrease Decimal icon from the Number group on Home tab as highlighted in the screenshot below:

Finally, this completes the formatting of negative numbers using the Number Formatting option.

Since the negative numbers are highlighted, going through the large sets of information in much easier.

Bonus: Check out our Youtube Video about Custom Number formatting. You can also download our Custom Number Formatting Cheat sheet using the form below!

https://www.youtube.com/watch?v=lC6amUM-jTE&t=33s