Google Sheets Custom Conditional Formatting: A Guide

Yes. In Google Sheets, custom conditional formatting lets you apply a style when a formula evaluates to TRUE: select a range, open Format > Conditional formatting, choose Custom formula is, enter the formula, and set the formatting style. It is useful for rules based on other cells, whole-row highlights, duplicates, and conditions that the built-in options do not cover.

What custom conditional formatting does

Conditional formatting changes how cells look when their contents meet a condition; it does not change the underlying values. A rule can apply a fill color, text color, bolding, or other available formatting. Custom formulas make the condition more flexible than choices such as “Text contains” or “Greater than.” They can check more than one cell, combine tests, or base a highlight on a value elsewhere in the sheet.

For example, a built-in rule can highlight a cell containing the word “Late.” A custom formula can highlight every cell in a row when the row’s status column says “Late,” even though the other cells contain names, dates, or amounts. This distinction—testing one cell while formatting a wider range—is one of the most useful reasons to learn the custom formula option.

Rules are visual aids, not data validation or protection. A red overdue row does not prevent someone from editing its date, and it does not automatically send a reminder. Use data validation or access controls when you need to restrict entries or changes.

How to add a custom formula rule

In Google Sheets in a web browser, use the following steps:

Select the cells you want the rule to format. You can also enter or confirm the range later in the conditional-formatting panel.
Choose Format > Conditional formatting. A rule editor opens on the right.
Under the Single color tab, check the Apply to range field and correct it if needed.
Open the Format cells if menu and select Custom formula is.
Enter a formula beginning with an equals sign, then choose the fill, text, or other formatting style.
Click Done. To change or remove the rule later, open Format > Conditional formatting and edit or delete it in the panel.

The custom formula must be a valid Google Sheets formula that returns TRUE or FALSE for the cell or row being evaluated. A formula that returns TRUE applies the chosen style; one that returns FALSE does not. You can use ordinary comparisons, functions such as AND and OR, and references to other cells. The formula is a condition, not a formula that writes a result into the selected cells.

How formulas relate to the selected range

The formula is interpreted relative to the top-left cell of the Apply to range. Suppose the range is A2:D100 and you want to shade each row when its status in column D is “Overdue.” With that range, the formula is =$D2="Overdue". The dollar sign fixes the column at D, while the row number remains relative. As Sheets evaluates the rule on the next row, it checks D3, then D4, and so on, while applying the style across columns A through D.

In contrast, a reference such as =$D$2="Overdue" fixes both the column and row. That makes every row depend on the single cell D2. Absolute references are helpful for a shared setting or threshold, but can be a mistake when each row should use its own status. Relative references shift as the rule is evaluated; dollar signs control which parts stay fixed.

Write the formula as though it describes the first cell or row in the range, not the last one. If the range starts at row 2 but the formula refers to row 1, the condition can be offset and highlight the wrong records. When you change the Apply to range, revisit the formula’s row reference as well.

Useful examples for common tasks

Custom formulas are most useful when a rule must connect cells or combine conditions. These examples show the assumed layout and what a matching example record does.

Highlight a row when an amount meets a threshold. Assume amounts are in B2:B100, the threshold is in F1, and the formatting range is A2:D100. Use =$B2>=$F$1. If B2 is 120 and F1 is 100, the condition is TRUE and the first row is formatted. The dollar signs keep the amount column and threshold cell fixed while the row advances.
Mark repeated IDs without coloring empty cells. Assume IDs are in A2:A and the Apply to range is A2:A. Use =AND($A2<>"",COUNTIF($A$2:$A,$A2)>1). If K-14 appears in A2 and A8, both occurrences meet the condition; an empty cell does not. COUNTIF counts matches in the ID range, and AND requires the current cell to be nonblank as well.
Flag a due date that has passed while the task is still open. Assume due dates are in C2:C100, statuses are in D2:D100, and the Apply to range is A2:D100. Use =AND($C2<>"",$C2<TODAY(),$D2<>"Complete"). A row with a past due date and a status other than Complete is formatted; a blank due date is ignored. TODAY updates with the current date, so the result can change from day to day.

These examples use formulas compatible with Google Sheets and familiar spreadsheet functions. Check the actual range and column meanings in your own file before applying one. A correct formula with the wrong starting row or column can still produce misleading highlights.

Choosing between custom formulas and built-in rules

