Google Sheets Conditional Formatting: Rules and Examples

In Google Sheets, select the cells you want to format, then choose Format > Conditional formatting to create a rule that changes their appearance when a condition is met. You can use built-in conditions, custom formulas, or color scales; the cell values themselves do not change.

What conditional formatting does

Conditional formatting applies a visual style—such as a text color, fill color, or bold text—when the contents of a cell meet a rule. A task list might highlight overdue items, a sales table might shade values above a target, and a calendar might mark dates that fall within a chosen period. The formatting updates as the underlying data changes, so there is no need to recolor each cell manually.

It is a display feature, not a way to edit or validate data. A red cell is still the same value it was before the rule ran, and other formulas continue to use that value normally. Conditional formatting also does not sort rows, filter a table, or prevent someone from entering an incorrect value. Use data validation or a filter when those are the outcomes you need.

How to create a rule

In Google Sheets in a web browser, follow these steps:

Select the cell or range to format. You can also select a range after opening the conditional-formatting panel.
Choose Format > Conditional formatting. A panel opens on the right.
Check Apply to range and edit the range if necessary. For example, enter B2:B100 to cover cells B2 through B100.
Under Format cells if, choose a condition, such as “Text contains,” “Date is before,” or “Greater than.”
Choose the formatting style, such as a fill color or text color. Select Done to save the rule.

The panel lets you add another rule for the same or a different range. To change an existing rule, reopen Format > Conditional formatting, select the rule, edit its range, condition, or style, and save the change. Check the apply-to range carefully: a correct condition attached to the wrong range can make a sheet appear inconsistent.

Common rule types and useful examples

For a straightforward condition, a built-in rule is usually the easiest option. Google Sheets offers choices for numbers, text, dates, and whether a cell is empty or not empty. Depending on the condition, you can enter a comparison value or choose a date criterion. These rules are convenient because you do not need to write a formula.

Highlight values above a target: Select a sales range, choose “Greater than,” and enter the target value. Cells exceeding it receive the selected style.
Flag incomplete entries: Select a column and use the “Is empty” condition to mark missing values.
Find matching text: Use “Text contains” for a word or phrase, such as “Waiting,” in a status column.
Show date-based reminders: Use a date condition, such as a date being in the past, to call attention to older entries.

For example, if a status column contains “Open,” “In progress,” and “Complete,” a text rule can color cells containing “Open.” If you want to highlight an entire row based on the status in one column, however, a custom formula is more suitable.

Custom formulas for row-based formatting

Choose Custom formula is in the rule panel when the built-in conditions cannot express what you need, or when a condition in one column should format other cells in the same row. A custom formula should evaluate to TRUE for rows that should receive the style and FALSE for rows that should not.

Suppose your table has task names in A, due dates in B, and status values in C, with records in rows 2 through 100. To highlight cells A2:C100 whenever the status in column C is “Overdue,” select A2:C100, choose Custom formula is, and use this rule:

With task names in A2:A100, due dates in B2:B100, and statuses in C2:C100, use =$C2="Overdue". The dollar sign fixes the status column, while the row number remains relative, so each row checks its own status.

The row number in the formula should correspond to the first row of the apply-to range. If the range begins at row 2, use row 2 in the formula. If it begins at row 5, the formula should normally refer to row 5. This alignment is a frequent source of rules that seem to highlight the wrong row.

You can combine conditions as well. For example, to flag tasks whose due dates have passed while they are not complete, use a formula that checks both columns:

With due dates in B2:B100 and statuses in C2:C100, and the rule applied to A2:C100, use =AND($B2<>"",$B2<TODAY(),$C2<>"Complete"). This excludes blank due dates, checks whether the date is earlier than today, and ignores completed tasks. TODAY() updates with the current date, so the highlighted rows can change over time.

For another example, you can highlight duplicate IDs in a list. With IDs in A2:A100 and the rule applied to A2:A100, use =COUNTIF($A$2:$A$100,A2)>1. Each cell is highlighted if its value appears more than once in the specified range. Be aware that the rule flags every occurrence of a repeated value, including the first one.

Color scales for spotting patterns

A color scale assigns colors across a range according to the relative size of the values. To create one, select a numeric range and open the conditional-formatting panel, then choose the Color scale tab. You can set the minimum, midpoint, and maximum points and choose a color for each. Sheets then shades cells along the scale rather than applying one identical style to every cell that meets a yes-or-no test.

