Site icon Professor Excel

Negative Values in Red Color: 3 Easy Methods for You!

excel, highlight, negative, color, red, minus

A good Excel table provides a quick overview of the most important facts and figures. One way of highlighting important numbers is by using colors. For example, values below zero should stand out. In this article we’ll explore two ways of highlighting negative values in red color in Excel.

Method 1a: Highlight negative values with cell formatting

Excel provides a number format for highlighting negative values in red color.

The first method for highlighting negative values in red color is quite simple: Changing the number format to the predefined format for red negative numbers.

Method 1b: Use a custom cell format

Customize the number format in order to keep the minus sign.

The first method (1a) above has one disadvantage: It removes the minus sign for negative values. If you want to keep it, you can easily tweak it:

Method 2: Use conditional formatting for highlighting negative values

With conditional formatting rules you can further adjust the formatting.

The second method is a little bit more complicated but offers more options: Use conditional formatting.

Conditional Formatting allows you to set formats, symbols and even small bars within the cell depending on the values. Follow these steps to set up a conditional format for highlighting negative numbers:

Further adjust the format for negative values.

Now you’ll see a small pop up window as shown on the right hand side.

For more information about how to use small symbols or shades of the background color, please refer to this article. This article provides more information of how to use conditional formatting with formulas. 

Bonus Tip: Remove conditional formatting rules but keep the colors

Remove conditional formatting but keep the format with Professor Excel Tools

In some cases, you want to remove the conditional formatting rules but keep the format. For example, you’ve selected the background color depending on the values (see above). Now you want to keep the color, but either delete the values or even change the values.

There is one very comfortable way: Use “Professor Excel Tools“:

  1. Select the cells having conditional formatting rules.
  2. Click on “Remove Cond. Formatting” within the Quick Cell Functions on the Professor Excel ribbon.

You can try the Excel add-in for free – no sign-up or installation needed. Just click the button below.

This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Exit mobile version