Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 29 ยท Working with Data

Conditional Formatting

Highlight cells automatically in Google Sheets with conditional formatting: single-color rules, color scales, custom formulas and duplicate checks.

Conditional formatting changes how a cell looks based on its value or on a formula. A late invoice turns red, the top scores turn green, a repeated email address is highlighted. The data itself never changes; only the colors do, and they update the moment the data does.

This lesson covers the rule types, the custom formula rule that handles almost every special case, color scales and duplicate detection. Rules are set up through menus, so the real steps appear as text. Small JavaScript stand-ins show how a rule decides which cells to color, which is the key to writing correct custom formulas.

Three kinds of rule

Single color A condition picks cells to paint.
Color scale Shades follow the value.
Custom formula Any TRUE or FALSE test you can write.
1 / 2

Creating a rule

Select the cells, then choose Format โ†’ Conditional formatting. A side panel opens with two tabs.

  • Single color: under Format cells if..., pick a condition such as Greater than, Text contains, Date is before, Is empty or Custom formula is. Enter a value, then choose the formatting style (fill color, text color, bold, underline, strikethrough) and press Done.
  • Color scale: choose a minimum, optional midpoint and maximum color. Each point can be the Min/Max value, a Number, a Percent or a Percentile.

Several rules can stack on one range. They are evaluated from top to bottom, and when two rules style the same property of a cell, the higher rule wins. Drag rules in the panel to reorder them. The same panel lists every rule on the sheet, so you can find out why a cell is red.

Remember that conditional formatting is applied on top of normal formatting from Formatting Cells. Clearing a cell's normal formatting does not remove a conditional rule.

Google Sheets cannot run on this page, so the menus above are described, not interactive. The JavaScript blocks are stand-ins: they apply the same kind of test to a small table so you can see which cells a rule would paint.

Rules based on values

Common single-color setups, with the condition and where it applies:

Goal Range Condition
Highlight sales above target C2:C50 Greater than 500
Mark overdue dates D2:D50 Date is before today
Flag missing entries B2:B50 Is empty
Find a keyword A2:A50 Text contains urgent

Conditions can point at another cell instead of a typed number: enter =$F$1 as the value and the threshold becomes a cell you can edit.

JS Stand-in: a value rule paints cells

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Color scales

A color scale shades every cell by where its value sits between the minimum and the maximum. It suits heatmaps: monthly sales by region, temperatures, survey scores. A typical setup is red at the minimum, white or yellow at the 50th percentile and green at the maximum. Use Percentile for the midpoint rather than a fixed number, so one extreme value does not wash out all the other shades.

Under the hood each cell gets a blend of the two nearest colors in proportion to its position. The stand-in computes that position:

JS Stand-in: shade by position in the range

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Custom formula rules

Choose Custom formula is when a built-in condition is not enough, for example to color a whole row. The formula must return TRUE or FALSE, and you write it for the first cell of the range. Sheets then shifts the references for every other cell, exactly like copying a formula (see Relative, Absolute and Mixed References).

Say the range is A2:C20 and you want rows with sales over 500 in column C highlighted:

TEXT
=$C2>500

The $ before C locks the column, so every cell in a row looks at column C. The row number has no $, so each row looks at its own row. Without the lock (=C2>500) only column C would match.

More useful custom formulas, each applied to the stated range:

TEXT
=$D2<TODAY()                          row where the due date has passed
=AND($D2<TODAY(), $E2<>"Done")        overdue and not finished
=MOD(ROW(), 2) = 0                    zebra stripes on even rows
=$A2=$A1                              repeat of the value above
=ISBLANK($B2)                         same idea as the "Is empty" rule

A frequent mistake is writing the formula for the wrong cell: if the range starts at A2 but your formula refers to row 1, every comparison is off by one row.

JS Stand-in: why the dollar sign matters

Press Run (or Ctrl + Enter) to see the console output here.

No output.

In the first output the whole row is highlighted. In the second, the column reference slides to the right with each cell, so only the first cell of each row, the one that still points at column C, is highlighted.

Highlighting duplicates

Duplicate detection is a custom formula with COUNTIF (the next lessons cover it in depth, see SUMIF, COUNTIF, AVERAGEIF and Friends). For emails in A2:A100:

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

The range is locked completely ($A$2:$A$100) so it is the same for every cell, while $A2 follows the row. Every value that appears more than once is highlighted, including its first appearance. To mark only the second and later copies use an expanding range: =COUNTIF($A$2:$A2, $A2) > 1. To ignore blanks, add a test: =AND($A2<>"", COUNTIF($A$2:$A$100, $A2) > 1).

JS Stand-in: first copy versus later copies

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Tips

  • Apply rules to whole columns (C2:C) if rows will be added later.
  • Use the Remove rule trash icon rather than deleting colors by hand.
  • Copying cells with Paste special โ†’ Conditional formatting only copies the rules without the data.
  • A rule referencing another sheet needs INDIRECT("Sheet2!A1"); direct references to other sheets are not accepted in the rule formula.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A conditional formatting rule changes which part of a cell?

    Conditional formatting only changes appearance. The cell's value and formulas stay as they are.
  2. 2. A custom formula rule is applied to A2:C20 to color whole rows where column C is over 500. Which formula is right?

    $C locks the column so every cell checks column C, while the unlocked row number follows each row.
  3. 3. Two rules style the same cell with different fills. Which fill is shown?

    Rules are evaluated top to bottom and the higher rule wins for a conflicting property.
  4. 4. Which formula highlights only the second and later copies of a value in A2:A100?

    The expanding range $A$2:$A2 counts only the cells up to the current row, so the first copy counts once and is not flagged.

Summary

  • Conditional formatting colors cells by value or formula and never changes the data.
  • Rules are checked per cell, top to bottom; the higher rule wins on conflicts.
  • Color scales shade by position between min, midpoint and max; percentiles resist outliers.
  • Custom formulas are written for the first cell of the range; lock columns or rows with $ to color whole rows.
  • COUNTIF with a locked range finds duplicates; an expanding range marks only later copies.

Next: Data Validation and Dropdowns