Formatting Issues

How to Use Conditional Formatting to Match Values Across Excel Worksheets

Huda Qurayshi Oct 13, 2026 870 views

Question details

Highlight names in red on two different worksheets if their corresponding numbers match, even when the names appear in different orders on each sheet.

How to Apply Conditional Formatting for Matching Values Across Worksheets

Product: Excel
Device & OS: not provided
Scenario: Comparing unstructured data across two worksheets to visually flag matching numerical values associated with specific names.
Observed behavior: Standard conditional formatting rules often return errors when directly referencing cells from another worksheet, requiring an advanced formula approach or named ranges to function properly.

Before you start

Ensure both worksheets are within the same workbook and verify that the names used for matching are spelled exactly the same on both sheets to prevent formula errors.

Solution 1Recommended

Use INDEX and MATCH for Cross-Sheet Conditional Formatting

Use a combination of COUNTIF, INDEX, and MATCH formulas to bypass Excel's cross-sheet reference limitations in conditional formatting.

Because Excel restricts direct cross-sheet cell references in conditional formatting rules, utilizing an INDEX and MATCH formula acts as an effective workaround. This ensures Excel dynamically searches the target worksheet and applies the desired formatting without triggering a reference error.

Select the target name range

Navigate to your second worksheet and select the range of names you want to format (for example, A5:A27). Make sure the top cell (A5) is the active cell in your selection.

Open the Conditional Formatting menu

Go to the 'Home' tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule' from the drop-down menu.

Enter the cross-sheet formula

Choose 'Use a formula to determine which cells to format'. Enter a formula tailored to your layout, such as =COUNTIF(INDEX('Tab 1'!$B$6:$S$28, MATCH(A5, 'Tab 1'!$A$6:$A$28, 0), 0), $B$49). Adjust the sheet names ('Tab 1') and cell references according to your actual workbook structure.

Apply the red formatting

Click the 'Format' button, navigate to the 'Font' or 'Fill' tab, select the color red, and click 'OK' to confirm and apply the formatting rule.

Use INDEX and MATCH for Cross-Sheet Conditional Formatting

Relative vs Absolute References: Ensure you lock the lookup ranges with absolute references (like $A$6:$A$28) but keep the row number relative for your active cell (like A5) so the rule adjusts for each name in your list.

Solution 2

Use a Helper Column and Basic Conditional Formatting

If complex cross-sheet formulas cause errors, use a helper column to pull data from the first sheet into the second, then apply a much simpler conditional formatting rule.

Effortless Data Analysis

Highlight and Match Data Across Sheets Easily with WPS Spreadsheet

WPS Office provides robust support for advanced conditional formatting and complex array formulas like INDEX and MATCH, allowing you to highlight matching cross-sheet data without hassle.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
2. Highlight target names: Select the range of names you want to apply the formatting to on your secondary sheet.
3. Access conditional formatting: Navigate to Home > Conditional Formatting > New Rule on the top ribbon.
4. Input your matching formula: Select 'Use a formula to determine which cells to format' and paste your INDEX/MATCH or COUNTIF cross-sheet formula.
5. Set color and apply: Click 'Format' to define your red font or fill color, then click 'OK' to instantly view the matched highlights.

Fully compatible with Microsoft Excel formulas and conditional formatting rulesAdvanced data analysis tools available completely for freeLightweight application that runs smoothly even with large datasetsFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel give an error when I click another sheet during conditional formatting?

Traditional versions of Excel restrict direct cross-sheet references in conditional formatting rules to prevent circular referencing and processing delays. You usually need to use named ranges or specific lookup formulas like INDEX and MATCH to bypass this limitation.

How do I define a named range for cross-sheet conditional formatting?

Select the desired data range on your first worksheet, click inside the Name Box located to the left of the formula bar, type a single-word name (e.g., 'Sheet1Data'), and press Enter. You can then use this defined name directly in your conditional formatting formula on the second sheet.

Can I highlight the entire row instead of just the name cell?

Yes. To highlight the entire row, you must select the entire data table range before creating the conditional formatting rule. Furthermore, ensure you lock the column reference in your formula by adding a dollar sign (e.g., $A5 instead of A5) so the formatting applies across the entire row based strictly on the value in the name column.

Document & File Issues