Conditional Formatting Across Sheets in Excel: How It Works

Yes, Excel can use conditional formatting to compare cells on one worksheet with values on another, but a rule generally cannot use a direct worksheet reference in its formula. In Microsoft Excel for Microsoft 365 on Windows, the reliable approach is to create a workbook-level named range for the source cells, then refer to that name in the conditional-formatting rule; an INDIRECT formula or a helper column can also work.

What “across sheets” can mean

The phrase can describe two different tasks. In one, you want cells on a sheet to change appearance based on information stored on another sheet—for example, highlight orders whose customer appears on a separate list. In the other, you want the same conditional-formatting rule on several worksheets. Both are possible, but the setup differs.

For a rule that checks another sheet, Excel’s conditional-formatting formula box does not normally accept an ordinary direct reference such as =Sheet2!B2. A formula that reads cells on the current worksheet is straightforward; crossing worksheet boundaries is where the restriction matters. Named ranges and INDIRECT provide ways around it.

For the same rule on several sheets, create or copy a rule on each worksheet and inspect its range and references. A rule belongs to a worksheet, so do not assume that a rule created on one sheet automatically governs matching cells elsewhere in the workbook.

Use a named range to check another sheet

Suppose an Orders sheet has customer names in A2:A100, and a Lists sheet has approved names in B2:B50. You want to highlight an order’s customer if that name appears in the approved list. A workbook-level name makes the source range available to the conditional-formatting formula without a direct sheet reference.

On the Formulas tab, select Name Manager, then New.
Enter a clear name such as ApprovedCustomers. Set Scope to Workbook so the name is available from the Orders sheet.
With the source list in Lists!B2:B50, enter =Lists!$B$2:$B$50 in Refers to, then select OK. If the sheet name contains spaces, Excel will include apostrophes around it.
On Orders, select A2:A100. Choose Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format.
With customer names in Orders!A2:A100 and the named range ApprovedCustomers referring to Lists!B2:B50, enter =COUNTIF(ApprovedCustomers,A2)>0. Choose Format, select the desired appearance, and confirm the rule.

The formula’s A2 reference is relative, so Excel checks each selected row against the list. With “Mira Patel” in A2 and that name present in the approved list, COUNTIF returns 1 and the rule evaluates to TRUE; a name absent from the list returns 0 and is not highlighted. COUNTIF does not distinguish uppercase from lowercase, so “MIRA PATEL” will match “Mira Patel.”

Understand the formula and its references

Conditional-formatting formulas are evaluated relative to the top-left cell in the range listed under Applies to. That is why the example uses A2 when the selected range begins at A2. If the selected range instead began at A5, the formula would need to refer to A5. Excel adjusts relative references as it evaluates the rule across the other cells.

Dollar signs control what moves. In a rule applied to A2:A100, A2 should usually remain relative so each row checks its own customer. If the rule compares each row to a fixed value in another column, you may need to lock the column or row with dollar signs. For instance, $A2 keeps the column fixed while allowing the row to change; A$2 keeps the row fixed while allowing the column to change.

COUNTIF is useful for membership checks because it counts matches in a range and returns a number. The comparison with zero turns that count into a TRUE or FALSE result for conditional formatting. If duplicates in the source list are possible, they do not change the outcome: one or more matches all satisfy the “greater than zero” test.

Use INDIRECT when a named range is not practical

Another option is INDIRECT, which turns a text reference into a range reference. It can be useful for a small, fixed rule when you would rather not create a name, but it is less transparent to someone maintaining the workbook. It is also volatile: Excel may recalculate it more often than a nonvolatile reference, which can matter in workbooks with many such formulas.

With names in Orders!A2:A100 and the comparison list in Lists!B2:B50, select A2:A100 and use this conditional-formatting formula: =COUNTIF(INDIRECT("'Lists'!$B$2:$B$50"),A2)>0. It highlights the same matches as the named-range example. The apostrophes inside the text reference help Excel read the sheet name; for a sheet name containing an apostrophe, the reference string needs the apostrophe escaped as two apostrophes.

INDIRECT relies on a text address, so renaming a sheet may not update that text the way a normal worksheet reference updates. It can also be harder to audit than a named range. For a workbook other people will maintain, a workbook-level name is often easier to understand; for large or complex workbooks, a helper column may be clearer still.

Use a helper column for clarity and troubleshooting

A helper column can move the cross-sheet comparison out of the conditional-formatting rule. For example, the Orders sheet could show “Found” or “Not found” in column C, and a simple rule on column A could highlight rows where column C says “Found.” This makes the comparison visible, which can help users verify why a row received its formatting.

With customer names in Orders!A2:A100 and the approved names in Lists!B2:B50, enter this in Orders!C2 and fill it down: =COUNTIF(Lists!$B$2:$B$50,A2)>0. This is an ordinary worksheet formula, where direct cross-sheet references are allowed. It returns TRUE for a match and FALSE otherwise. Then select A2:A100, create a formula-based conditional-formatting rule with =$C2=TRUE, and choose a format. In this rule the column is fixed, while the row changes for each order.

