Google Sheets Conditional Formatting Rules Explained

In Google Sheets, conditional formatting rules automatically change a cell’s appearance when its value meets a condition. Select the cells, choose Format > Conditional formatting, set a condition and a style, then click Done; use a custom formula when the built-in conditions cannot express the pattern you need.

What conditional formatting does

Conditional formatting changes how data looks without changing the underlying values. A rule can apply a fill color, text color, bold or italic styling, or a color scale when cells meet a condition. For example, a task list can turn overdue dates red, a sales table can shade unusually high figures, and a column of repeated email addresses can highlight duplicates.

The distinction between appearance and data matters. A red cell is not automatically a different value, and formatting does not sort, filter, validate, or correct the data. You can still use the cell’s actual contents in formulas and charts. Formatting is a visual signal that helps people scan a sheet, so it works best when its meaning is consistent and easy to understand.

Create a basic rule

In Google Sheets in a web browser, create a rule from the conditional-formatting sidebar. The range you select determines which cells the rule can format. You can select a range first or enter it later in the sidebar.

Select the cells to format, such as B2:B50.
Choose Format > Conditional formatting.
In the sidebar, check the Apply to range field and correct it if necessary.
Under Format cells if, choose a condition, such as Is greater than, Text contains, or Is not empty.
Enter the comparison value if the selected condition asks for one.
Choose a preset style or use the formatting controls to set a fill, text color, or text style.
Click Done. To add a separate condition, choose Add another rule.

For example, to mark scores below 60 in B2:B50, choose Is less than and enter 60, then select a noticeable style. The condition applies to each cell in the selected range; it does not require a separate rule for every score. If you later add rows beyond row 50, update the range or extend it so the new scores are included.

Built-in conditions and color scales

The Format cells if menu offers common tests for numbers, text, dates, and blank cells. The exact choices depend on the type of rule, but options include comparisons such as greater than or equal to, text contains, date conditions, and whether a cell is empty. These are usually the quickest choice when each cell can be judged on its own.

A color scale is different from a single-color rule. It assigns a gradient across a range, helping readers see relative high and low values rather than pass/fail status. For instance, a scale across monthly totals can make larger amounts darker and smaller amounts lighter. A scale is most useful when the numbers are comparable and the gradient has an intuitive direction. It may mislead if a high value is not actually desirable, or if the range contains mixed measures with different meanings.

For a simple threshold, use a single-color rule: it gives a clear visual distinction between values that meet the test and those that do not. For a continuous measure, a color scale can reveal patterns at a glance. Avoid adding so many colors that readers must guess what each shade means.

Use custom formulas for more control

Choose Custom formula is when a rule needs a calculation, a comparison across columns, or a condition that built-in options cannot express. The formula should evaluate to TRUE for cells you want to format and FALSE for cells you want to leave alone. In a custom formula, references are interpreted relative to the top-left cell of the Apply to range, so that starting point is important.

To highlight an entire row when its status is Late, assume the status is in B2:B100 and the rows to format are A2:F100. Enter this custom formula:

=$B2="Late"

The dollar sign fixes the status column at B, while the row number remains relative. As the rule evaluates the next row, it checks B3, then B4, and so on. Without the dollar sign before B, the reference could shift across columns and produce unexpected results when the rule is applied to a whole row.

To identify repeated values, assume names or IDs are in A2:A100 and apply the rule to A2:A100. Use:

=COUNTIF($A$2:$A$100,$A2)>1

COUNTIF counts how often the current cell’s value appears in the list. When it appears more than once, the comparison is TRUE and the cell is formatted. This marks every occurrence of a repeated value, including its first appearance. If blank cells should not be highlighted, use this version instead:

=AND($A2<>"",COUNTIF($A$2:$A$100,$A2)>1)

For an overdue-task example, assume due dates are in C2:C100, statuses are in D2:D100, and you want to format task rows A2:D100. This formula marks a row when its date has passed and its status is not Complete:

=AND($C2<>"",$C2<TODAY(),$D2<>"Complete")

The empty-date test prevents blank due dates from being treated as overdue. TODAY updates as the date changes, so a task can become overdue without anyone editing its row. Check that the due dates are stored as dates, not text, or the comparison may not behave as intended.

Ranges and cell references

Conditional formatting depends on both the formula and its application range. If the range starts at A2, a relative reference such as A2 is evaluated from that starting position. If the range instead starts at A3 but the formula is written as though it starts at row 2, the test can be offset by one row. When a result seems shifted, compare the first cell in Apply to range with the row and column references in the formula.