Use the simplest rule that expresses the condition. The built-in options in the Format cells if menu are often easier for straightforward cases, such as cells that are blank, text that contains a phrase, or numbers above a fixed value. Google Sheets also offers color scales for showing a gradient across numeric values. A custom formula is a better fit when the condition depends on another column, multiple criteria, or a more specific logical test.

Custom formulas are not automatically better. For a basic “greater than 100” test, a built-in greater-than rule is generally easier for another editor to understand and maintain. For “greater than the adjustable threshold in F1,” a formula can express the link to that cell. If a sheet has many overlapping or complex rules, simplify them where possible and use clear labels or notes so collaborators can understand what the colors mean.

Rule order and overlapping formats

A cell or row can meet more than one rule. Google Sheets evaluates conditional-formatting rules in the order shown in the right-side panel; when multiple rules apply, the first applicable rule takes precedence for formatting. If the displayed style is not the one you expected, inspect the rules for that range and drag them into the intended order.

Overlapping ranges can also make a result confusing. For instance, one rule might color a whole row red while another colors a status cell green. Review both the range and the style when troubleshooting, rather than changing the formula immediately. It can help to temporarily give a rule an unmistakable color, confirm which cells it affects, and then select the final style.

Common problems and how to fix them

The wrong row is highlighted: Confirm that the formula’s row number matches the top row of Apply to range. For a range beginning at row 2, the row-specific references normally begin with 2.
Every row gets the same result: Check whether a row reference has been locked with a dollar sign. A reference such as $D$2 always checks D2; $D2 keeps the column fixed but allows the row to change.
No cells change format: Test the formula’s logic against a sample row. Check spelling, quotation marks around text, comparison operators, and whether the formula returns TRUE for the case you expect. Also verify that the cell is inside Apply to range.
Blank cells are highlighted unexpectedly: Add a nonblank test, such as $A2<>"", to the condition when empty cells should be excluded. This is especially important in duplicate checks, where repeated blank values can otherwise satisfy a count test.
A rule behaves differently after copying data: Review both absolute and relative references. Copying or expanding a range can change which cells a formula refers to, while a fixed reference may continue pointing to a setting cell by design.

Formula separators and function names can depend on spreadsheet locale settings. If a formula is rejected, check the locale and the exact cell references, and try entering the same logical test in a normal cell first. In a regular cell, a TRUE or FALSE result makes it easier to distinguish a formula error from a conditional-formatting range or style issue.

Google Sheets compared with Excel

The general idea exists in both Google Sheets and Microsoft Excel: a formula can determine whether conditional formatting applies. The instructions here are for Google Sheets in a web browser, where the path is Format > Conditional formatting and the rule type is Custom formula is. Excel uses its own conditional-formatting controls and menus, so the exact interface and rule-management behavior are not identical.

Google Sheets formulas are often compatible with Excel, but do not assume every spreadsheet feature or formula behaves identically after converting a file. If a workbook moves between the programs, verify custom rules, ranges, formula references, and displayed results in the destination application. This is particularly worthwhile for sheets with several rules, references to other tabs, or locale-specific formula settings.

Frequently Asked Questions

Can I use a custom formula to highlight an entire row in Google Sheets?
Yes. Apply the rule to the full row range, then use a formula that fixes the condition column but leaves the row relative. For example, for A2:D100 and a status in column D, =$D2="Overdue" tests each row’s status and can format all four cells in that row.

Does a custom conditional-formatting formula change the cell value?
No. It changes the cell’s appearance when the condition is true. To calculate or replace a value, use a normal sheet formula or edit the cell; conditional formatting is not a data-editing feature.

Can a rule check a different sheet?
Google Sheets has restrictions on direct references to other sheets in conditional-formatting custom formulas. Google’s guidance is to use INDIRECT when referencing another sheet. Because INDIRECT references are easier to break if sheet names or ranges change, keep the reference clear and verify the rule after renaming or reorganizing tabs.

Can I use more than one condition?
Yes. Functions such as AND and OR can combine tests. For example, AND requires all included tests to be true, while OR requires at least one. Make sure the combined formula still returns a logical TRUE or FALSE for the cells covered by the rule.

Why does the rule work in one cell but not across the range?
The formula may be using the wrong relative reference, or the Apply to range may start on a different row than the formula assumes. Check the top-left cell of the range, adjust the references, and test a few rows with different values before relying on the formatting.

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.