How to Merge Excel Spreadsheets to Find Duplicates

Updated March 23, 2017

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.

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.

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.

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.

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.

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.

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

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.

Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Peyton Brookes is a workforce development expert and has written professionally about technology, education and science since 2009. She spent several years developing technology and finance courses for social programs in the Washington, D.C. area. She studied computer and information science at the University of Maryland College Park.