How to Show Negative Numbers in Red in Google Sheets

There are multiple ways you can use to show negative numbers in red in Google Sheets.

Doing this helps is going through numbers of data easily as it makes these negative numbers stand out.

To highlight negative numbers in red, you can either use conditional formatting so that the entire cells get highlighted in red or you can use custom number formatting so that the only the text is shown in red.

In this Google Sheets tutorial, I will cover both these methods to show the negative numbers in red.

So let’s get started!

Show Negative Numbers in Red – Using Conditional Formatting

Conditional formatting allows you to format a cell based on the value in it.

Since we want to highlight all the negative values, we can use conditional formatting to only show negative values in red and leave everything as is.

Suppose you have a dataset as shown below:

Data where negative values need to be highlighted in red

As you can see, it takes some effort if going through the numbers and spotting the ones that are negative.

Below are the steps to show negative numbers in red in Google Sheets:

  1. Select the cells in which you want to highlight the negative numbers
  2. Click the Format option in the menuClick the Format tab
  3. Click on Conditional Formatting. This will open the ‘Conditional formatting rules’ pane on the rightClick on Conditional Formatting
  4. Click on the ‘Format cells if’ drop-down.Click on format cells if drop down
  5. Click on the ‘Less than’ option (you may have to scroll down a bit to see this)Click on the less than option
  6. In the ‘Value or Formula’, enter 0Set the less than value to 0
  7. Set the color in which you want to highlight the cellSet the color to red to show negative cells
  8. Click on Done

The above steps would instantly highlight all the cells where the value in it is less than 0.

dataset where negative values are shown in red

Since conditional formatting is dynamic, in case you change any value in the cell, the formatting would update automatically. For example, if you change the value in a cell from positive to negative, the cell would instantly be highlighted in the specified color.

You can also just change the font of the cell value (which has a negative number) to red, instead of changing the entire cell color. This can be done by changing the font color only in step 7.

Note: When you copy and paste a cell’s formatting that has conditional formatting applied to it, this will also copy the conditional formatting rules. So if you add new data to the one where you already have conditional formatting applied, you don’t need to repeat all the steps above. Simply copy any cell that has the formatting and paste the formatting only.

Pro Tip: You can also highlight other cells in a similar way (in the same dataset). For example, if you want to highlight negative cells in red and the cells where the value is less than 200 in yellow, you can do that as well. All you need to do is repeat the same steps again and add another rule.

Show Negative Numbers in Red – Using Custom Formatting

Another easy way to automatically show all negative values in red in Google Sheets is by changing the custom number formatting.

With custom number formatting, you can specify what format you want to apply to:

  • Positive numbers
  • Negative numbers
  • Zero values
  • Text values

Suppose you have the dataset as shown below where you want to show negative numbers as red:

Below are the steps to do this:

  1. Select the dataset where you want to highlight the negative numbers
  2. Click the Format option in the menuClick the Format tab
  3. Hover the cursor over the Number option. It will show some additional options.
  4. Hover the cursor over the More Formats option. It will further show some additional options
  5. Click on Custom Number format. This will open the ‘Custom number formats’ dialog boxClick on Custom Number Format Option
  6. In the number format field, enter the following format: General;[RED]General;General;@Specify the format to show negative values in red and then click on apply
  7. Click on the Apply button

That’s it!

The negative numbers in your dataset would now be in red color while everything else would remain as is (as shown below).

Negative Numbers shown in red because of the number formatting

How does this work?

In Google Sheets, every cell can hold four different types of data – Positive numbers, Negative numbers, Zero values, and Text values.

Google Sheets also allows specifying what format you want to use for each of these data types. In this example, while I want to keep everything as is and only change the formatting of negative numbers.

So I used the below format:

General;[RED]General;General;@

General here indicates the general formatting (the default one) and each format is separated by a semi-colon.

By adding the word [RED] in front of the second format (which is for negative numbers), I am asking Google Sheets to show all the negative numbers in red.

For text, I have used @, which tells Google Sheets to treat text as text.

Note: Just like conditional formatting, using this method also makes your dataset dynamic. So if you change any cell’s value from positive to negative, it would automatically change the font to red.

Unlike conditional formatting, you can not use the custom formatting method to change the color of the cell.

So there are two simple methods you can use to highlight cells with a negative value in red color. Personally, I prefer using the conditional formatting method as it gives a lot of flexibility in formatting and is easier to apply.

You may also like the following Google Sheets tutorials:

Leave a Comment