Yes, Excel can use values from another sheet to control conditional formatting, but a rule usually cannot refer directly to another worksheet. In Microsoft 365 for Windows, use a workbook-level defined name for a fixed value, or use INDIRECT for row-by-row comparisons; then create the rule through Home > Conditional Formatting > New Rule.
Why compare values across worksheets?
Keeping source data and review rules on separate sheets is often useful. A data sheet might list sales, inventory, deadlines, or test results, while another sheet holds targets or approved limits. Conditional formatting can make exceptions visible in the data itself: a below-target amount turns red, an overdue date gets highlighted, or a value that differs from a reference list stands out.
A conditional-formatting rule does not move or change the underlying value. It evaluates a formula for each cell covered by the rule. When the formula returns TRUE, Excel applies the chosen appearance, such as a fill color, font style, or border. When it returns FALSE, that rule does not apply. The result is a visual signal that can update as the source values change.
The cross-sheet reference limitation
In a normal worksheet formula, a reference such as =Targets!B2 can retrieve a value from another sheet. Conditional-formatting formula rules have a restriction: Excel generally rejects a direct reference to a different worksheet in the rule formula. This catches many people because the same reference works in an ordinary cell.
There are two common workarounds. A defined name can point to a cell on another sheet, allowing the rule to use the name instead of a direct sheet reference. Alternatively, the INDIRECT function can turn text into a reference, which is useful when the rule needs to compare corresponding rows across sheets. A helper column on the data sheet is another practical option, especially when you want a rule that is easy to inspect and maintain.
Use a defined name for one fixed value
For a single threshold that applies to many cells, a workbook-level defined name is usually the clearest option. Suppose the values to format are in Data!A2:A100 and the threshold is in Targets!B2. The goal is to highlight a value in column A when it exceeds that threshold.
- On the Formulas tab, choose Define Name. Give the name a clear label such as
TargetLimit, set Scope to Workbook, and set Refers to to=Targets!$B$2. If the sheet name contains spaces, Excel may display it with single quotes, as in='Target Values'!$B$2. - Return to the Data sheet and select A2:A100. Make sure A2 is the active cell in the selection.
- Choose Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format.
- With values in Data!A2:A100 and the threshold in Targets!B2, enter the rule formula
=A2>TargetLimit. Choose Format to select the appearance, then confirm the rule.
The reference to A2 is relative, so Excel evaluates A3 against the same named threshold for the next row, then A4, and so on. If A2 contains 125 and Targets!B2 contains 100, the formula evaluates TRUE and the cell receives the selected format. If the threshold changes to 130, the rule recalculates and A2 no longer meets it.
For the opposite test, such as flagging values below a minimum, change the comparison operator. The name itself can also point to a different cell if the threshold is moved; the rule does not need to change as long as the defined name still refers to the intended value.
Compare corresponding rows with INDIRECT
When each data row has its own reference value on another sheet, a fixed name is not enough. For example, Data!A2:A100 might contain actual figures and Targets!B2:B100 the corresponding limits. The following rule compares each actual value with the limit on the same row.
With actual values in Data!A2:A100 and row-matched limits in Targets!B2:B100, select Data!A2:A100 and use this conditional-formatting formula:
=A2>INDIRECT("'Targets'!B"&ROW())
For the first selected cell, ROW() returns 2, so INDIRECT evaluates the text reference to Targets!B2. In the next row it evaluates Targets!B3. If Data!A2 is 125 and Targets!B2 is 100, A2 is highlighted; if the next data value is 90 and its limit is 100, that row is not highlighted.
The rule depends on the row numbers lining up. If the selected range begins at row 5 but the matching target data begins at row 2, adjust the row calculation rather than assuming the same row number on both sheets. Also note that the sheet name is written inside a text string. Excel does not reliably revise text inside INDIRECT when a sheet is renamed, so update the formula if you change the tab name.
Use a helper column for clarity
A helper column avoids putting a cross-sheet lookup inside the formatting rule. On the Data sheet, place each corresponding target beside its actual value. With actual values in Data!A2:A100 and targets in Targets!B2:B100, enter this regular worksheet formula in Data!C2 and fill it down:
=Targets!B2
Then select A2:A100 and create a formula-based conditional-formatting rule. With actual values in A2:A100 and the copied target values in C2:C100, use =A2>C2. Because both references are on the same sheet, the rule is straightforward to review and adjust. The helper cells can be hidden if displaying them would clutter the report, although leaving them visible can make it easier to audit the comparison.
This method adds a column and copies formulas, but it is often preferable in shared workbooks. A colleague can inspect the target beside the value instead of having to decode a text-based reference in a formatting rule. It can also make troubleshooting easier because you can see whether the lookup itself is returning the expected value.
Get the Applies To range and references right
Conditional-formatting formulas are interpreted relative to the upper-left cell in the rule’s Applies To range. If the range is A2:A100, the formula should normally refer to A2 for the first row. Using $A$2 would lock the rule to that one cell’s value, so every row could be formatted based on A2 instead of its own value.
Use dollar signs deliberately. In A2, both the column and row can adjust as Excel evaluates the rule. In $A2, the column stays fixed but the row can change. In A$2, the row stays fixed but the column can change. In $A$2, neither changes. For a vertical range where every row compares its own column A value to a fixed named threshold, A2 is the important relative reference.
To review an existing rule, choose Home > Conditional Formatting > Manage Rules. Check the rule formula and its Applies To range. A correctly written formula can still appear to behave incorrectly if it applies to the wrong cells, starts on a different row than expected, or overlaps with another rule that takes precedence.
Rule order, performance, and compatibility
Excel can apply multiple conditional-formatting rules to the same cell. In Manage Rules, rule order matters when rules set conflicting formats. The Stop If True option can prevent lower-priority rules from being evaluated for a cell once a higher rule is true. If a color does not appear as expected, inspect all rules affecting that range rather than assuming the newest rule is the only one in effect.
INDIRECT is a volatile function, meaning Excel may recalculate it more often than a nonvolatile reference. A handful of rules on a modest range is typically manageable, but thousands of cells with several volatile rules can add calculation work. For larger or frequently used workbooks, a helper column or a carefully designed defined-name approach may be easier to maintain.
These formula principles also apply in Excel for Mac, although dialog layout and some ribbon details can differ. Excel for the web supports many conditional-formatting tasks, but its available rule-management controls can differ from the desktop app. If you cannot create or edit the specific formula rule you need in a browser, open the workbook in desktop Excel. Avoid assuming that a workbook will behave identically in every spreadsheet program; formulas and rule support can differ outside Excel.
Common problems and practical fixes
- Excel rejects the formula: Check for a direct worksheet reference in the conditional-formatting formula. Use a defined name,
INDIRECT, or a same-sheet helper column instead. - The wrong rows are highlighted: Confirm that the formula’s relative row matches the first row of Applies To. For A2:A100, begin with a reference to row 2.
- Every row uses one comparison value: Check whether the data reference was accidentally locked, such as
$A$2, or whether the target is intentionally fixed. For row-by-row comparisons, use a relative row reference and confirm the target row calculation. - Nothing is highlighted: Check that the comparison is true for a sample row, that the target cell contains a number rather than text, and that the selected format is visually noticeable. Also inspect the Applies To range.
- The rule breaks after a tab is renamed: If the formula uses
INDIRECT, revise the sheet name inside its text string. A defined name is often less fragile for a fixed target cell. - A different color appears: Open Manage Rules and check for overlapping rules, their order, and whether Stop If True is enabled.
Which method should you choose?
Use a workbook-level defined name when one cell on another sheet supplies a shared threshold or setting. It keeps the rule readable and avoids a text-built reference. Use INDIRECT when corresponding values on separate sheets need to be compared row by row and the row alignment is stable. Choose a helper column when transparency, review, and ease of maintenance matter more than keeping the sheet compact.
For a one-off rule, INDIRECT can be convenient. For a workbook that other people will maintain, a named threshold or a visible helper calculation is often easier to explain. Whichever option you select, test the rule with one value that should pass and one that should not, then confirm the Applies To range before relying on the formatting in a report.
Frequently Asked Questions
Can Excel conditional formatting directly reference another sheet?
Usually not in a conditional-formatting formula rule. Use a defined name that refers to the other sheet, an INDIRECT reference, or a helper cell on the sheet being formatted.
Can I compare each row with the same row on another sheet?
Yes. A formula using INDIRECT and ROW() can build a reference to the matching row, provided the rows align. A helper column is an alternative that is easier to inspect.
Can I use a named range in a conditional-formatting formula?
Yes. A workbook-level name can refer to a cell or range on another worksheet, and the rule can use that name without placing a direct cross-sheet reference in the rule formula.
Why does my rule highlight the wrong cells?
The most common causes are a mismatch between the formula’s starting row and the Applies To range, an accidentally absolute reference, or another overlapping rule that affects the same cells.
Does conditional formatting change the cell value?
No. It changes only the cell’s appearance when a rule evaluates TRUE. The underlying value and any ordinary worksheet formulas remain unchanged.
Related
- Conditional Formatting in Excel: Questions Answered
- Conditional Formatting Rows in Excel: A Practical Guide
- Conditional Formatting in Excel: Yes, and Here’s Why
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Cell Service in Jurong East: What to CheckIf you mean a business called “Excel Cell Service” in Jurong East, I can’t reliably confirm that such…
- Add a Shared Mailbox in Old Outlook: Classic Outlook StepsYes. In classic Outlook for Windows, go to File > Account Settings > Account Settings, select your work…
- Excel WEEKDAY: Get the Day of the Week from a DateIn Excel, use WEEKDAY to return a number for the day of the week represented by a date.…
- Excel Max Cell Height: The 409-Point Row Height LimitIn Excel for Microsoft 365 on Windows, the maximum row height is 409 points, or about 5.68 inches…
- Excel Date Selector: Add a Calendar or Validate DatesExcel for Microsoft 365 on Windows does not include a built-in calendar date selector for ordinary worksheet cells.…
- Apps Script in Google Sheets: Where to Find ItIn Google Sheets on a computer, open your spreadsheet and choose Extensions > Apps Script. The Apps Script…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.