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
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.
- Right-click on the cell and select “Format Cells…”. Alternatively, press “Ctrl + 1” on the keyboard.
- On the “Number” tab, select the category “Number” and change the format to the second or fourth option (negative numbers have red color and the “-” sign is shown).
Method 1b: Use a custom cell format
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:
- Proceed as written in the method 1a above (set the cell format to Number and choose the second option).
- Click on “Custom” on the left hand side. Add the minus sign as shown in the image above or copy the following code:
#,##0;[Red]-#,##0
Paste it into the “Type” field in the center of the window. - Confirm with OK.
Method 2: Use conditional formatting for highlighting negative values
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:
- On the Home ribbon, click on “Conditional Formatting” and then on “Highlight Cells Rules…”.
- Click on “Less Than”.
Now you’ll see a small pop up window as shown on the right hand side.
- In the left text field type 0 because you want to format all negative values.
- Now you can select a predefined style (for example the “Light Red Fill with Dark Red Text” like on the screenshot. Or just choose the “Red Text” option. You can also set a custom format with the last entry.
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
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“:
- Select the cells having conditional formatting rules.
- 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.
