Formatting Issues

How to Apply Conditional Formatting Across Multiple Excel Worksheets

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to highlight cells across two different worksheets when specific data values appear on both sheets simultaneously.

Product: Excel
Device & OS: not provided
Scenario: Comparing data lists across two separate worksheets and highlighting the duplicate entries for quick identification.
Observed behavior: Cells containing duplicate values between the two specified worksheets are automatically highlighted with a chosen color.

Before you start

Ensure that your data columns are correctly aligned on both worksheets and take note of the exact worksheet names, as you will need them to build the cross-sheet formula.

Solution 1Recommended

Use the COUNTIF Function for Cross-Sheet Conditional Formatting

You can set up custom conditional formatting rules on each worksheet using the COUNTIF function to check for matching values on the opposing sheet.

Standard duplicate highlighting options in Excel do not natively work across different worksheets. To solve this, you must use a custom formula based on the COUNTIF function to determine which cells should be formatted.

Select the data on the first worksheet

Go to your first worksheet (e.g., NewRepCodes) and select the specific column or range containing the data you want to format, such as column C.

Create a new formatting rule

Navigate to the 'Home' tab on the ribbon, click 'Conditional Formatting', and then select 'New Rule' from the dropdown menu. Choose the option 'Use a formula to determine which cells to format'.

Enter the COUNTIF formula

Input the formula =COUNTIF(TeamDirected!$C:$C,NewRepCodes!$C2)>0 into the rule description box, substituting the sheet names and cell references to perfectly match your workbook's layout.

Apply the desired formatting

Click the 'Format' button, navigate to the 'Fill' tab, choose a highlight color to visually flag the matches, and click 'OK' twice to apply the formatting rule.

Set the reciprocal rule on the second sheet

Navigate to the second worksheet (e.g., TeamDirected), highlight the target column, and repeat the conditional formatting process using the reciprocal formula: =COUNTIF(NewRepCodes!$C:$C,TeamDirected!$C2)>0.

Adjusting Formula References: Make sure to adjust the worksheet names, column letters, and starting row numbers in the formula carefully. If your worksheet names contain spaces, enclose them in single quotation marks within the formula (e.g., 'Team Directed'!$C:$C).

Efficiently Format Data Across Sheets

Use WPS Spreadsheet for Advanced Conditional Formatting

WPS Spreadsheet fully supports cross-sheet conditional formatting using formulas like COUNTIF. It offers a lightweight, user-friendly interface that handles complex data comparison tasks with ease.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the multiple sheets you want to compare.
2. Access Conditional Formatting: Highlight your target data column, navigate to the Home tab, and click on the 'Conditional Formatting' icon.
3. Apply the custom formula: Select 'New Rule', choose the formula option, enter your cross-sheet COUNTIF formula, pick a highlight color, and click 'OK'.

Seamlessly apply cross-sheet conditional formatting and advanced formulas.100% compatible with Microsoft Excel (.xlsx, .xls) formats and structures.Free, lightweight, and easy to navigate for professional data analysis.

Frequently Asked Questions

Can I use the standard 'Highlight Duplicate Values' feature across different sheets?

No, Excel's built-in 'Highlight Duplicate Values' preset only works within a single selected range on a single worksheet. To compare data across multiple sheets, you must utilize the 'Use a formula to determine which cells to format' option with a formula like COUNTIF.

Why is my conditional formatting formula returning an error across worksheets?

This typically happens due to syntax errors in the sheet reference. Ensure your worksheet names are spelled exactly as they appear on the tabs. Additionally, if your worksheet name contains spaces, you must wrap the name in single quotes within the formula (for example, =COUNTIF('Sheet 2'!$A:$A, A2)>0).

How can I highlight unique values that do not appear on the second worksheet?

To highlight unique values (differences) instead of duplicates across sheets, simply modify your COUNTIF formula to check for a count of zero. Change the '>0' at the end of your formula to '=0' (e.g., =COUNTIF(TeamDirected!$C:$C, NewRepCodes!$C2)=0).

Document & File Issues