Google Sheets Conditional Formatting: If-Then Rules

In Google Sheets, an if-then conditional formatting rule checks whether a condition is true and automatically changes a cell’s appearance when it is. Select a range, open Format > Conditional formatting, then choose a built-in condition or use a custom formula for more specific logic.

What “if-then” means in Google Sheets

Conditional formatting is a visual rule, not a spreadsheet formula that writes a result into a cell. The “if” is a test—such as whether a number is below zero or a status says Overdue—and the “then” is the formatting Sheets applies if that test passes. You can change text color, fill color, bolding, or other available style options in the formatting panel.

For example, a task tracker might show overdue items in red. The rule checks a due date and a completion status; when the conditions are met, Sheets highlights the relevant cells or row. The underlying date and status remain unchanged. If you need the sheet to write words such as Late or Yes, use a regular formula in a cell instead of conditional formatting.

Built-in rules cover common tests, including whether a cell contains text, is blank, is greater than a number, or falls within a date condition. Custom formulas let you combine tests, refer to other columns, and format a whole row based on one cell.

How to create a basic if-then rule

In Google Sheets in a web browser, select the cells to format and choose Format > Conditional formatting. The Conditional format rules panel opens on the right. Under Apply to range, check that the selected cells are the ones you intend to affect. Under Format rules, choose a condition, enter a value if requested, and select the formatting style under Formatting style.

Select the target range, such as C2:C50.
Choose Format > Conditional formatting.
Under Format rules, choose a rule such as Greater than, Text contains, or Is not empty.
Enter the comparison value when the rule asks for one.
Choose a fill or text style, then select Done.

As a straightforward example, if cells C2:C50 contain scores and you want scores below 60 to stand out, set Apply to range to C2:C50, choose Less than, enter 60, select a color, and click Done. The rule is effectively “if a cell in this range is less than 60, then apply this style.” This is easier to maintain than a custom formula when one simple comparison is all you need.

When to use a custom formula

Choose Custom formula is in the Format rules menu when the condition depends on another cell, combines several tests, or should format an entire row. The custom formula must evaluate to TRUE or FALSE for the cell or row being considered. The important detail is that references are interpreted relative to the first cell in the Apply to range.

Suppose a task table has task names in A, due dates in B, and status in C, with headers in row 1. To color the whole row when a task is overdue and not complete, apply the rule to A2:C100. With task names in A2:A100, due dates in B2:B100, and statuses in C2:C100, use this custom formula:

=AND($B2<TODAY(),$B2<>"",$C2<>"Complete")

Choose a highlight style and click Done. AND requires every test to be true: the due date is earlier than today, the date cell is not blank, and the status is not Complete. The dollar signs lock the column references B and C, while the row number remains relative. As Sheets evaluates the rule down the range, it checks B2 and C2 for the first row, then B3 and C3 for the next. Without the dollar signs, a row-wide range can shift the references sideways and produce confusing results.

The blank-date check matters because empty cells can behave unexpectedly in comparisons. It prevents a row with no due date from being treated as overdue. If your status wording is different, replace Complete with the exact status used in your sheet.

Useful examples you can adapt

Custom formulas are useful for common spreadsheet patterns beyond task tracking. In each case, set the Apply to range to match the cells you want to format, and use the first row of that range in the formula. For example:

Highlight duplicate entries: With values in A2:A100 and that same range selected, use =COUNTIF($A$2:$A$100,A2)>1. Any value appearing more than once meets the condition.
Highlight a whole row for a priority: With records in A2:D100, priority in column D, and the full range selected, use =$D2="High". The fixed column makes every cell in each row check its priority value.
Flag a number outside an acceptable range: With measurements in B2:B100, use =OR(B2<10,B2>50) to format values below 10 or above 50.
Highlight a cell when another cell is filled: With results in C2:C100 and a related note in B2:B100, apply the rule to C2:C100 and use =$B2<>"". A result cell is formatted when its corresponding note is not blank.

These examples use row 2 because each selected range begins in row 2. If your range begins in row 5, adjust the relative row references to 5. For duplicate checking, also decide whether the header should be excluded; applying the rule only to the data rows avoids treating a heading as an entry.

Choosing between built-in rules and formulas

Use a built-in rule when the question is about the selected cell itself and the test is simple. “Is greater than 100,” “Text contains urgent,” and “Is empty” are easy to read in the panel and do not require formula references. Built-in rules are often the clearest choice for a sheet that other people will maintain.