The helper-column method can be easier to inspect, but it uses extra cells and may expose intermediate results users do not need to see. You can hide the helper column, though hiding it makes the logic less visible. Consider adding a header such as “On approved list?” and documenting the purpose if the workbook is shared.

Apply a rule on multiple worksheets

If the goal is identical formatting on several sheets, make the rule on each worksheet rather than assuming one rule covers the workbook. A practical process is to configure the rule on one sheet, then copy its relevant cells to the matching range on another sheet and use Paste Special > Formats. Because copied conditional formatting can bring along ranges and relative references, check the result before relying on it.

To review the rule on a particular sheet, choose Home > Conditional Formatting > Manage Rules. Set Show formatting rules for to This Worksheet to see that sheet’s rules. Inspect both the formula and Applies to range. A correct formula can appear to do nothing if Applies to points to the wrong cells; conversely, a correct range can show unexpected results if the formula’s references shifted during copying.

When copying between sheets, check whether each sheet should use its own local data or continue referring to one central list. For a shared list, a workbook-level named range is helpful because the same name can be used by rules on different sheets. For a separate list on each sheet, create appropriately scoped names or adjust each rule to the intended source. Avoid copying a rule blindly across worksheets with different layouts.

Choose the approach that fits the workbook

Named range: A good default for a reusable cross-sheet list comparison. It avoids putting a direct sheet reference in the conditional-formatting formula and gives the source a readable name.
INDIRECT: Handy for a quick, fixed reference, but the text-based address is easier to break when sheets or ranges change, and the function is volatile.
Helper column: Best when people need to see or audit the comparison result, or when troubleshooting matters more than keeping the worksheet compact.
Separate rules per sheet: Appropriate when the same visual logic needs to be repeated on multiple worksheets, especially if each one has its own data range.

For a short list that rarely changes, a named range can be a simple solution. If the list grows regularly, consider whether the source should be an Excel table and whether the name or range needs to expand with it. Test the rule after adding rows: a fixed reference such as B2:B50 will not include new entries below row 50 unless you update the range or define it to cover the intended space.

Common problems and practical checks

The rule is rejected: Check whether the formula contains a direct reference to another worksheet. Replace it with a named range or use a helper column.
The wrong rows are highlighted: Confirm the formula reference matches the top-left cell of Applies to. A formula starting at A1 while the range starts at A2 can create an offset result.
Nothing matches: Check for extra spaces, inconsistent spelling, or a name that falls outside the source range. COUNTIF ignores letter case but not differences in the text itself.
New list entries are missed: Expand the named range or other source range. A rule cannot find items that are outside the range it checks.
Unexpected formatting wins: Open Manage Rules and review rule order and the Stop If True setting. Another rule may apply a conflicting format or prevent later rules from being evaluated.
Copied rules behave differently: Review Applies to and the cell references on the destination sheet; copied rules may adjust relative references.

It is useful to test a known match and a known nonmatch before sharing the workbook. If names come from external imports, examine whether spaces or inconsistent text are involved. A visible helper formula can help diagnose the comparison, even if you remove or hide that helper after the rule is working.

Excel for the web and Mac differences

Excel for the web includes conditional-formatting controls, but the exact options available for creating or managing rules can differ from the Windows desktop application. If you cannot create or edit the needed formula-based rule in the web interface, open the workbook in Excel for Microsoft 365 on Windows and use Home > Conditional Formatting > New Rule. The named-range method is based on Excel formulas, not a Google Sheets feature.

On Excel for Mac, conditional formatting is also available, but the ribbon layout and dialog wording can vary by release. Look under Home > Conditional Formatting for the rule commands. The Windows ribbon path and steps above are the reference instructions; the formula logic itself is the same. Menu navigation does not require a keyboard shortcut, and shortcuts for opening dialogs can differ between Windows and Mac.

Frequently Asked Questions

Can conditional formatting directly reference another sheet?

Usually not with an ordinary direct worksheet reference in the conditional-formatting formula. Use a workbook-level named range, INDIRECT, or a helper-cell formula that performs the cross-sheet comparison on the worksheet.

Can one conditional-formatting rule apply to every sheet?

A conditional-formatting rule is associated with a worksheet. Create or copy the rule on each sheet that needs it, then verify the formula and Applies to range on each worksheet in Manage Rules.

Does COUNTIF treat uppercase and lowercase as different?

No. COUNTIF is not case-sensitive for this kind of text comparison, so entries that differ only in capitalization match. Differences such as extra spaces or different spelling can still prevent a match.

Why does a rule highlight the row above or below the match?

The formula’s relative reference may not correspond to the top-left cell in the Applies to range. If the range begins at A2, the formula should generally refer to A2, not A1 or A3. Check the range and formula together.

Will the rule include new entries added to the other sheet?

Only if the source range includes them. A fixed reference ending at row 50 will not cover entries added in row 51. Expand the named range or use a range design that includes the expected additions, then test the rule again.

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.