How to Make Cells Red if a Number Is Less Than Zero in Google Sheets

To make cells turn red when a number is less than zero in Google Sheets, select the cells, click Format > Conditional formatting, set Format cells if to Less than, type 0, pick a red fill or text color, and click Done. Any negative number in that range now turns red automatically, and it changes back if the value goes above zero.

This guide follows Google’s conditional formatting guide and Google’s number format guide. It covers the conditional formatting rule, a custom number format that turns only the text red, how to color a whole row, the mobile app, and how to edit or remove the rule.

How to Make Cells Red if a Number Is Less Than Zero

These steps use conditional formatting, which Google describes as a way to change a cell’s text or background color when it meets a condition.

1. Open your spreadsheet in Google Sheets on a computer.
2. Select the cells you want to check, like a column of totals or a profit column.
3. Click Format > Conditional formatting. A panel opens on the right side of the screen.
4. Make sure the Single color tab is selected.
5. Under Format cells if, click Less than. If a rule already exists, click it, or click Add new rule first.
6. Click the Value or formula box and type 0.
7. Under Formatting style, click the fill color button and choose red. To make only the number red, set the text color to red instead.
8. Click Done.

Result: Every cell in the range with a value below zero is now red. A cell that holds exactly 0 stays unchanged, because 0 is not less than 0.

Red Text Only, Without a Red Cell

If you want red numbers but no colored background, set the Fill color to None and pick a shade of red under Text color. This keeps whatever number format you already use, such as a minus sign or parentheses, and only changes the color.

Another Way: Make Negative Numbers Red With a Custom Number Format

A custom number format can also color negative numbers. According to Google’s number format guide, a custom format can have up to four parts separated by semicolons: positive, negative, zero, and non-numeric (text). Adding a color in brackets, like [Red], to the negative part turns those numbers red.

1. Select the cells.
2. Click Format > Number > Custom number format.
3. Type a format into the box, such as #,##0.00;[Red]-#,##0.00. You can also search the box for a ready-made format.
4. Click Apply.

Result: Negative numbers show in red text with two decimal places. Positive numbers keep the normal text color.

A few useful variations:

• #,##0;[Red](#,##0) shows negatives in red inside parentheses, a common accounting style.
• #,##0;[Red]#,##0 shows negatives in red without a minus sign, so use it only if the red color alone is enough to tell values apart.
• General;[Red]General;General;@ turns negatives red while leaving everything else in the default format.

Google notes that format colors must be written in English. The supported names are Black, White, Red, Blue, Green, Magenta, Yellow and Cyan, plus Color# with a number from 1 to 56 for other shades. Typing “red” into the custom number format box shows several preset formats with red negatives that you can preview before applying.

Conditional Formatting vs. Custom Number Format

Both methods update automatically when a value changes. The main differences:

• Cell color: Only conditional formatting can fill the whole cell with red. A number format can change the text color but not the cell color.
• Flexibility: Conditional formatting can use other conditions, like less than -100, and you can stack more than one rule on the same range.
• Display: A number format also controls decimals, separators and parentheses, so you set the look and the color in one place.

How to Make the Whole Row Red if a Number Is Negative

Google’s guide shows how to format an entire row based on one cell, using a custom formula. To turn rows red when the value in column D is below zero:

1. Select the rows you want to format, for example columns A to E.
2. Click Format > Conditional formatting.
3. Under Format cells if, choose Custom formula is.
4. Type =$D1<0. The dollar sign locks the column, so every cell in the row checks column D.
5. Choose a red fill and click Done.

Result: Any row with a negative number in column D turns red. If your data starts on row 2, use =$D2<0 and select from row 2 down.

How to Do It in the Google Sheets App on Android

Google’s Android instructions follow the same idea. Open the spreadsheet in the Google Sheets app, select the range, and tap Format > Conditional formatting. Under Format cells if, tap Less than, tap Value or formula and enter 0, tap the red box under the formatting style, then tap Done.

How to Edit or Remove the Rule

Select the cells and click Format > Conditional formatting to see the rules. Click a rule to change the value or color. To delete it, point to the rule and click Remove (the trash icon), as described in Google’s conditional formatting guide.

To remove a custom number format, select the cells and choose Format > Number > Automatic or another standard format.

Troubleshooting

• Some negative cells aren’t red: Check the range in the Apply to range box. New rows added below the range won’t be covered until you extend it.
• A different color shows up: Google evaluates rules in the order listed, and the first rule that is true sets the format. Drag the red rule higher in the list.
• The rule spread to other cells: Google notes that copying and pasting cells with formatting rules also pastes the rules. You can use this on purpose to copy the rule to new data.
• Numbers stored as text: A value that Sheets reads as text, such as one with an apostrophe in front of it, may not be treated as a number by a Less than rule. Convert it to a number and check again.

Frequently Asked Questions

Will the cells change back if the number becomes positive?

Yes. Both conditional formatting and custom number formats are dynamic, so a cell loses its red color as soon as its value is zero or higher. The one exception is a script that colors cells once, which doesn’t update by itself.

Can I make positive numbers green at the same time?

Yes. With conditional formatting, add a second rule using Greater than and 0 with a green color. With a number format, put a color in the positive part too, such as [Green]#,##0;[Red](#,##0).

Related Guides

• How to use currency formatting in Google Sheets
• How to remove fill color in Google Sheets
• How to clear formatting in Google Sheets
• How to add a hyperlink in Google Sheets