How to Use Conditional Formatting in Excel & Sheets

Conditional formatting automatically colors a cell when a rule you set is true. Select your data, open the conditional formatting menu, choose a built-in rule or write a formula, and pick a format — from then on the colors update themselves whenever the values change. Over-budget rows turn red, finished tasks turn green, duplicates light up, and heatmaps show trends without a chart.

What it’s most useful for

• Spotting outliers — sales below target, expenses over budget.
• Finding duplicates — repeated emails, invoice numbers, or customer IDs.
• Tracking status — colouring an entire row once a task is marked “Done”.
• Showing trends — colour scales and data bars that shade low-to-high values.

Built-in rules, step by step

In Google Sheets

1. Select the range (for example A2:D100).
2. Click Format → Conditional formatting. The panel opens on the right.
3. Under Format rules, choose a rule from the Format cells if dropdown.
4. Enter the value the rule needs (a number, text, or date).
5. Set the Formatting style — fill colour, text colour, bold, or italic.
6. Click Done. Add more rules with Add another rule.

In Excel

1. Select the range.
2. Click Home → Conditional Formatting.
3. Pick a category: Highlight Cells Rules, Top/Bottom Rules, Data Bars, or Color Scales.
4. Choose the preset (for example Greater Than…), type the value, pick a format, and click OK.
5. For full control, choose New Rule → Use a formula to determine which cells to format.

Highlight cells greater or less than

Choose the rule, enter the threshold, and pick a fill. “Greater than 1000” highlights strong sales; “less than 0” flags negatives. Excel groups these under Highlight Cells Rules; Sheets lists them in the dropdown.

Between

“Between” highlights anything inside two numbers — useful for a target band like 80–100. Boundary values are included.

Text that contains

Use Text contains to mark cells holding a keyword such as “Refund” or “Urgent”. It’s a case-insensitive match in both tools.

Duplicate values

Google Sheets has Custom formula is with =COUNTIF(A:A,A1)>1, . Excel has Duplicate Values…; run it on a single column to avoid false matches across unrelated columns.

Colour scales

A colour scale shades each cell along a gradient from the lowest value to the highest, creating a quick heatmap. Reverse the default green-to-red when low is good.

Data bars

Data bars draw a horizontal bar inside each cell proportional to its value, like a tiny in-cell bar chart — ideal for comparing rows you’re already sorting.

Formula-based rules (the real power)

Built-in rules cover one condition each. A custom formula applies any logic, including references to other columns. In Google Sheets, choose Custom formula is; in Excel, New Rule → Use a formula to determine which cells to format.

1. Write the formula for the top-left cell of your range, and the tool shifts it for every other cell.
2. Use $ to lock whichever part shouldn’t move: $A1 locks the column, A$1 the row, $A$1 both.

Example 1: Highlight an entire row when a status says “Done”

Select A2:D100, then use:

=$D2="Done"

Because $D is locked but the row 2 is not, every cell in a row checks that row’s column D, so the whole row fills green when D says “Done”. Without the $, the reference drifts and only column D highlights.

Example 2: Highlight overdue dates

Select the due-date column (say A2:A100) and use:

=A2<TODAY()

TODAY() recalculates on every open, so a task turns red the day after its due date with no manual updates. Add =A2="" as a second rule to leave blank cells alone.

Example 3: Highlight a whole row from a checkbox

If column A holds a checkbox (TRUE/FALSE), select the data range and use =$A2=TRUE. Google Sheets checkboxes store booleans, so checked rows shade grey as a live to-do list. Excel works the same way if you use the native checkbox (Microsoft 365) or a Form Control linked to a cell - both write TRUE/FALSE.

Ready-made formulas to copy

Managing rules: order, priority, and scope

Rules don’t all get along. When two rules touch the same cell, the one higher in the list wins and can hide the rule below it.

• Reorder in Google Sheets — drag rules up or down in the panel; the top rule has priority.
• Reorder in Excel — use Conditional Formatting → Manage Rules and the up/down arrows.
• Stop If True (Excel) — tick it so that once a rule matches, lower rules are ignored — this is how “Done = green” wins over “overdue = red”.
• Edit or delete — click a rule in the Sheets panel, or double-click it in Excel’s Manage Rules; use the trash icon / Delete Rule to remove it.
• Apply to a whole column — set the range to A2:A (Sheets) or $A:$A (Excel) to cover new entries.

Excel vs Google Sheets: what’s different

Both apply formatting live, so nothing is re-run when the numbers change. To feed rules with dynamic data, see Google Sheets formulas for beginners and how to use VLOOKUP in Excel.

FAQ

Does conditional formatting update automatically?

Yes. Both re-evaluate rules whenever a cell changes, so colors always reflect the current data.

How do I highlight an entire row based on one cell?

Select the full row range and use a custom formula with the controlling column locked — =$D2="Done" when the status is in column D. The $ keeps every cell pointing at column D.

Why doesn’t my formula rule work?

Write the formula for the top-left cell of the range; relative references move from there. A mismatch between the range and the formula — or a missing $ that lets the reference drift — is the usual cause.

How do I remove all conditional formatting?

In Excel, click Home → Conditional Formatting → Clear Rules → Clear Rules from Entire Sheet. In Google Sheets, open Format → Conditional formatting and delete the rules one by one.