Color scales can help reveal high and low values in a column of scores, expenses, or response times. Think about what the colors imply before using one. A red-to-green scale might suggest that low values are undesirable and high values are desirable, which is not true for every measure. A middle point can make sense when values around a target are important, but a simple two-color scale may be clearer when only relative low and high values matter.

A scale compares values within the selected range, so its meaning can shift when you add or remove data. It is useful for visual comparison, but it does not label a value as “good” or “bad” according to a fixed business threshold. Use a rule such as “Greater than” when the threshold itself is the important part.

Manage rules, ranges, and overlapping conditions

The conditional-formatting panel displays rules for the selected cells. Review their apply-to ranges when copying data, inserting rows, or expanding a table. A rule that covers only B2:B20 will not necessarily format new entries in B21 and below. You can extend the range in the panel or set a larger range in advance, such as B2:B500, if that suits the sheet.

More than one rule can apply to the same cells. Google Sheets evaluates rules in the order shown in the panel, and when multiple rules are true, the first matching rule determines the formatting. If a result looks unexpected, inspect overlapping rules and their order. Reorder or simplify them so the intended condition takes precedence. Also consider whether two rules are trying to assign conflicting styles to the same cells.

Formatting is easiest to maintain when ranges and rules have a clear purpose. For example, a rule for overdue tasks can use a light red fill, while another rule for completed tasks can use muted text. If a single cell meets both conditions, the rule order matters; ideally, make the conditions mutually exclusive or arrange them so the most important state wins.

Common problems and how to fix them

The wrong row is highlighted: Compare the first row in the apply-to range with the row number in the custom formula. Check that columns which should stay fixed have dollar signs, such as $C2.
Nothing is highlighted: Confirm the selected range, condition, and comparison value. Check for extra spaces or spelling differences in text. For a formula rule, make sure the formula returns TRUE for at least one intended row.
Some new rows are not formatted: Expand the apply-to range to include them. The rule only affects cells inside its range.
A formula refers to the wrong cells: Use relative references for the row that should change and absolute references for columns or ranges that should stay fixed. Test the formula against a few rows before applying it broadly.
Colors are hard to interpret: Reduce the number of competing styles, use readable contrast, and avoid relying on color alone to communicate a critical status.

When troubleshooting, temporarily select a cell that should match and inspect its raw value in the formula bar. A date that looks correct may be stored as text, and a status may contain a hidden space. Conditional formatting tests the underlying value, not just what the cell appears to show.

Google Sheets and Excel: what differs

The basic idea exists in both Google Sheets and Microsoft Excel, but the menus and some options differ. In Sheets, the browser path is Format > Conditional formatting. In Excel for Microsoft 365 on Windows, the usual path is Home > Conditional Formatting. Excel’s rule manager and selection controls are not the same as Sheets’ right-hand panel, so instructions for one program should not be assumed to match the other exactly.

Custom formulas in Sheets and Excel share many familiar spreadsheet conventions, but you should check references and supported options if moving a workbook between them. Sheets also makes it straightforward to edit a shared sheet in a browser, while Excel’s behavior can depend on the platform and workbook. If a feature or rule looks different after importing a file, review the conditions and ranges in the destination program rather than assuming every formatting rule transferred identically.

Frequently Asked Questions

Can Google Sheets conditional formatting change a cell’s value?
No. It changes how a cell looks when a condition is true. To change values, use a formula, edit the data, or use another appropriate spreadsheet feature.

How do I highlight an entire row based on one cell?
Select the full row range you want formatted, choose Custom formula is, and write a formula that locks the condition column but leaves the row relative. For example, if column C contains status values and the selected range begins at row 2, use =$C2="Overdue".

Can I use more than one conditional-formatting rule?
Yes. You can add multiple rules, including rules for the same cells. Review their order if conditions overlap, because the first matching rule determines the formatting.

Why does a conditional-formatting rule not work on new rows?
The new rows may fall outside the rule’s apply-to range. Open the conditional-formatting panel and extend the range to include them.

Can I use a formula with conditional formatting?
Yes. Choose Custom formula is and enter a formula that returns TRUE or FALSE. Use dollar signs thoughtfully so references move across rows or remain fixed as intended.

Are color scales the same as conditional-formatting rules?
They are another conditional-formatting option, but they use a gradient across a range rather than one style for a single pass-or-fail condition. Use a color scale for relative comparisons and a standard rule for a clear threshold or category.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.