Compare and merge Excel tables
Related Videos: Excel 2016 Tutorial Compare and Merge Workbooks Microsoft Training Lesson (May 2024).
You can compare and merge old and newer Excel spreadsheets in a few simple steps without having to manually compare all data. You can find numerous complicated formulas on the Internet - we will show you how this works more easily.
How to Compare Excel Spreadsheets (Quick Start Guide)
To compare tables and remove duplicate content, use Excel as described in our quick guide. Below you will find our step-by-step instructions with pictures.
- First, open both Exel tables.
- Now insert the content of the first table into the second table. To do this, mark all data with the key combination [CTRL] + [A], copy the table with [Ctrl] + [C] and then paste it at the end of the second data record with [Ctrl] + [V].
- To filter out duplicate entries, first select the corresponding column.
- Under the "Data" tab you will find the "Remove duplicates" button. The tool now searches the marked column for duplicates and automatically removes multiple entries.
Step 1: assemble the contents of the tables
Open one of the tables you want to compare and copy & paste the second table at the end of the first table.
Step 2: mark the area
Select the area or column that you want to check for duplicate entries.
Step 3: remove duplicates
Now switch to "Data" above. With the "Remove duplicates" tool, Excel now finds all duplicate entries.
Method 2 (quick start guide)
There is another way to compare Excel spreadsheets.
- Copy the first table and paste it on a new sheet in the same file.
- Now mark the inserted table if it is no longer selected.
- In the "Start" menu, click "Conditional Formatting" and then define a "New Rule".
- Select "Use formula to determine the formatting cells" and enter the formula "= A1Tab1! A1". Here we assume that the table begins in field A1. Of course, you can also start the formula in another field.
- Then all changes are marked and you can compare the tables directly.
Step 1: insert table in new sheet
Insert the new table in a new sheet of the first table and select it.
Step 2: conditional formatting
Select the function "Conditional formatting and here" New rule ".
Step 3: Formula to determine the formatting cells
In the dialog box, select "Use formula to determine the formatting cells" and enter the formula "= A1Tab1! A1" to compare the tables.
In the next article, we will show you how you can then insert the Excel table into Word.