Use a custom formula when the decision uses multiple conditions, a different column, or a row-level relationship. For example, a built-in rule can identify dates before today, but a custom formula is more suitable when the date should count as overdue only if the status is not Complete. A formula can also combine tests with AND, which requires all conditions to pass, or OR, which requires at least one to pass.

Conditional formatting can also use color scales to show relative values across a range. A color scale is useful for spotting high and low values at a glance, but it is not the same as a simple if-then rule: it assigns a gradient based on the values in the range rather than applying one style only when a yes-or-no test is true.

Rule order, ranges, and overlapping conditions

A cell can meet more than one conditional formatting rule. For example, one rule might highlight all values below 100 in yellow, while another highlights values below 50 in red. When rules overlap, review their order in the Conditional format rules panel; Sheets uses the first applicable rule in the list to determine the formatting when multiple rules conflict. Keep related rules organized and use styles that make their intent clear.

Apply to range controls where the formatting can appear; the formula controls which cells qualify. These are separate decisions. If you want to color A2:D100 based on a status in D, apply the rule to A2:D100 and write the formula to check column D. If you apply the rule only to D2:D100, only the status cells can be formatted.

When expanding a table, check whether the Apply to range includes the new rows. A rule applied to A2:D100 will not necessarily cover records added in row 101 or below. You can edit the range in the panel to include more rows, or choose a suitably larger range when setting up the rule. Avoid including entire columns without a reason if the sheet is large; a more focused range can be easier to inspect and manage.

Common problems and how to fix them

Nothing changes: Confirm that the condition is true for at least one cell, that the rule applies to the intended range, and that the chosen text matches the data. Also check that the formatting style is visibly different from the existing cell style.
The wrong row is highlighted: Match the formula’s row number to the first row in Apply to range. For a range starting at row 2, use row 2 in the relative part of the formula, not the header row.
Only one column changes: Expand Apply to range to the full row or block you want formatted. A formula that checks another column does not automatically make the formatting apply across that row.
Blank rows are highlighted: Add a not-blank test for the relevant date or key field. This is especially useful when comparing dates or numbers in partially completed records.
A formula shows an error: Check parentheses, quotation marks around text, and whether the formula begins with an equals sign. In some spreadsheet locales, separators may differ; use the separator format expected by your Google Sheets locale.

To inspect or change a rule, select a cell in its range and open Format > Conditional formatting. The panel shows rules that apply to the selected cells. You can edit the range, change the condition or style, add another rule, or remove a rule. If a result looks wrong, checking the range and the relative row reference first usually narrows down the cause.

Google Sheets versus Excel

The basic idea is similar in Google Sheets and Microsoft Excel: a condition controls visual formatting, and both applications support rules based on formulas. The menu paths and rule-management panels differ. In Sheets in a browser, use Format > Conditional formatting. In Excel for Microsoft 365 on Windows, conditional formatting is on the Home tab, under Conditional Formatting. Do not assume that instructions for one program’s dialog match the other program’s controls exactly.

Many straightforward formulas, including AND, OR, COUNTIF, and TODAY, are available in both programs, but settings, supported features, and formula separators can vary by app, version, and locale. If a workbook is shared between Excel and Sheets, review important conditional formatting after conversion; a formula or visual rule may not transfer with identical behavior. For a sheet that will stay in Google Sheets, build and manage the rule in Sheets itself.

Frequently Asked Questions

Can Google Sheets do if-then formatting?
Yes. Use a built-in condition for a simple test or choose Custom formula is for a formula-based test. When the test is true, Sheets applies the style to the range specified by Apply to range.

Can I highlight an entire row based on one cell?
Yes. Apply the rule to the full table range, then use a custom formula that locks the column containing the condition while leaving the row relative. For example, a rule applied to A2:D100 can check whether column D in each row says High.

Does conditional formatting change the cell’s value?
No. It changes how a cell looks, not its stored contents. To display a result such as Late in another cell, use a spreadsheet formula there; conditional formatting can then style that result if needed.

How do I make a rule for two conditions?
Use AND when both conditions must be true, or OR when either condition is enough. For instance, an overdue task rule can check that its date is before today and its status is not Complete.

Why is my custom formula highlighting the wrong cells?
Check that the formula’s relative row matches the first row of the Apply to range, and use dollar signs to lock columns or rows that should not shift. Also verify that the rule applies to the cells you expect to format.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.