Formatting Issues

How to Highlight Values in Excel Sheet 1 That Appear in Sheet 2

Nimra Malik Oct 9, 2026 870 views

Question details

The user needs to compare a specific column across two worksheets and highlight the cells in the first sheet that have matching values in the second sheet, without highlighting duplicates that only exist within the first sheet.

How to Highlight Values in Excel Sheet 1 That Appear in Sheet 2

Product: Excel
Device & OS: not provided
Scenario: Comparing lists of data across different worksheets to identify and visually highlight overlapping entries.
Observed behavior: Values present in both worksheets are successfully highlighted on Sheet 1, while duplicates occurring exclusively within Sheet 1 remain unformatted.

Before you start

Ensure both worksheets are located within the same workbook and note the exact spelling of your second worksheet's name, as you will need it for the formula reference.

Solution 1Recommended

Use Conditional Formatting with ISNUMBER and MATCH

This method dynamically highlights cross-sheet matches without altering your data or requiring complex VBA macros.

By combining Conditional Formatting with the ISNUMBER and MATCH functions, you can force Excel to look at another worksheet and return a true/false condition for formatting. Because the formula specifically checks the second sheet, it ignores any duplicates that exist solely within the first sheet.

Select the target range

Open Sheet1 and select the range of cells you want to compare, for example, click and drag to select B2:B100.

Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.

Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Enter the matching formula

In the formula bar, enter =ISNUMBER(MATCH(B2,'Sheet2'!B:B,0)). If your second sheet is named differently, replace 'Sheet2' with your actual worksheet name.

Apply a highlight format

Click the Format button, go to the Fill tab, choose a highlight color, and click OK twice to apply the formatting rule to your selected range.

Use Conditional Formatting with ISNUMBER and MATCH

Cell Reference Accuracy: Ensure the first cell reference in your formula (e.g., B2) is relative (no $ signs) and matches the very first cell of your selected range. Otherwise, the highlighting will be misaligned.

Efficient Data Analysis with WPS Office

Highlight Cross-Sheet Matches Easily in WPS Spreadsheet

WPS Spreadsheet provides powerful Conditional Formatting and comprehensive formula support, making it simple to compare data across multiple worksheets. You can seamlessly use the exact same MATCH formula as in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your two lists.
2. Select the data range: On your primary sheet, select the column or range of cells you want to highlight.
3. Create a new formatting rule: Go to the Home tab, click Conditional Formatting, and choose New Rule.
4. Apply the cross-sheet formula: Choose 'Use a formula to format cells', input =ISNUMBER(MATCH(B2,'Sheet2'!B:B,0)), select a background color under Format, and click OK.

Fully compatible with Microsoft Excel formulas and formatting (.xlsx files).Advanced Conditional Formatting tools for quick and accurate data visualization.Free, lightweight, and user-friendly interface for seamless data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows?

This misalignment usually happens if your formula uses an absolute reference for the starting cell (like $B$2 instead of B2) or if your highlighted range starts at a different row than the one specified in your formula.

Can I use VLOOKUP instead of MATCH for conditional formatting?

Yes, you can use the formula =NOT(ISNA(VLOOKUP(B2,'Sheet2'!B:B,1,FALSE))). However, MATCH combined with ISNUMBER is generally preferred as it is slightly faster for simply checking if a value exists.

How do I highlight values in Sheet 1 that do NOT appear in Sheet 2?

To highlight unique values that are missing from the second sheet, you can use the ISNA function instead of ISNUMBER. The formula would be =ISNA(MATCH(B2,'Sheet2'!B:B,0)).

Will this formula accidentally highlight duplicates within Sheet 1?

No. The MATCH formula is specifically instructed to look at the array in 'Sheet2'. If a value appears multiple times in Sheet 1 but is completely absent in Sheet 2, the formula returns false and the cell remains unformatted.

Document & File Issues