Google Sheets INDIRECT Conditional Formatting Explained

How-to basics · Updated November 20, 2026

Yes. Google Sheets conditional formatting can use INDIRECT in a custom formula to turn text into a cell reference, which is useful when the rule needs to point to another sheet or to a reference that changes dynamically. Select the cells to format, choose Format > Conditional formatting, then add a Custom formula is rule.

What INDIRECT conditional formatting means

Conditional formatting applies a visual style when a condition is true. Most rules can refer directly to cells in the same sheet, but a rule sometimes needs a reference assembled from text—for example, a sheet name chosen in a dropdown. The Google Sheets INDIRECT function interprets a text string as a cell or range reference. Used in a custom conditional-formatting formula, it lets the rule evaluate the value at that constructed reference.

This does not make INDIRECT a special formatting feature. It is an ordinary Sheets function being evaluated by a conditional-formatting rule. The rule still needs to return a condition that Sheets can evaluate as true or false, such as whether a referenced cell equals a status or exceeds a number.

For a fixed cell on the same sheet, a direct reference is usually simpler and easier to maintain. INDIRECT is most useful when the address itself must vary, or when you need to refer to a different sheet from a conditional-formatting rule.

How to add the custom rule

  1. Select the cells that should receive the formatting. For example, select A2:F100 to apply a rule to each row of a table.
  2. Choose Format > Conditional formatting. The Conditional format rules panel opens.
  3. Under Format rules, select Custom formula is.
  4. Enter the formula, choose the formatting style, and select Done.

The formula is evaluated for each cell in the selected range. Its relative and absolute references determine which row or column Sheets checks as the rule is applied. If the range starts at row 2, a formula that refers to row 2 can adjust to row 3, row 4, and so on for subsequent rows. Dollar signs lock the parts of a reference that should not move.

To edit a rule later, select a cell in its range and return to Format > Conditional formatting. You can change the formula, formatting style, or apply-to range in the panel. When multiple rules affect the same cells, review their order and formats if the result does not look as expected.

Example: format rows using a value on another sheet

Suppose a sheet named Status Log has task statuses in column C, starting at C2. On the current sheet, task rows occupy A2:F100, with the rows matching the Status Log rows. To highlight a row when its corresponding status is Overdue, apply the rule to A2:F100 and enter this custom formula:

With statuses in Status Log!C2:C100 and the formatted range starting at row 2, use =INDIRECT("'Status Log'!$C2")="Overdue".

The text inside INDIRECT points to column C on Status Log. The dollar sign fixes the column at C, while the row number remains relative. As the rule moves down the selected range, it checks C2, then C3, then C4. The comparison is true only for a row whose matching status is exactly Overdue. The quotes around the sheet name are important for sheet names containing spaces; using them consistently also works for simple names.

For a dynamic version, imagine B1 contains a sheet name selected from a dropdown, and each possible sheet has the relevant status in column C. Apply the rule to A2:F100. With the sheet name in B1 and the matching statuses in column C from row 2 down, use =INDIRECT("'"&$B$1&"'!$C2")="Overdue".

Here, the formula joins a quoted sheet name, an exclamation mark, and the row-specific cell reference. The dollar signs keep the dropdown reference fixed at B1 and the status column fixed at C. Changing B1 makes the rule read from another sheet without editing the rule itself. If B1 contains a sheet name that does not exist, the reference is invalid and the rule may show an error or fail to apply as intended.

References, quotes, and row behavior

INDIRECT takes text that looks like a reference and interprets it. In a conditional-formatting formula, that text can be fixed, joined from several pieces, or built using a value in another cell. For a cross-sheet reference, the general pattern is a quoted sheet name, an exclamation mark, and a cell address—for example, 'Status Log'!$C2. Because INDIRECT receives that reference as text, quotation marks inside the formula must be handled as part of the text string.

Think carefully about the top-left cell of the apply-to range. If the range starts at A2, a row-relative reference ending in 2 is generally designed to evaluate the first row using row 2. If the range instead starts at A5, a formula that still refers to row 2 will not automatically mean row 5; adjust the formula to match the intended alignment. A locked row such as $C$2 always refers to C2, while $C2 keeps the column fixed and lets the row change.

INDIRECT can also be given an A1-style reference or, with its optional second argument, an R1C1-style reference. Most formatting rules use A1 notation because it is familiar and easy to inspect. Avoid building a complicated address string when a normal cell reference would do; every extra concatenation makes a rule harder to understand and troubleshoot.

Useful patterns and when to use them

