Question

Excel tables - this is how it works

Answer 1

Merge tables with the same entries
1st step:
2nd step:
3rd step:
4th step:
5th step:
6th step:
7th step:
8th step:
9th step:
10th step:
Video
Merge tables with different entries
1st step:
2nd step:
3rd step:
4th step:
5th step:
6th step:
7th step:
8th step:
9th step:
Quick Start Guide: Matching Entries
Brief instructions: Different entries

---

Would you like to merge several tables in Excel? Then find out how to do it here..

For evaluations or comparisons, data is often required that is contained in two or more worksheets in Excel tables. If you want to merge columns and rows from tables in Excel, then the integrated "Consolidate" function offers exactly that. The file should consist of at least two worksheets that you want to merge. The data in your Excel table does not necessarily have to be structured in the same way. To compare data, however, they should have the same basic structure. In this tipps + tricks article, we will use an example to show you how you can merge the data from different tables in an Excel workbook.

• Merge tables with the same entries
• Merge tables with different entries

Merge tables with the same entries

Follow our step-by-step instructions , the explanatory video or the brief instructions .

1st step:

Open an Excel file that contains at least two worksheets that are to be merged. At the bottom of the workbook, to the right of the last worksheet, click the " + " to add a new blank worksheet. You can give the worksheet a suitable name with a right click > " Rename ". For our example we choose "Summary".

2nd step:

Click on the cell where you want to insert the merge of your tables. We choose cell " A1 ". You can recognize the marked cell by the green selection border .

3rd step:

Click the Data tab in the menu bar at the top . Here you select the " Consolidate " function under " Data tools " .

4th step:

From the drop-down menu the desired function from the merger. We want the data to be added in our example, so we select " Sum " accordingly .

5th step:

Next to the text box for " Reference ", click the up arrow. The " Consolidate " window is then minimized and changes to " Consolidate Reference ".

6th step:

In the worksheet you want, select the data that you want to merge. To do this, first click on the name of the worksheet below and mark the data with the left mouse button pressed. You can recognize your selection by a dashed line around the data. The selected area also appears in the "Consolidate - Reference " window .

7th step:

In the Consolidate - Reference window, click the down arrow to the right of the text box.

8th step:

The link now appears in the form "Sheet name! Line: Column: Line: Column". Now add this to the existing references via " Add ". For the selection process for the second worksheet, repeat steps 5 through 8.

9th step:

Select the check boxes to the left of " Top Row " and " Left Column " so that your newly merged table is properly formatted. Finally click on " OK ".

10th step:

The selected data is now merged and displayed in the desired cell in the new worksheet .

Video

Merge tables with different entries

Consolidating is not always the right way to merge tables. An example: You have several Excel spreadsheets with identical IDs for your customers, but different entries such as location or telephone number. The solution: the integrated Power Query editor from Excel. This enables you to create a new table that contains the complete data for both tables. To do this, follow our step-by-step instructions or take a look at our brief instructions ..

1st step:

In our example we have the following case: We have two tables in an Excel file with an identical column and additional columns with different contents. The aim is now to bring this content together in a new table based on the common column. Excel provides the Power Query Editor for this.

2nd step:

We select table 1 and go to " Data " on the menu ribbon at the top . Now click on " From table ".

3rd step:

The Power Query Editor opens. The previously selected table is automatically opened as a query. In our case this is table 1. To add the other table you want to merge Table1 with as an additional query, click New Source at the top . We select "File" and "Excel". Of course, you can also import additional data from a .CSV file or other databases. However, it is important that a column is identical in order to add the data together.

4th step:

We select "Table2" from the example Excel file and click " OK ". A preview of the table is displayed in the Navigator. Note that the table contains different values ​​but the same IDs in order to assign the values ​​accordingly. Your table can also contain other unique values ​​in a column, such as name or phone number, to merge the tables.

5th step:

Under Queries, go to "Table1" or the name of your table. Under " Combine " in the menu, click on " Merge Queries ".

6th step:

This opens the dialog box " Merge ". Select the matching columns from the tables and click OK . As an example, we select "Person ID" and "ID".

7th step:

Display the other table in your output table by clicking on the two-arrow symbol . Here you then select which entries should be added to the table. Logically, we deselect ID in order not to receive duplicate entries. We keep the hooks for company and location. If you are satisfied with the selected entries, click on " OK ".

8th step:

Finally click on " Close & Load " in the menu at the top left. Tip: You can change the column title in the query editor by double-clicking.

9th step:

The merged tables are displayed as a new table in your open Excel document. This contains the complete data of both tables sorted according to the corresponding column.

Quick Start Guide: Matching Entries

1. Open the Excel workbook and click the " + " in the lower right of the worksheet names .
2. Go to the cell in the new worksheet where you want to insert the merge.
3. From the menu bar, choose Data > Data Tools > Consolidate .
4. In the drop-down menu then select the desired function from.
5. Click the arrow next to the References text box .
6. In the worksheet, mark the data that you want to merge.
7. Once selected, click the arrow in the Consolidate - Reference window .
8. Now go to " Add ". Repeat the process for all worksheets that you want to merge.
9. Check the two check boxes . Confirm the merging with " OK ".

Brief instructions: Different entries

1. Open the first table to be merged with other tables.
2. Now go to " Data "> " From table ".
3. The Power Query Editor opens with the table open. To add more tables, click New Source > File > Excel . Instead of Excel files, you can also import .CSV files or other databases.
4. Select the table you want to merge with the first table from the file and click OK .
5. Under " Combine " go to " Table1 " or the appropriate name of your table. Now click on " Merge queries " in the menu .
6. Select the matching columns from the tables and click OK .
7. Display the other table in your output table by clicking on the two-arrow symbol .
8. Then click on " Close & load ". A new table with all the entries is now displayed in your Excel document.

---