Dollar signs control which parts of a reference move as the rule evaluates other cells. A reference such as $B2 keeps the column fixed but lets the row change, which is common when a status in one column controls formatting across a row. A reference such as B$2 keeps the row fixed while allowing the column to change. A reference such as $B$2 keeps both fixed, useful when comparing many cells with one threshold stored in a single cell.

For example, if a threshold is stored in F1 and values are in B2:B100, a custom rule can compare each value with that fixed threshold using =B2>$F$1. The range begins at B2, so the relative B2 reference checks each value in turn, while $F$1 stays fixed. Keeping thresholds in cells rather than hard-coding them into a rule makes it easier to update the rule’s cutoff later.

Rule order and overlapping formats

A single cell can be covered by more than one conditional-formatting rule. For example, a score could be both below a target and marked as a duplicate. Overlapping rules can make the visible result confusing, especially when they assign different colors or styles. Review the rules in the sidebar and reorder them by dragging when needed; then test cells that meet more than one condition. Do not assume that a cell’s color represents only one rule if several conditions can be true.

A useful design is to give each rule a distinct purpose. You might use red for overdue items, amber for items due soon, and a separate text style for duplicates. If two rules need to identify different situations but use the same fill, readers may not be able to tell why a cell is highlighted. Simplifying or narrowing the rules is often clearer than adding more colors.

Common problems and how to fix them

The wrong rows are highlighted: Check the first row of Apply to range and make sure the formula’s relative row number matches it. Review dollar signs to confirm which references should move.
Nothing changes: Confirm the condition is true for at least one cell, check for extra spaces or different capitalization in text, and verify that numbers and dates are stored as the intended data types.
Blank cells get colored: Add an explicit nonblank test, such as a check that the relevant cell is not equal to an empty string, before the rest of the formula.
A rule stops at the old last row: Expand Apply to range to include new data, or choose a range large enough for expected additions. A rule only formats cells in its assigned range.
The formula is rejected: Check parentheses, quotation marks, and cell references. Spreadsheet locale settings can affect whether formulas use commas or semicolons between arguments; if commas are rejected, check the file’s locale and use the separator it expects.
Different rules make the result hard to read: Open Format > Conditional formatting, inspect the rules that cover the cell, and remove or revise rules that are redundant or contradictory.

To edit a rule, reopen Format > Conditional formatting and select it in the sidebar. Change its condition, range, or style, then click Done. To remove it, select the rule and use the trash icon. Deleting a rule removes the conditional formatting, not the cell contents.

Choosing useful formatting

Choose formatting that communicates a decision or pattern, not merely decoration. A warning color should mean something specific throughout the sheet. Make text readable against the fill, and avoid relying on color alone when a label, icon, or status value can make the meaning clearer. This is especially helpful when a sheet will be printed or viewed by people with different color perception.

Test a rule against ordinary, boundary, and exceptional values. For a rule that flags scores below 60, check a score of 59, a score of 60, and a blank. For an overdue-date rule, check a past date, today, a future date, and a blank date. Boundary checks expose off-by-one mistakes and assumptions about empty cells before other people rely on the sheet.

Frequently Asked Questions

Can conditional formatting highlight an entire row in Google Sheets?
Yes. Set Apply to range to cover the rows and columns you want to shade, then use a custom formula that fixes the column containing the condition while leaving the row relative. For example, a status in column B can control formatting across A:F with =$B2="Late" when the range starts on row 2.

Does conditional formatting change the cell value?
No. It changes the cell’s appearance when a rule is satisfied. The value remains available to formulas and other spreadsheet features, and removing the rule does not delete the data.

Can I highlight duplicates in Google Sheets?
Yes. Apply a custom formula using COUNTIF to the range containing the values. Add a nonblank check if empty cells should remain unformatted, and adjust the formula’s range and starting row to match your data.

Why is my custom formula highlighting the wrong row?
The formula may be offset from the first cell in Apply to range, or its dollar signs may lock a reference that should move. Make the formula’s starting reference correspond to the top-left cell of the formatting range, then test a few rows.

Can I use more than one conditional formatting rule on a cell?
Yes. Multiple rules can cover the same cell. Review their order and styles in the conditional-formatting sidebar, and test cells that meet multiple conditions so the final appearance remains understandable.

Can conditional formatting use today’s date?
Yes. A custom formula can use TODAY() to compare a date with the current date. Because TODAY changes over time, a rule based on it can update automatically; make sure the compared cells contain actual date values.

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.