Highlight Values in One Column That Appear in Another in Excel
Question details
The user wants to use conditional formatting to highlight values in a specific target column (e.g., Column D) only if those exact values also exist in a reference column (e.g., Column E).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two columns to visually identify matching data.
- Observed behavior
- The goal is to accurately highlight matching duplicate values in the primary column without incorrectly highlighting empty cells or mismatched values.
Ensure both columns contain data in consistent formats (e.g., both are formatted as text or numbers), as mismatched formats can cause the formula to miss duplicates. Clear any existing conditional formatting rules from the target column to avoid overlapping conflicts.
Use Conditional Formatting with a COUNTIF Formula
Use a custom formula within the Conditional Formatting tool to dynamically check if the value in the target column exists in the reference column.
This method uses a combination of the AND function and COUNTIF function to cross-reference the two columns. It ensures that blank cells are ignored and only exact matches trigger the highlight.
Highlight the data range in your target column where you want the colors to appear (for example, select D2:D100).
Go to the Home tab on the Excel ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.
In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.
Type the formula: =AND(D2<>"",COUNTIF($E$2:$E$100,D2)>0). Ensure that 'D2' is the first cell in your selected range and '$E$2:$E$100' is the absolute reference to your second column.
Click the Format button, go to the Fill tab, select your preferred highlight color, and click OK twice to apply the rule to your column.
Highlight Matching Column Values Quickly in WPS Spreadsheet
WPS Spreadsheet offers a powerful and intuitive Conditional Formatting tool, allowing you to highlight cross-column duplicates just like Microsoft Excel. It handles complex data seamlessly and guarantees a smooth data management experience.
- 1. Select your column data: Open your document in WPS Spreadsheet and highlight the column range you want to format.
- 2. Open Conditional Formatting: Navigate to the Home tab, click on Conditional Formatting, and choose New Rule.
- 3. Apply the cross-column formula: Select 'Use a formula to determine which cells to format', enter your COUNTIF formula, set your custom color, and click OK.
Frequently Asked Questions
Why is my conditional formatting formula highlighting the wrong cells?
This usually happens if the active cell when creating the rule does not match the relative reference in your formula. Make sure that if you select D2:D100, your formula explicitly refers to D2, not D1 or D3. Also, ensure absolute references (with $) are used correctly for the lookup range (e.g., $E$2:$E$100).
How do I highlight values in both columns instead of just one?
To highlight duplicates in both columns, you do not need a complex formula. Simply select both data ranges (e.g., D2:D100 and E2:E100), go to Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel will automatically highlight any value that appears more than once across the selected ranges.
Can I highlight rows based on matching values in another sheet?
Yes. You can modify the COUNTIF formula to reference a range in another worksheet. For example, use =AND(D2<>"",COUNTIF(Sheet2!$E$2:$E$100, D2)>0) in your conditional formatting rule to check against a column on 'Sheet2'.
