How to Use Conditional Formatting in Google Sheets

Make cells color themselves based on what's in them — highlight overdue dates, flag numbers over a target, build a heat map. Here's the basic rule, a color scale, and the custom-formula trick for "based on another cell."

By the Quiriz Team · Published July 25, 2026 · 4 min read

The quick version: select your cells, go to Format → Conditional formatting, set a condition (like "Greater than 1000"), pick a color, and click Done. Here's that plus color scales and the "based on another cell" formula.

Color by value

Select the range, open Format → Conditional formatting, and under Single color choose a condition — Greater than, Text contains, Date is before, Is empty — enter the value, pick a fill, and click Done. The color updates live as values change.

A color scale (heat map)

Switch to the Color scale tab and Sheets shades cells along a gradient from low to high — an instant heat map, no formula needed.

Based on another cell (custom formula)

To color a cell by comparing it to another cell, choose Custom formula is. To highlight column B when it exceeds column C, on each row:

=$B2 > $C2

The $ before the column locks it so the rule applies row by row. This is how you flag "actual over budget," "past due," and similar.

Remove a rule

Open the panel, hover the rule, and click the trash icon.

Analyze your Sheets data with AI

Ask questions about your Google Sheets in plain English and get the answer in the cell with =QUIRIZ(). Free to start.

Try Quiriz free →

Frequently asked questions

How do I highlight cells based on their value in Google Sheets?

Select the range, go to Format > Conditional formatting, choose a condition such as "Greater than" or "Text contains", enter the value, pick a color, and click Done.

How do I format a cell based on another cell?

Use a custom formula. Select the range, choose "Custom formula is", and enter a formula referring to the other cell — e.g. =$B2 > $C2 formats column B's cell when it exceeds column C's. Use $ to lock the column so it applies per row.

How do I create a color scale (heat map)?

In Format > Conditional formatting, switch to the Color scale tab. Sheets colors cells along a gradient from lowest to highest value — a quick heat map with no formula.

How do I remove conditional formatting?

Open Format > Conditional formatting, hover the rule you want to delete, and click the trash icon.