Google Sheets Conditional Formatting With IF

Yes. In Google Sheets, use Format > Conditional formatting, choose Custom formula is, and enter a formula that evaluates to TRUE or FALSE. You can use IF, but often a shorter direct comparison—such as =$B2="Overdue"—is easier to read and works just as well.

What conditional formatting with IF means

Conditional formatting changes a cell’s appearance when its contents meet a rule. The value itself does not change: a rule can make an overdue date red, for example, without replacing the date or adding a separate status value. The formatting is recalculated as the sheet’s data changes.

In a custom formula rule, Google Sheets checks whether the formula returns TRUE for each relevant cell or row. A formula can test the cell being formatted, another cell in the same row, or a range of values. IF is one way to produce a TRUE or FALSE result, but it is not required. A comparison such as =$B2="Overdue" already returns TRUE when the condition is met and FALSE otherwise.

Use IF when the rule is clearer with an explicit choice between outcomes, or when the condition needs to be expressed as a calculation. For a simple test, avoid adding IF just because the feature is called conditional formatting; a direct test is generally shorter and less likely to contain an unnecessary argument.

How to add a custom formula rule

In Google Sheets in a web browser, select the cells that should receive formatting, then open Format > Conditional formatting. The conditional-formatting panel appears on the right. Under Apply to range, confirm the selected cells, then choose Custom formula is from the rule-condition menu.

Select the target range, such as A2:D100 if each row is a record and the whole row should be highlighted.
Open Format > Conditional formatting.
Choose Custom formula is under Format rules.
Enter the formula, choose a text color, fill color, or other available formatting, and select Done.

Suppose column B contains a task status, and you want the entire row from A through D highlighted when the status is Overdue. With statuses in B2:B100 and the formatting range A2:D100, enter =$B2="Overdue". The dollar sign fixes the status column at B, while the row number remains relative. Sheets checks B2 for the first row, B3 for the next, and so on.

If you select a range that starts on row 2, make sure the formula’s row reference also starts at 2. A mismatch—for example, applying a rule to A2:D100 but referring to B1—can make the colors appear one row out of place.

Using IF in a conditional-formatting formula

IF has the structure =IF(condition,value_if_true,value_if_false). It tests a condition and returns one result if it is true and another if it is false. For a conditional-formatting rule, the final result needs to function as a TRUE/FALSE test. For example, with task statuses in B2:B100 and a formatting range of A2:D100, this formula highlights rows whose status is Overdue:

=IF($B2="Overdue",TRUE,FALSE)

This works, but it is equivalent to =$B2="Overdue". In both cases, the rule is true only when the status matches. The shorter version is easier to inspect and is the better choice for a straightforward comparison.

IF can be useful when the condition itself has multiple parts. For example, with due dates in C2:C100 and a formatting range of A2:D100, this formula marks a row when its due date is in the past and it is not yet complete:

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

Here, AND requires both tests to be true. The date must be earlier than today, and the status must not equal Complete. The IF wrapper still does not change the logic, so the more direct version, =AND($C2<TODAY(),$B2<>"Complete"), is usually preferable. Check that the date cells contain actual dates rather than text that only looks like a date.

Relative and absolute references

References are central to custom formulas because Sheets adjusts them as it evaluates cells across an applied range. A relative reference such as B2 can shift by row and column. A dollar sign locks the part immediately after it: $B2 keeps the column fixed but allows the row to change; B$2 keeps the row fixed but allows the column to change; and $B$2 fixes both.

For row-based rules, the usual pattern is to lock the column containing the test value and leave the row relative. For example, with priority values in column C and a range A2:E50 to format, use =$C2="High". Sheets checks column C for each row and formats all selected cells in that row when the priority is High.

For a rule comparing every cell against one fixed threshold, lock both parts of the threshold reference. For example, if the target value is stored in H1 and the cells being evaluated are D2:D50, =D2>=$H$1 compares each D cell to the same H1 value. The compared cell is relative; the reference threshold is absolute.

Useful examples for common spreadsheet tasks

Custom formulas can express rules that are more specific than the built-in options. These examples illustrate common patterns; adjust the ranges and columns to match the layout of your own sheet.

