How to Merge Excel Spreadsheets to Find Duplicates

Written by peyton brookes
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Merge Excel Spreadsheets to Find Duplicates
Spreadsheet programs offer dynamic information management tools. (number background image by kuhar from

A powerful spreadsheet program, Microsoft Excel offers dynamic information management tools. Excel workbooks are comprised of columns, rows and cells. Excel contains 16,777,216 cells that hold your data and information. With that many cells, duplicate information can be difficult to locate. Excel offers a formula feature that will allow you to locate duplicate information in your spreadsheets. Merging spreadsheets to find duplicates in Excel is a two-step process.

Skill level:

Other People Are Reading


  1. 1

    Identify the duplicate values you are searching for. Identify the data type and make sure it is the same in both spreadsheets. For example, if you would like to identify duplicate dates, make sure the dates are recorded in the same format (9/12/2034) across spreadsheets.

  2. 2

    Confirm the spreadsheets have the same number of columns. To merge spreadsheets, make sure they have the same number of columns. If not, make sure the column headers match until it no longer matters. For example, if spreadsheet A has four columns and spreadsheet B has six columns, make sure the first four columns of both spreadsheets are First Name, Last Name, Phone and Email. Make sure there are no empty columns or rows.

  3. 3

    Copy and paste or consolidate. Copy the information from spreadsheet A and paste the information into spreadsheet B below the last record on spreadsheet B. Each column in spreadsheet B should line up with the appropriate column in spreadsheet A.

  1. 1

    Insert a new column. Insert the new column next to the column with the duplicate value. For example, if you are looking for duplicate phone numbers in column C, insert a new column to the left of column C. Column C should be come before column D. Type the title "Duplicate?" into the column's first row to name the column.

  2. 2

    Enter the formula for identifying one column duplicates. Select the second row of your "Duplicate?" column and enter the duplicate formula: =IF(D2=D3, "Duplicate",""). This formula will identify duplicate records in column D only. For duplicates in multiple columns, enter =IF(D2&E2=D3&E3, "Duplicate",""). This formula will identify duplicate records in columns D and E.

  3. 3

    Copy and paste the formula. Copy the formula down the length of the appropriate column. Right-click the cell with the formula and select "Copy" from the shortcut menu. Select each cell down the length of the appropriate column and right-click and select "Paste Special...", then click "Values" and "OK."

  4. 4

    Sort the "Duplicates?" column. Sort your spreadsheet based on the duplicate column. Sort the duplicate column in descending order to move each duplicate to the top of the list.

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.