Conditional Formatting in Google Sheets: A Practical Guide

by

Conditional formatting changes a cell’s appearance when its value meets a rule. Use it to spot overdue work, low scores, completed tasks, and important values without manually recoloring your sheet.

Start with a built-in condition for one cell range. Use a color scale for a visual ranking, and use a custom formula when one cell should control a full row.

What conditional formatting can do

A rule checks each cell in an applied range. When the condition is true, Google Sheets applies the style you chose, such as a fill color, text color, or bold text.

Single color rules work well for clear pass-or-fail conditions. For example, you can color scores below 80 red or mark a cell that contains the word “Blocked.”

Color scales suit a range of numbers. They shade lower and higher values differently, helping you compare a column without adding a helper formula.

Custom formulas are for rules that depend on another cell. They can format an entire project row when its status changes, as shown in this full-row conditional formatting tutorial.

Create a conditional formatting rule

Use this workflow to format cells that meet one straightforward condition, such as a score below a threshold or text that contains a word.

  1. Select the cells you want the rule to check. Select only data cells when the header should stay unchanged.
  2. Choose Format > Conditional formatting. The Conditional format rules sidebar opens on the right.
  3. Under Format cells if, choose a condition such as Text contains, Less than, or Date is before.
  4. Enter the comparison value or text when Sheets asks for it.
  5. Choose the fill, text, or other formatting style, then click Done.

Sheets immediately checks the selected range. Edit the rule later from the sidebar if the comparison value, applied range, or style needs to change.

Google Sheets Format menu with Conditional formatting highlighted.

Use a built-in rule for text, dates, or numbers

Built-in conditions are the quickest choice when every cell can be judged by its own value. They keep the rule readable because you do not need to write a formula.

For example, select B2:B6 containing scores and choose Less than. Enter 80 to format 72, 68, and 77 while leaving 91 and 84 unchanged.

Text conditions can use Text contains, Text is exactly, or Text starts with. Pick “contains” only when the match can appear within a longer cell value.

Date conditions include before, after, and is. Make sure the source cells contain real dates, because text that only looks like a date can produce an unexpected result.

Choose a visible but restrained style after selecting the condition. If you only need a manual fill instead, see the fill color options and steps.

Use a color scale for a quick visual ranking

Use a color scale when the relative position of each number matters more than a single cutoff. It is useful for sales totals, test scores, response times, and budget variance.

The example copies the same scores into column C so you can compare the gradient with the less-than rule in column B.

These steps add a gradient across one numeric range.

  1. Select the copied numeric cells in C2:C6.
  2. Choose Format > Conditional formatting.
  3. In the sidebar, select the Color scale tab.
  4. Choose a preset, or set the minimum, maximum, and optional midpoint values and colors.
  5. Click Done.

The colors describe the selected range, not a fixed business meaning. Set explicit minimum and maximum values when the same color should represent the same threshold in different reports.

Google Sheets color scale for a range of scores.

Format an entire row with a custom formula

A custom formula lets one cell decide whether every cell in its row receives a style. The formula must return TRUE for rows you want to format.

In this task list, column D contains the status. Apply the rule to A2:D5 so the task, owner, due date, and status use one consistent highlight.

=$D2="Done"

The dollar sign locks column D. The row number stays relative, so Sheets checks D2 for row 2, D3 for row 3, and continues that pattern through the range.

In the verified fixture, only the Review budget row had the status Done, so only A3:D3 received the selected green fill.

Google Sheets custom conditional formatting formula highlights the completed task row.

Use these steps to create the row rule.

  1. Select the full output range, A2:D5, without the header row.
  2. Choose Format > Conditional formatting.
  3. Choose Custom formula is from Format cells if.
  4. Enter =$D2="Done", choose a style, and click Done.

Selection matters. Start the formula with the first data row in the applied range. If your range begins at row 2, use $D2, not $D1.

For a different row condition, change the test after the equals sign. For example, =$D2="Blocked" highlights every row whose status is Blocked.

Make rules predictable as the sheet grows

Keep the applied range as tight as practical. A rule covering unused columns or thousands of empty rows can make the sheet harder to maintain and can add calculation work.

When a rule seems wrong, first compare its applied range with its formula’s first reference. Then check whether another rule above it uses a competing style.

Use absolute and relative references intentionally. $D2 locks the status column while moving down rows; $D$2 would test the same single cell for every row.

Apply a finished visual treatment elsewhere with Paint Format in Google Sheets. That copies formatting but does not replace a conditional rule’s logic.

Other Google Sheets articles you may also like

Leave a Comment