Highlight a row by status: With statuses in B2:B100 and the applied range A2:D100, use =$B2="Complete" to format completed rows.
Flag a past-due date: With dates in C2:C100 and the applied range C2:C100, use =AND(C2<TODAY(),C2<>""). The second test prevents empty cells from being treated as overdue.
Highlight duplicate entries: With IDs in A2:A100 and the applied range A2:A100, use =COUNTIF($A$2:$A$100,A2)>1. Each value appearing more than once is marked, including its first occurrence.
Flag either of two labels: With labels in D2:D100 and the applied range A2:D100, use =OR($D2="High",$D2="Urgent") to format rows with either priority.
Shade alternating rows: With an applied range A2:D100, use =ISEVEN(ROW()) for even-numbered sheet rows. If the range starts on a different row, the parity follows the actual sheet row numbers.

For duplicates, decide whether you want every occurrence marked or only later repetitions. The COUNTIF rule above marks all copies because each one has a count greater than one. If blanks are present in the ID range, consider adding a nonblank test so empty cells are not treated as repeated values.

Choosing a built-in rule or a custom formula

Google Sheets offers ready-made conditional-formatting conditions for common needs, including text matching, numeric comparisons, dates, and whether a cell is empty. Choose one of these when it expresses the requirement exactly; a built-in condition is often quicker to set up and easier for another editor to understand.

Choose Custom formula is when the rule must refer to another column, combine several tests, or format a range based on a row-level condition. For instance, a built-in date condition can flag dates before today in the date column. A custom formula is more useful if you want the whole row colored only when that date is overdue and the status is not Complete.

Color scale rules are different from TRUE/FALSE rules. They apply a gradient based on values across a range, such as low-to-high scores, rather than checking one custom logical condition. Use a color scale to show relative magnitude; use a single-color rule when a specific condition should trigger a clear visual signal.

Common problems and how to fix them

The wrong row is formatted: Align the formula’s starting row with the first row in Apply to range. For a range beginning at A2, a row-based condition normally refers to row 2.
Only one cell or column responds: Check Apply to range. To color an entire row, select the row’s full data range and use a formula that checks the relevant column.
The rule never triggers: Check spelling, spaces, and data types. A date stored as text may not compare as a date; a status with an extra space may not match the text in the formula.
Blank cells are highlighted: Add a test that excludes blanks, such as C2<>"" inside AND, when the rule depends on a date or value being present.
The result seems shifted across columns: Use a dollar sign before a column letter when the test should always use the same column, as in $B2.
Formulas are rejected: Confirm the condition is set to Custom formula is and the formula begins with an equals sign. Spreadsheet locale settings can affect function names, date interpretation, and whether formulas use commas or semicolons between arguments.

Also inspect other rules applied to the same cells. Several conditions can overlap, and the order shown in the conditional-formatting panel can affect which formatting is visible. Reorder rules or make their conditions mutually exclusive if two colors compete for the same cells. When editing an existing rule, verify the full applied range rather than assuming it covers newly added rows.

How Google Sheets differs from Excel

The basic idea is familiar in both programs: choose a range, create a conditional-formatting rule, and use a logical formula when built-in conditions are not enough. In Google Sheets, the browser menu path is Format > Conditional formatting, and the custom formula option is called Custom formula is. Excel for Microsoft 365 on Windows uses Home > Conditional Formatting; its rule dialog offers formula-based rules, but the menus and dialog labels differ.

Do not assume that a rule copied between programs will behave identically without checking it. References, range boundaries, locale settings, and the available formatting options can matter. If a workbook is shared or converted between Sheets and Excel, verify the rule in the program where people will use it, especially when the rule contains several conditions or refers to other sheets.

Frequently Asked Questions

Can I use IF in Google Sheets conditional formatting?
Yes. Select Custom formula is and enter a formula that returns a logical result. IF works, but a direct comparison is usually shorter when you only need to test one condition.

Does conditional formatting change the cell’s value?
No. It changes how a cell looks when a condition is true. To write a status or calculated result into a cell, use a regular spreadsheet formula or enter a value separately.

How do I highlight an entire row based on one cell?
Apply the rule to the full row range, such as A2:D100, and lock the condition’s column while leaving its row relative. For a status in column B, =$B2="Overdue" checks the status for each row.

Can a conditional-formatting formula use another cell?
Yes. A custom formula can refer to cells outside the cell currently being formatted. Use relative or absolute references deliberately so that the reference moves—or stays fixed—as the rule is evaluated.

Why does my IF formula work in a cell but not in conditional formatting?
Check that you chose Custom formula is, that the formula begins with an equals sign, and that its references match the first row of the applied range. The rule needs to evaluate as a logical condition; returning text such as Red does not itself select a formatting color.

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.