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:

  1. Conditional Formatting
  2. 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.

  1. Open a blank workbook in Excel
  2. Enter a few random numbers including few negative numbers in Column A. Refer to the below screenshot if need beNegative Numbers
  3. 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
  4. Select the range of cell which contains the numbers. In the above case I created, I’ll select A2:A20
  5. Then, Go to Home > Conditional Formatting > Highlight Cell Rules > Less ThanNegative Numbers-Conditional Formatting
  6. 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 neededNegative Numbers - Conditional Formatting 2
  7. Click OK to apply the formatting, you’ll notice that the all the cells containing negative numbers are now formattedNegative Numbers - Red

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

  1. I’ve entered another set of random numbers including negative numbers in Column C as shown in the screenshot belowNegative Numbers - Number Formatting
  2. Now, we’ll select cell range C2:C20
  3. Click on the more options button in the Number section of the Home tab as shown in the screenshot below:
  4. 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 redNegative Numbers- Number Formatting
  5. 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
  6. 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

 

 

 

Join the newsletter & Get your CHEAT SHEET!

Enter your name and email to get our custom number formatting cheat sheet and Join the Newsletter!

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to ConvertKit ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Abdelrahman Abdou
Latest posts by Abdelrahman Abdou (see all)
It's only fair to share...Share on Facebook
Facebook
0Pin on Pinterest
Pinterest
0Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin