Google Sheets Conditional Formatting: How to Apply Rules

Yes. Google Sheets includes conditional formatting, which automatically changes a cell’s appearance when its value or a formula meets a condition. In a browser, select the cells and choose Format > Conditional formatting to create a rule; Sheets then updates the formatting as the data changes.

What conditional formatting does

Conditional formatting adds visual emphasis without changing the contents of your spreadsheet. A rule can color a cell, change its text style, or apply a color scale when a condition is true. For example, a sales tracker could shade low totals red, mark completed tasks green, or draw attention to dates that have passed.

The formatting is dynamic: if a value changes so that it no longer meets the rule, the special appearance is removed or replaced by the appearance for another matching rule. The underlying value remains the same. A cell containing the number 12 is still 12 even if a rule displays it with a red background.

Rules apply to a selected range, and one range can have more than one rule. They are useful for scanning a table quickly, but they do not sort rows, change values, or prevent someone from entering data. Those tasks require other spreadsheet tools or processes.

How to apply a basic rule

In Google Sheets in a web browser, use these steps to set up a straightforward rule:

Select the cell or range you want to format. To apply a rule to a table column, select its data cells rather than the header if the header should stay unchanged.
Choose Format > Conditional formatting. A conditional-formatting panel opens on the right.
Check the range shown in the panel. Edit it if necessary, then use the Format cells if menu to choose a condition, such as a value being greater than a number or text containing a phrase.
Under formatting style, choose a fill color, text color, or text style. The preview shows the appearance of cells that match the rule.
Select Done. To add a different condition, select Add another rule in the panel.

For example, to flag values below 10 in cells B2:B50, select that range, open the panel, choose the less-than condition, enter 10, select a noticeable style, and finish with Done. You can change or remove the rule later by opening the same panel, selecting the rule, and editing or deleting it.

Choosing the right rule type

The rule menu offers conditions for common data checks, so a custom formula is not necessary for every task. The options available depend on the type of condition you choose. A number rule can compare values; text rules can look for text that contains, starts with, or matches a phrase; and date rules can identify dates such as yesterday, today, or a date in the past. There are also options for blank or nonblank cells.

Choose a condition that expresses the test you actually need. If you want to flag every score below 60, a less-than rule is clearer to maintain than a formula. If you want to highlight all cells containing “Review,” a text condition is usually simpler than writing a formula. Clear conditions make rules easier for colleagues to understand when they open the panel.

Use a custom formula when the rule depends on another cell, combines several tests, or should format an entire row based on one column. Before setting a rule, decide exactly which cells should receive the formatting and which values should trigger it. A rule applied to the wrong range can appear to work while missing new rows or coloring headings unintentionally.

Using custom formulas for rows and duplicates

Custom formulas are useful when a condition cannot be expressed with one of the built-in options. Select the range to format, choose Custom formula is in the condition menu, enter a formula, and set the style. The formula should evaluate to TRUE or FALSE for each cell or row in the selected range.

To highlight an entire row when its status is “Overdue,” suppose the status values are in column C, with records in rows 2 through 100. Select A2:D100 and use =$C2="Overdue". The dollar sign fixes the status column at C, while the row number remains relative so Sheets checks the status on each row. If the status is in another column, adjust the reference to match.

To find repeated entries in A2:A100, select that range and use =COUNTIF($A$2:$A,$A2)>1. This highlights each value that appears more than once in column A, including its first occurrence. If you want to flag only later repeats rather than every instance, that requires a different test; the intended meaning of “duplicate” matters.

In custom formulas, relative and absolute references are important. A reference such as A2 can shift as the rule is evaluated across the selected range. A reference such as $A$2 stays fixed, while $A2 fixes the column but lets the row change. If a rule colors the wrong column or repeats one row’s result throughout the range, check its range and dollar signs first.

When to use a color scale

A color scale shows how values compare across a range, rather than applying one style to values that pass or fail a single test. It can be helpful for a heat map of monthly totals, response times, or scores, where a gradual change in color makes relative highs and lows visible.

In the conditional-formatting panel, choose the Color scale tab and set the range and scale colors. You can configure minimum, midpoint, and maximum points, including the values or point types used for those stops. A midpoint is optional. Consider whether the scale should represent the observed spread of the data or meaningful thresholds: a scale based on the range’s extremes can make a small difference look large when all the values are close together.

Color scales work best with comparable numeric values. They can be confusing when a range mixes units, labels, or unrelated measures. For instance, applying one scale to both a percentage column and a dollar-amount column does not create a meaningful comparison, even if both are stored as numbers.

Managing rules and fixing common problems

If a rule does not behave as expected, begin by checking the range displayed in the panel. A rule meant for A2:D100 will not necessarily include a new row 101. Expand the range or choose a range that includes the rows you expect to use. Also verify that the condition refers to the correct column and that values are stored as the type the rule expects. A date entered as text, for example, may not behave like a real date value.

The wrong cells are colored: check the selected range and formula references. For row-based rules, lock the relevant column with a dollar sign but usually leave the row relative.
A formula rule never matches: test the logic against a few cells, check spelling and quotation marks, and confirm that blank cells are handled as intended.
Some rows look different from others: look for multiple rules that apply to the same cells. Their order and overlapping formats can affect the result; review the rule list and reorder or simplify rules if needed.
Formatting stops at the bottom of the data: extend the apply-to range or include space for future records.
A color is hard to interpret: use text labels or another visual cue as well. Color alone may not be clear to every reader, and a strong fill can make a busy sheet harder to scan.

When rules accumulate, remove obsolete ones instead of layering new rules over them. A shorter, well-named set of conditions is easier to review and less likely to produce surprising results. Conditional formatting is not a substitute for data validation: it can signal that an entry is unusual, but it does not stop an invalid entry from being made.

Practical examples and design advice

Conditional formatting can make common spreadsheet workflows easier to monitor. A project tracker might highlight overdue dates, a class roster might flag missing scores, and an inventory sheet might mark low stock. For a date-based alert, a rule can compare a due-date column with today’s date; a custom formula such as =AND($C2<>"",$C2<TODAY()) checks that C2 is not blank and is earlier than today. Applied to a range of rows, the relative row reference checks each record.

Use restraint with colors. Reserve a few consistent styles for meaningful states, such as overdue, complete, or needs review. If every value has a different bright fill, the important exceptions become harder to spot. Add a text status or legend when a sheet will be shared with people who may not know what each color means.

It is also worth checking how rules behave when people add, paste, or clear data. Try a few representative values and confirm that the rule catches the intended cases. For a shared sheet, explain the meaning of important highlights near the table or in a notes column. This makes the formatting useful rather than decorative.

Google Sheets compared with Excel

Google Sheets and Microsoft Excel both offer conditional formatting, including value-based rules, formula-driven rules, and color scales. In Sheets, the browser path is Format > Conditional formatting. Excel for Microsoft 365 on Windows uses Home > Conditional Formatting, and its dialogs, available rule options, and formula behavior are not identical to Sheets. Instructions or formulas copied between the programs should therefore be checked rather than assumed to work unchanged.

A practical difference is how people collaborate: Sheets is designed for browser-based editing and sharing, while Excel also has desktop apps with their own workflows. Conditional formatting is a built-in feature of Sheets and is not charged as a separate add-on, though access to Google services and Workspace features depends on the account and plan. If you move a spreadsheet between Sheets and Excel, review the rules after conversion because some formatting details or formulas may not transfer exactly.

Frequently Asked Questions

Can Google Sheets automatically highlight duplicates?

Yes. Select the cells to check, open Format > Conditional formatting, and use a custom formula such as =COUNTIF($A$2:$A,$A2)>1 for values in A2:A100. Adjust the column and range to fit your sheet.

Can I apply conditional formatting to an entire row?

Yes. Select the full row range you want to color, then write a custom formula that checks the relevant column. For example, with status in column C and records starting in row 2, =$C2="Overdue" can format each selected row according to its status.

Does conditional formatting change the cell value?

No. It changes how the cell appears when a rule matches. It does not replace the value, edit the formula in the cell, or block someone from entering different data.

Can I use more than one conditional-formatting rule?

Yes. You can add multiple rules, including rules that apply to the same range. Review their order and overlap if the resulting style is not what you expect.

Why is my conditional-formatting formula not working?

Common causes include an incorrect apply-to range, a reference that should be fixed with a dollar sign, a mismatch between text and numbers, or a formula that does not return TRUE for the cells being checked. Test the rule on a small range and confirm its references before expanding it.

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.