Watch Video – 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:
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:
- Select the cells in which you want to highlight the negative numbers
- Click the Format option in the menu
- Click on Conditional Formatting. This will open the ‘Conditional formatting rules’ pane on the right
- Click on the ‘Format cells if’ drop-down.
- Click on the ‘Less than’ option (you may have to scroll down a bit to see this)
- In the ‘Value or Formula’, enter 0
- Set the color in which you want to highlight the cell
- Click on Done
The above steps would instantly highlight all the cells where the value in it is less than 0.
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.
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:
- Select the dataset where you want to highlight the negative numbers
- Click the Format option in the menu
- Hover the cursor over the Number option. It will show some additional options.
- Hover the cursor over the More Formats option. It will further show some additional options
- Click on Custom Number format. This will open the ‘Custom number formats’ dialog box
- In the number format field, enter the following format: General;[RED]General;General;@
- Click on the Apply button
The negative numbers in your dataset would now be in red color while everything else would remain as is (as shown below).
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 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:
- How to Color Alternate Rows In Google Sheets
- How to Filter By Color in Google Sheets
- How to Hide Zero Values in Google Sheets
- Apply Conditional Formatting based on Another Cell Value in Google Sheets
- How to Clear Formatting in Google Sheets
- How to Show Formulas in Google Sheets (instead of the value)
3 thoughts on “How to Show Negative Numbers in Red in Google Sheets”
Thank you, this was perfect and exactly what I needed!
nice work thanks
Straight to the point and clear to follow….THANKS!