INDIRECT is not limited to checking text statuses. The reference can point to a number, date, or other value, and the custom formula can compare that value to a threshold. For example, a rule could highlight rows whose corresponding value on a chosen sheet is greater than a limit. The important pieces are a correctly built reference and a comparison that yields a true-or-false result.

  • Choose a source sheet: Store a valid sheet name in a dropdown cell and combine it with a fixed column and changing row, as in the example above.
  • Choose a source column: Store a column letter in a cell and join it to a row number when the rule needs to inspect different columns based on a selection.
  • Use a named range: If a text value corresponds to an existing named range, INDIRECT can resolve that name. Check spelling and scope if the reference does not resolve.
  • Evaluate an entire range: A custom formula may use a reference to a range, but the rule must still be designed to return a condition suitable for each formatted cell. Test the rule on a small range before applying it broadly.

These approaches are helpful for reusable templates, monthly tabs, and dashboards that let someone choose which sheet to inspect. They are less helpful when the workbook structure is stable. In that case, an ordinary reference—or a helper column that calculates the condition—usually makes the logic more transparent.

Common errors and how to fix them

A rule that does nothing often has a reference or alignment problem rather than a formatting-style problem. Check the formula in the context of the first cell in the apply-to range, and verify the reference it constructs. Common causes include a misspelled sheet name, a missing exclamation mark, a mismatched quote, or a row number that does not line up with the first formatted row.

  • Check the sheet name: The text assembled by INDIRECT must match an existing tab name, including spaces and punctuation. A dropdown can reduce typing errors.
  • Check the apply-to range: Make sure the range begins on the row assumed by the formula and covers all intended cells.
  • Check the result type: A comparison such as equals Overdue gives a clear true-or-false result. A bare reference may return a value that does not express the condition you intended.
  • Check quotation marks: Quotes are needed around text values and are also part of a quoted sheet reference. Inspect the completed reference string for missing or misplaced marks.
  • Check errors in source cells: An error in the referenced value can stop the condition from behaving as expected. Fix the source data or account for invalid selections.

For troubleshooting, temporarily enter the reference-building logic in an unused cell and see whether it returns the expected value. This can reveal a malformed sheet name or address. Remove the temporary test when finished, and keep the final conditional-formatting formula focused on the condition rather than adding unnecessary calculations.

INDIRECT versus direct references and helper columns

A direct reference is preferable when the source cell is known. For example, if a table on the same sheet should be highlighted when column B says Overdue, a custom formula such as =$B2="Overdue" is easier to read than constructing a reference with INDIRECT. With the rule applied to A2:F100, the fixed column and relative row let each row test its own status.

Cross-sheet conditional formatting is a special case: Google Sheets may require INDIRECT to refer to another sheet from a custom formula. If the logic becomes difficult to audit, calculate the relevant status in a helper column on the formatted sheet, then base the formatting rule on that helper value. This separates data retrieval from visual styling and makes errors easier to locate.

INDIRECT is also volatile, meaning it can recalculate more often than many ordinary references. A handful of rules is generally a practical use, but a large workbook with many INDIRECT formulas and broad apply-to ranges can become harder to maintain and may recalculate more work than necessary. Keep ranges as small as the task permits and avoid using INDIRECT merely to make a fixed reference look flexible.

Frequently Asked Questions

Can Google Sheets conditional formatting reference another sheet?
Yes. For a custom formula, INDIRECT can construct a cross-sheet reference from text. Include the exact sheet name and cell address, then compare the returned value with the condition you want.

Why is my INDIRECT conditional-formatting formula not working?
Check the sheet name, quotes, exclamation mark, and cell address. Also confirm that the first row in the formula matches the top row of the apply-to range and that the formula returns the condition you expect.

Do I need INDIRECT for a cell on the same sheet?
No. A normal relative or absolute reference is usually clearer for a same-sheet condition. Use INDIRECT when the address needs to be assembled dynamically or when you need to refer to another sheet in the rule.

Can a dropdown choose which sheet the rule checks?
Yes. Put a valid sheet name in a dropdown cell and concatenate that cell into the reference text passed to INDIRECT. Ensure every selectable name corresponds to an existing tab with the expected layout.

Does INDIRECT work with sheet names that contain spaces?
Yes. Put the sheet name in single quotes within the constructed reference text, as in the Status Log example. The reference still needs the exclamation mark before the cell address.

Is INDIRECT available in Excel too?
Excel has an INDIRECT function, but conditional-formatting rules and cross-sheet reference constraints differ between Excel and Google Sheets. The menu steps and examples here are specifically for Google Sheets in a web browser, so do not assume the same rule setup will transfer unchanged.

Related

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.