How to Compare Two Excel Spreadsheets for Duplicate Rows

Updated April 17, 2017

Excel is used by many businesses as a lightweight database and contacts manager; one of the things this sort of business task requires is comparing lists of contacts and finding duplicate records. Fortunately, Excel's COUNTIF function makes this easy, even when comparing two different spreadsheets in the same workbook.

Open your spreadsheet and make sure you know what columns the data you want compared are in. This example will assume the data is in column A, and that the two lists to be compared are in two spreadsheet tabs, labelled Sheet 1 and Sheet 2 of the same workbook.

Enter the following formula in cell B1 of Sheet 1. =COUNTIF(Sheet1A:A,Sheet2A:A). Hit Enter. If the record is unique (it doesn't appear on both lists), this will evaluate to 0.

Copy the formula from cell B1 through as many rows as you need on Sheet 1.

Select columns A and B, and click on the Home tab (in Excel 2007) and select Sort and Filter. Choose to filter the results by Column B in ascending order. This will move all the results with a '0' in column B to the top, putting all your duplicate records at the bottom of the list.


This technique can be extended to check for duplicates in other workbooks, by making the COUNTIF function refer to sheets in an external workbook. In general, you are far better off copying the contents of those other workbooks into separate spreadsheets in the same workbook and using the technique here, as linking to external workbooks can result in errors if one of those linked workbooks gets moved, or has its name changed. The modified COUNTIF formula would look like this, if Sheet 2 were in a workbook called DataTwo.xls: \=COUNTIF(Sheet1A:A,'[DataTwo.xls]!Sheet2'A:A)


Large numbers of COUNTIF functions can slow down Excel calculations considerably; once you've sorted out your unique records, delete the column with the COUNTIF results before working with the data further.

Things You'll Need

  • Excel 2007 or Excel 2010
Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Ken Burnside has been writing freelance since 1990, contributing to publications as diverse as "Pyramid" and "Training & Simulations Journal." A Microsoft MVP in Excel, he holds a Bachelor of Arts in English from the University of Alaska. He won the Origins Award for Attack Vector: Tactical, a board game about space combat.