sodcalculator

10 Tech How-To

Conditional Formatting in Google Sheets: Setup Guide

Guide 10-56658 · Updated · Step by step

To set up conditional formatting in Google Sheets, select your data range, click Format > Conditional formatting, then set a rule under the “Format cells if” dropdown and choose a formatting style. This lets you automatically change cell colors, text styles, or borders based on the values or formulas you specify, making patterns and outliers instantly visible.

What Is Conditional Formatting and Why Use It?

Conditional formatting is a built-in Google Sheets feature that applies formatting (like background color, bold text, or a custom color scale) to cells that meet a condition you define. Instead of manually highlighting rows, you set a rule once, and Sheets updates the formatting live as data changes. It is widely used for:

  • Flagging low inventory levels (e.g., red when stock < 10)
  • Highlighting overdue dates (e.g., due date before today)
  • Spotting top performers with color scales (e.g., green for highest sales)
  • Finding duplicate entries in a list
  • Visualizing progress toward a goal with data bars

The feature works on both desktop browsers and the mobile app, though editing rules is easier on a computer.

How to Set Up a Basic Rule (Step-by-Step)

  1. Open your Google Sheet and select the range of cells you want to format (e.g., A2:A100).
  2. Click Format in the top menu, then Conditional formatting.
  3. In the panel that opens on the right, confirm the range under “Apply to range.” You can edit it if needed.
  4. Under “Format cells if,” choose a condition. For a simple number rule, pick Greater than and enter a value (e.g., 100).
  5. Set the formatting style: click the paint roller icon to choose a fill color, text color, or bold/italic.
  6. Click Done. Your rule is now active.

To add another rule on the same range, click Add another rule. Rules are evaluated in order; the first matching rule wins unless you check “Stop if true” for that rule.

Choosing the Right Condition Type

Google Sheets offers several condition categories. Understanding what each does helps you pick the right one:

  • Format cells if: Use for simple comparisons (greater than, less than, equal to, between, etc.) or text rules (contains, starts with, is exactly).
  • Custom formula is: Write a formula that returns TRUE for cells you want to format. This is the most flexible option—for example, =AND($A2>10, $B2<5).
  • Color scale: Applies a gradient across the range based on value. Choose a minimum, midpoint, and maximum color. Great for heat maps.
  • Data bars: Adds a horizontal bar inside each cell proportional to its value. Good for quick visual comparison.

For text-based rules, note that “contains text” is case-insensitive by default. If you need case-sensitive matching, use a custom formula with EXACT or FIND.

Using Custom Formulas (Advanced)

The “Custom formula is” option lets you build rules that go beyond simple comparisons. The formula applies to the top-left cell of the range, and relative references (like A2) adjust for each row/column. Common examples:

  • Highlight entire row if a cell in that row meets a condition: select the whole data range (e.g., A2:C100), then use =$B2>100 (note the absolute column reference with $).
  • Highlight duplicates: =COUNTIF($A$2:$A$100, A2)>1
  • Highlight dates older than 30 days: =A2<TODAY()-30
  • Highlight blank cells: =ISBLANK(A2)

Remember: the formula must return TRUE for the cell to be formatted. Use absolute references ($A$2) for fixed ranges, and relative references (A2) for per-row logic.

Common Problems and How to Fix Them

Conditional formatting often trips people up. Here are the most frequent issues and solutions:

  • Rule not applying to new rows: If you add rows below your range, they won’t be formatted automatically. Either extend the range in the rule or use a full-column range like A2:A.
  • Wrong cells highlighted: Check the “Apply to range” field. Also verify that your custom formula uses the correct absolute/relative references.
  • Rules conflicting: When multiple rules apply, the first one in the list takes priority. Reorder rules by dragging them in the panel, or enable “Stop if true” to prevent lower rules from running.
  • Formatting disappears after copy/paste: Pasting values over formatted cells does not remove the rule, but pasting formatting from another range might. Use Edit > Paste special > Paste values only to avoid overwriting rules.
  • Dates not formatting as expected: Ensure your cells are actually dates (not text). Use ISDATE() in a custom formula to check.

If nothing works, clear all rules from the range (Format > Conditional formatting > Remove rule) and start fresh.

Comparing Conditional Formatting in Sheets vs Excel

Google Sheets and Microsoft Excel both offer conditional formatting, but there are differences:

  • Interface: Sheets uses a side panel; Excel uses a dialog box with more preset options (like icon sets and top/bottom rules).
  • Custom formulas: Both support them, but Sheets uses comma as argument separator (Excel uses semicolon in some locales).
  • Color scales: Both have them, but Excel offers more gradient options and 3-color scales with more control.
  • Performance: Excel handles very large datasets more smoothly; Sheets may slow down with thousands of rules.
  • Collaboration: Sheets rules are visible and editable by all collaborators in real time; Excel desktop files are single-user unless shared via OneDrive.

For most everyday tasks, Sheets is sufficient and easier to share. For complex, multi-condition formatting on massive spreadsheets, Excel might be more robust.

Cost and Accessibility

Conditional formatting is completely free in Google Sheets. You only need a Google account, which is free. There are no premium tiers for this feature—it’s available to all users, including those with personal accounts and Google Workspace business accounts. The feature works on any device with a browser or the Google Sheets mobile app, though the mobile app has a simplified rule editor.

Frequently Asked Questions

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

Select the entire data range (e.g., A2:D100), then add a custom formula rule with =$B2>100. The dollar sign before the column letter locks the column reference, so the rule checks column B for each row and highlights the whole row if true.

Can I use conditional formatting with text that contains a specific word?

Yes. Choose “Text contains” under “Format cells if” and type the word. For case-sensitive matching, use a custom formula like =EXACT(A2, "word") or =FIND("word", A2) (which returns a number, so wrap it in ISNUMBER()).

Why is my conditional formatting not working on new rows?

Because the rule only applies to the range you originally selected. Change the “Apply to range” to include the whole column (e.g., A2:A1000) or use an open-ended range like A2:A to cover any future rows.

How do I remove conditional formatting from a sheet?

Go to Format > Conditional formatting. In the panel, you’ll see a list of rules. Click the trash can icon next to each rule to delete it, or click “Remove all rules” at the bottom to clear everything from the selected range.

Can I copy conditional formatting to another range?

Yes. Select the formatted cell, click the paint format icon (or press Ctrl+Alt+C on Windows, Cmd+Option+C on Mac), then select the target range. This copies the formatting and its rules, though you may need to adjust the range in the rule afterward.

Does conditional formatting slow down my spreadsheet?

It can, especially with many complex rules or large ranges. To keep performance good, avoid applying rules to entire columns (like A:A) when a smaller range suffices, and limit the number of rules per sheet.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →