How to Merge Data From Two Excel Spreadsheets
· admin
Merging two spreadsheets is different from simply stacking one underneath the other. Usually you have information in one file that needs matching to the correct record in another.
For example, one sheet might contain customer IDs and names while another contains customer IDs and account balances. The job is to match the right balance to the right customer without creating a data casserole.
Find the common key
The safest merge starts with a field that identifies the same record in both datasets. A unique customer ID, order number, employee number or other stable reference is usually better than matching on names.
Names can be misspelled, duplicated or formatted differently. Unique IDs are much less dramatic.
Clean the key columns first
Check for leading or trailing spaces, numbers stored as text, inconsistent capitalisation where relevant, and missing IDs. Make sure the same identifier genuinely has the same format in both files.
Use a lookup for straightforward merges
Excel lookup functions can return a matching value from the second dataset. Modern versions of Excel include XLOOKUP, while older workbooks may use VLOOKUP or INDEX and MATCH.
Test the formula on a sample before filling it down thousands of rows.
Use Power Query for bigger merges
Power Query can merge tables using one or more matching columns. This is particularly useful when the process needs repeating or when several transformations are required before the data can be joined.
Check unmatched records
Do not treat missing matches as an inconvenience to hide. They often reveal missing IDs, formatting differences or genuinely absent records.
Review both matched and unmatched rows before relying on the merged result.
Watch out for duplicate keys
If the supposedly unique identifier appears several times in one dataset, a merge can produce unexpected duplicates. Work out whether those repeats are legitimate before removing them.
Our guide on removing duplicates safely explains the distinction.
Can’t be faffed?
If you have two or more datasets that need matching, joining, checking and cleaning, Faff It Off.
Send us the files and explain what the finished spreadsheet should contain. We’ll quote for doing the matching without the spreadsheet archaeology.