Using custom formatting in your spreadsheet can make your data much easier to read.
One common formatting task that users of Google Sheets and other spreadsheet programs want to learn is how to turn negative numbers red.
Instead of highlighting negative numbers one at a time, there are a few ways you can do this in Google Sheets that will make the job much more efficient.
In this tutorial, I will show 2 ways to turn negative numbers red in Google Sheets.
Table of Contents
Making Negative Numbers Red with Conditional Formatting
The best way to turn your negative numbers red is with conditional formatting.
You can use conditional formatting to apply formatting rules based on the values of your cells.
In this example, what we will do is use conditional formatting to apply a rule to any cell that is less than zero and highlight it red.
Here are the steps to do this:
1. The first step is to highlight the range of cells that you want to use this conditional formatting on. I selected the entire column A in my example by clicking at the top of the column
2. Next in the top menu, select Format
3. In the drop-down menu, select Conditional formatting
4. Click on the text box below “Format cells if” by default, it should say “Is not empty”
5. This is where you will select your rules for conditional formatting. If you just want to do negative numbers, select Less than
6. A text box that says “Value or formula” should appear beneath the option you just selected. In this box Enter a zero (0)
7. Next select the paint bucket to edit your Fill color
8. You will now be choosing the color you want your negative numbers to change to.. In this example we chose a bright red color, but you can choose whatever color you wish
9. Select Done
10. Your negative numbers (any number less than zero) will now change to the fill color that you have chosen
With conditional formatting you can apply any rules you like to format your cells. If you want to apply formatting rules to numbers equal to, less than, or greater than certain values, all of that is possible by adjusting your format rules.
Using a Custom Format to Make Negative Numbers Red
Another way to change your negative values to red in Google Sheets is by adding custom formatting rules.
Here are the steps to do this:
1. Highlight the range of cells that contain the numbers that you want to turn red. In this example we highlighted the entire first column by clicking the letter A in the top of the column
2. Go to the top menu and select Format
3. In the drop-down menu, select Number
4. At the very bottom of the menu, select More Formats and then select Custom number format
5. In the text box you will be entering the rules that will tell Google Sheets to turn negative numbers red. You want to enter this in the text box: General;[RED]General;General;@
6. After you have entered the rules into the text field, select Apply
7. The negative numbers in the range that you have selected should now all be red.
The reason this works is because in Google Sheets there are four types of data: Positive numbers, Negative numbers, Zero values, and Text.
The custom format that we have used:
Each of the four data types is represented in the custom formatting rule above. Each format is separated by a semi colon (;).
Because the word RED is in front of the second “General” in the custom formatting rule, it tells Google Sheets to turn all negative numbers red.
In this tutorial, I have shown a couple of different ways of turning your negative numbers red inside your spreadsheet.
In this first method, I showed how to do this by using conditional formatting and changing the fill color to red for values less than zero. You can also change the text color if you like.
The second I showed how to apply custom formatting rules to do this.
Whichever method you choose is up to you, though most people lean towards using conditional formatting for this type of task.