Function Problems

How to Compare Two Excel Sheets and Highlight Duplicates by Name and Date

WPS Editor Oct 9, 2026 872 views

Question details

The user wants to compare two Excel sheets with different layouts and highlight records where the same name appears on the exact same date.

Compare Two Excel Sheets and Highlight Duplicate Names by Date

Product: Excel
Device & OS: not provided
Scenario: Cross-referencing two distinct datasets to visually identify matching entries based on two specific criteria: Name and Date.
Observed behavior: The standard COUNTIF conditional formatting formula fails to evaluate multiple criteria, leaving the matching records unhighlighted.

Before you start

Ensure that the Date columns in both sheets are formatted identically (e.g., as Short Date) and remove any trailing spaces in the Name columns to prevent formula mismatch errors.

Solution 1Recommended

Use COUNTIFS with Conditional Formatting

Replace the single-criterion COUNTIF with a COUNTIFS formula to check for both the Name and Date conditions simultaneously across different sheet layouts.

Since you are comparing two specific variables (Name and Date), the standard COUNTIF function will not work because it only evaluates one condition. The COUNTIFS function is designed for multi-criteria comparisons.

Assuming your current sheet has Names in Column A and Dates in Column B, and you are comparing them to 'Sheet2' which has Names in Column C and Dates in Column D.

Select the target data range

Open your first sheet and highlight the data range you want to apply the formatting to (e.g., A2:B100).

Open Conditional Formatting

Navigate to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule'.

Enter the COUNTIFS formula

Choose 'Use a formula to determine which cells to format'. Enter the following formula: =COUNTIFS(Sheet2!$C:$C, $A2, Sheet2!$D:$D, $B2)>0

Apply formatting style

Click the 'Format' button, go to the 'Fill' tab, select a highlight color (like yellow), and click 'OK' twice to apply the rule.

Use COUNTIFS with Conditional Formatting

Relative vs. Absolute Referencing: Using the dollar sign ($A2) locks the column but allows the row number to change, ensuring the formula checks each row individually while highlighting the entire row if desired.

Solution 2

Use Power Query to Find Matches Across Layouts

If the sheet layouts are highly complex, use Power Query to merge the tables based on Name and Date to filter out the exact matches without formulas.

Seamless Data Comparison

Easily Highlight Duplicates with WPS Spreadsheet

WPS Spreadsheet provides a highly intuitive Conditional Formatting manager and full support for advanced multi-criteria formulas like COUNTIFS, making complex data comparison across multiple sheets incredibly easy.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your two data sheets.
2. Select your data: Highlight the columns or specific range where you want the duplicate highlights to appear.
3. Apply the COUNTIFS formatting rule: Go to Home > Conditional Formatting > New Rule. Select the formula option, paste your COUNTIFS formula, choose a fill color, and click OK.

100% compatibility with Microsoft Excel formulas including COUNTIFS, VLOOKUP, and INDEX MATCH.Visual and user-friendly Conditional Formatting rules manager.Lightweight software that processes large datasets quickly without lagging.Free to use with a familiar, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIF formula returning an error for two columns?

The standard COUNTIF function only supports a single criteria range and a single condition. To evaluate two or more conditions (like matching both a name and a date), you must use the COUNTIFS function.

How do I reference another sheet in my conditional formatting formula?

You can reference another sheet by typing the sheet name followed by an exclamation mark before the cell range, such as Sheet2!$A:$A. If the sheet name contains spaces, you must wrap it in single quotes: 'Data Sheet'!$A:$A.

Can I highlight the entire row instead of just the specific cells?

Yes. To highlight the entire row, ensure your conditional formatting applies to the whole range (e.g., $A$2:$F$100) and lock the column references in your formula using a dollar sign, such as $A2 and $B2.

What if the dates look the same but the formula isn't highlighting them?

Dates might contain hidden time values or be formatted as text. Use the INT() function or reformat the columns to ensure both sheets use the exact same underlying serial date numbers.

Excel & Data Problems