How to Compare Two Excel Spreadsheets for Duplicate Rows

Written by ken burnside Google
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Compare Two Excel Spreadsheets for Duplicate Rows
Excel's COUNTIF function can be used to check for duplicate entries in multiple lists. (computer image by blaine stiger from

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.

Skill level:

Things you need

  • Excel 2007 or Excel 2010

Show MoreHide


  1. 1

    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.

  2. 2

    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.

  3. 3

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

  4. 4

    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.

Tips and warnings

  • 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.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.