Google Sheets Conditional Formatting If True: How It Works

Yes. In Google Sheets, conditional formatting applies a style when a cell meets a selected condition; for more flexible rules, choose Format > Conditional formatting, select Custom formula is, and enter a formula that evaluates to TRUE or FALSE. When the formula is TRUE for a cell in the selected range, Sheets applies the formatting you chose.

What “if true” means in Google Sheets

Conditional formatting is a display rule, not a change to the cell’s contents. It can change a cell’s fill color, text color, bolding, or other available style when its condition is met. The underlying value or formula remains the same, so the formatting does not affect calculations, sorting, or what is stored in the cell.

There is no separate button labeled “if true.” The condition itself determines whether the rule applies. Built-in conditions such as “Text is exactly” or “Greater than” check values for you. A custom formula lets you define the condition yourself. It must return a logical result: TRUE means apply the style, and FALSE means do not apply it.

You usually do not need to wrap a condition in an IF function. For example, a comparison already returns TRUE or FALSE. An IF formula that returns TRUE for one case and FALSE for another can work, but it is often longer and less clear than expressing the condition directly.

Set up a standard conditional-formatting rule

In Google Sheets in a web browser, select the cells you want the rule to affect, then open Format > Conditional formatting. The Conditional format rules panel appears on the right. Under “Apply to range,” check the selected range, choose a condition under “Format cells if,” choose the style, and select Done.

Select the target cells, such as B2:B50.
Open Format > Conditional formatting.
Confirm or edit the range under “Apply to range.”
Choose a built-in condition, or choose “Custom formula is.”
Set the formatting style and select Done.

For a simple threshold, a built-in condition is usually easiest. If the range B2:B50 contains scores and you want scores below 60 to stand out, choose “Less than” and enter 60. There is no need to write a formula for this straightforward test. Built-in choices also cover common needs such as text containing a word, dates in a selected period, or cells that are empty.

Use a custom formula that returns TRUE

Choose “Custom formula is” when the condition involves another cell, multiple tests, or a pattern that is not available as a built-in option. A custom formula is evaluated in relation to the cells in the apply-to range. The formula should be written for the first cell in that range; relative and absolute references then determine how it adjusts for the other cells.

For example, with due dates in B2:B100 and the rule applied to B2:B100, this formula highlights dates earlier than today while leaving blank cells alone: =AND(B2<>"",B2<TODAY()). AND returns TRUE only when both tests are true. The first test excludes empty cells, and the second checks whether the date is in the past. Dates in Sheets need to be real date values for the comparison to behave as intended; text that merely looks like a date may not compare correctly.

The formula can also use a value elsewhere in the sheet. With scores in C2:C50 and a pass mark in F1, apply the rule to C2:C50 and use =C2>=$F$1. The result is TRUE for a score at least as large as the fixed threshold in F1. The dollar signs keep F1 fixed as the rule evaluates each row, while C2 adjusts to C3, C4, and so on.

Highlight a whole row based on one cell

A common request is to color an entire record when its status matches a value. Suppose the data occupies A2:F100 and each row’s status is in column F. Set “Apply to range” to A2:F100, choose “Custom formula is,” and enter =$F2="Paid". When the status in column F is Paid, the rule formats the cells across that row.

The reference has two important parts. The dollar sign before F locks the status column so every cell in the row checks column F. The row number has no dollar sign, so it changes as the rule moves down the range. If you wrote =$F$2="Paid" instead, every row would check only F2. If you wrote =F2="Paid" without locking the column, cells farther across the row would shift the reference to G2, H2, and so forth.

For a row-level rule, confirm that the formula refers to the top row of the apply-to range. If the range begins at row 3, use row 3 in the formula. A mismatch between the range’s first row and the formula reference is a frequent source of rules that appear to highlight the wrong records.

Useful examples for common tasks

Custom formulas can express patterns that would otherwise require multiple rules. Set the apply-to range to match the cells being formatted, and adjust the references if your data starts on a different row.

Highlight duplicate nonblank IDs: With IDs in A2:A100 and that same range selected, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). The fixed search range stays put, while A2 changes for each row. The blank check prevents empty cells from being treated as repeated IDs.
Mark even-numbered rows: With a range beginning in row 2, such as A2:F100, use =MOD(ROW(),2)=0. MOD returns the remainder after division, so the condition is TRUE on even-numbered worksheet rows. This alternates by actual row number, not necessarily by the position of a record after filtering.
Highlight a task whose priority is High: If priorities are in C2:C100 and the rule applies to C2:C100, use =C2="High". To format each whole row instead, apply the rule to A2:F100 and use =$C2="High".
Flag a value above the row’s limit: If actual amounts are in B2:B100 and limits are in C2:C100, apply the rule to B2:B100 and use =B2>C2. Each amount is compared with the limit on the same row.

