Merge Two Spreadsheets / Tables in Excel & Google Sheets

This tutorial will demonstrate 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, select Data > Data Tools > Consolidate.

 

 

3. In the Reference box, click on 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-hand 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, make sure the required options are checked.
   Optionally, you can also check Create links to source data. This will link the consolidated summary sheet to the original sheets which means that if any data changes in the original sheets, then the summary sheet will change.

 

 

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

 

 

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. You can expand the summary information by clicking on the small grouping plus sign to the left of the row headers. You will then be able to see the linked formula in the formula bar.

 

 

To show all the information expanded, click on the small 2 in the top left-hand corner of the screen. This will show 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 would also change.

 

 

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 required 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 will add up the data from all the individual sheets.

 

 

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