How to compare two columns and count differences in Excel?
If there are two columns, which include duplicate values, how can you compare the columns and count only the different values in Excel? In this article, I will introduce methods to count the differences by cells or rows between two columns/ranges.
Compare methods for counting differences between two columns or ranges
Compare and count differences between two columns by cells 
Compare and count differences between two ranges by rows 
Compare methods for counting differences between two columns or ranges
The methods in this tutorial differ not only in how the differences are counted, but also in whether they can compare individual cells or entire rows. The table below highlights the key differences.
| Method | Comparison level | How the count is obtained | Best for |
|---|---|---|---|
| Conditional Formatting + Filter | Individual values in two columns | Highlight matches, filter the remaining values by color, and read the count from the status bar | A built-in Excel solution for occasional cell-level comparisons |
| Kutools for Excel | Individual cells or entire rows | Selects the differences and reports their number directly in a dialog | Quickly finding and counting differences without formulas, formatting rules, or filtering |
Compare and count differences between two columns by cells
Method 1: Conditional Formatting and Filter functions
To compare two columns and count differences by cells, you can use the Conditional Formatting function to highlight the duplicates first, then use the Filter function to count the total differences.
1. Select the column you want to count the differences, click Home > Conditional Formatting > New Rule.
2. In the New Formatting Rule dialog, select Use a formula to determine which cells to format in the Select a Rule Type section, then type this formula =COUNTIF($C:$C,$A1) into the Format values where this formula is true textbox.
3. Click Format, under the Fill tab, select a color for the duplicates.
4. Click OK > OK. The duplicates have now been highlighted with the specified background color.
5. Select the column in which you highlighted the cells, click Data > Filter, and then click the Filter icon to select Filter by Color > No Fill.
Now the differences in this column have been filtered. Select the filtered data and check the Status Bar to see the count.
Tip: In the formula =COUNTIF($C:$C,$A1), A1 is the first cell of the column you want to count differences in, and column C is the other column you want to compare with.
Method 2: Select Same & Different Cells
If you have Kutools for Excel installed, you can use its Select Same & Different Cells utility to quickly find and count differences between two columns in just a few steps.
Kutools for Excel - Packed with over 300 essential tools for Excel. Make Excel tasks faster, easier, and more efficient. Download now!
1. Click Kutools > Select > Select Same & Different Cells.
2. In the Select Same & Different Cells dialog, select the two columns without headers in the Find values in and According to textboxes, then check the Single cell and Different Values options.
3. Click OK. A dialog box displays the number of different cells found, while the differences are selected in the worksheet.
Compare and count differences between two ranges by rows
If you want to compare the two ranges row by row rather than checking individual values, the same Select Same & Different Cells feature in Kutools for Excel can perform the comparison by entire row and report the number of different rows directly.
1. Click Kutools > Select > Select Same & Different Cells.
2. In the Select Same & Different Cells dialog, select the two ranges without headers in the Find values in and According to textboxes, then check Each row and Different Values.
3. Click OK. The different rows in the range specified in Find values in are selected, and the number of differences is displayed in the pop-up dialog.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Demo: Select Same & Different Cells using Kutools
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in