These examples illustrate why “Custom formula is” is useful: one rule can compare columns, combine conditions, or identify repeated values. Keep the formula as simple as the task allows, since complicated references are harder to audit when a sheet changes.

Relative references and rule ranges

Conditional formatting formulas use the same basic reference ideas as ordinary spreadsheet formulas. A relative reference such as A2 can shift as Sheets evaluates the rule across the range. An absolute reference such as $A$2 stays fixed. A mixed reference such as $A2 locks the column but lets the row change, while A$2 locks the row but lets the column change.

Think of the formula as being tested from the top-left cell in “Apply to range.” If a rule is applied to B2:B20 and the formula is =A2="Ready", the value in column A determines whether the corresponding cell in column B is formatted. This is valid even though the formula refers outside the formatted range. If the rule should check each cell in B itself, use a reference to B2 instead.

For a range with several nonadjacent areas, inspect the apply-to range carefully. The same custom formula may not behave as expected if the areas begin on different rows, because its relative references are interpreted in relation to each area. It can be clearer to create separate rules for ranges with different starting rows.

Why a rule may not highlight the cells you expect

When the result looks wrong, check the range and the formula together before changing the formatting style. A correct condition applied to the wrong range can look like a broken rule, and a correct range paired with an offset reference can format the wrong rows.

Check TRUE and FALSE directly: Put the same logical test in a spare cell temporarily. If it returns FALSE for a row you expected to match, investigate the data or references rather than the color settings.
Check the starting row: If the apply-to range starts at A3, references intended to follow each row usually start with row 3, not row 2.
Check data types: Numbers stored as text, dates stored as text, extra spaces, and inconsistent capitalization can make a comparison behave differently from what you expect. For text matching, review the actual cell contents, not just their appearance.
Check blanks and errors: Add a blank test when empty cells should not match. If source cells can contain errors, a formula that refers to them may also return an error rather than TRUE or FALSE; handle the source data or make the condition account for that possibility.
Review overlapping rules: Open the Conditional format rules panel and inspect other rules covering the same cells. When several conditions match, rule order can affect the displayed formatting. Reorder or simplify overlapping rules where necessary.
Check the style itself: The rule may be working but using a subtle fill or text color that is difficult to see against the sheet’s existing formatting.

Built-in conditions versus custom formulas

Built-in conditions are easier to set up and easier for another sheet editor to understand. Use them for a direct test on the selected cells, such as “Greater than,” “Text contains,” or “Is empty.” They present the condition as a field in the rule panel rather than asking the editor to interpret a formula.

Custom formulas are better when the condition depends on another column, a fixed reference, or more than one test. They also let you format a whole row based on a status cell. The trade-off is that a custom formula requires careful reference management: the correct row, locked columns where needed, and a range that begins where the formula expects.

For maintainable sheets, use a built-in rule when it can express the requirement clearly, and use a custom formula only when it adds necessary flexibility. Add a short note in the sheet or use clear column labels if the rule’s logic might not be obvious to the next person who edits it.

Google Sheets and Excel: a practical distinction

Google Sheets and Excel both support conditional formatting based on built-in conditions and formulas, so many simple logical tests look similar. The steps and interfaces are not interchangeable, though. In Sheets in a browser, use Format > Conditional formatting and configure the rule in the side panel. Excel for Microsoft 365 on Windows uses its own Conditional Formatting controls on the Home tab, and its rule dialogs and choices are arranged differently.

If a spreadsheet is converted or opened in another program, check custom formulas, ranges, and formatting after conversion. The same basic comparison may transfer cleanly, but feature support and interface behavior can differ. Do not assume that instructions for Excel’s ribbon describe where to find the equivalent setting in Google Sheets.

Frequently Asked Questions

Does conditional formatting in Google Sheets have an “if true” option?
No separate option uses that label. Select a built-in condition or choose “Custom formula is.” Sheets applies the chosen style when the condition is satisfied; a custom formula that evaluates to TRUE meets that test.

Do I need to use IF in a conditional-formatting formula?
Usually not. Comparisons such as =A2>10 already return TRUE or FALSE, as do functions such as AND. Use IF only when its branching result is genuinely needed; it is not required just to make a rule conditional.

How do I highlight a row when one cell says Complete?
Set the apply-to range to the whole table, for example A2:F100, and use “Custom formula is” with =$F2="Complete" if the status is in column F. Lock the column with a dollar sign and let the row number adjust.

Can a conditional-formatting formula refer to a different cell?
Yes. A rule can check a cell outside the formatted range, such as comparing a score with a threshold stored in F1. Use an absolute reference like $F$1 when the threshold should remain fixed as the rule applies to other rows.

Why does my formula return TRUE in a cell but not format the expected row?
Check that the formula is based on the first row of the apply-to range and that relative and absolute references are set correctly. Also review overlapping rules, the exact cell contents, and whether the selected range includes the row you expected 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.