Merge Two Spreadsheets / Tables in Excel & Google Sheets

This tutorial demonstrates how to merge tables from two spreadsheets in Excel and Google Sheets.

 

 

Consolidate Data

1. Open the file that contains the sheets you wish to consolidate into one and create a new sheet for the consolidation. You may want to name the new sheet something like Summary.
2. Select the destination cell for the summary data. Then in the Ribbon, go to Data > Data Tools > Consolidate.

 

 

3. In the Reference box, click the small arrow to the right.

 

 

4. Highlight the table of information in the first worksheet you wish to have in the consolidation, and then click once again on the small arrow on the right side of the Consolidate – Reference box.

 

 

5. The data range is shown in the Reference box. Click Add to add it to the consolidation.

 

 

6. Repeat this as often as necessary to add all the references to the consolidation list.

 

 

7. If your data contains heading in the top row and /or left column, tick those checkboxes.
   Optionally, you can also check Create links to source data. This links the consolidated summary sheet to the original sheets; if any data changes in the original sheets, they carry on to the summary sheet.

 

 

8. Click OK to create the consolidation in your new summary sheet.

 

 

Work With Merged Data

In the example above, since there are links to source data, the summary has been created with formulas linking to each of the individual sheets. Expand the summary information by clicking on the small grouping plus sign to the left of the row headers. You can then see the linked formula in the formula bar.

 

 

To show all the information expanded, click on the small 2 in the top-left corner of the screen. This shows both levels of the grouping.

 

 

Click on the 1 to show only the first level of the grouping.

 

 

Since the data is linked to the individual sheets, if a value in one of the linked sheets is changed, the summary data also changes.

 

 

Merge Two Spreadsheets in Google Sheets

You can consolidate data in Google Sheets, but you have to do it manually by creating a formula that links the sheets together. Google Sheets doesn’t have a Consolidate feature.

1. In your Google Sheets file, create a new blank sheet.
2. In the cell where you want the consolidated data to go, create a formula that totals the data from each individual sheet.

 

 

3. You can then copy this formula down and across to get the information from the other cells in the sheets whose data you wish to consolidate.

 

 

4. You can get the headings from a single sheet by creating a formula that refers to that sheet.

 

 

Note: You can also use this manual method to consolidate data from different sheets in Excel if you don’t wish to use Consolidate.