How to Match Two Columns Microsoft Excel

Updated on:

May 12, 2026

By:

Use this interactive demo to learn how to compare and highlight matching values across two Excel columns.

Quick summary

Matching two columns in Microsoft Excel using Conditional Formatting and the COUNTIF formula lets you instantly highlight duplicate or matching values across datasets. This method visually flags matching cells with a color fill, making data comparison fast and error-free.

Steps

Select the column you want to compare against the other dataset.
Navigate to Home, then open Conditional Formatting and click New Rule.
Choose the rule type "Use a formula to determine which cells to format."
Enter the COUNTIF formula =COUNTIF($A:$A, C2)>0, adjusting the column references to match your data.
Set your desired format — for example, change the fill color to blue — then click OK.
Review the results: all matching cells will now be highlighted in blue for easy identification.

📌 Why this matters

Comparing two columns in Microsoft Excel is one of the most common data validation tasks for analysts, administrators, and business users. Using Conditional Formatting with a COUNTIF formula allows users to instantly identify matching or duplicate values across large datasets without manual lookup. This approach saves significant time during data audits, deduplication workflows, and cross-referencing tasks. Mastering this Excel technique reduces errors and improves data accuracy across reporting, reconciliation, and data cleaning projects.

Your product deserves an interactive demo

Start free

How to Organize Dates by Week in Excel

How to Make Cells Fit Text Microsoft Excel

How to Make a Bar Graph in Microsoft Excel