See all How-To Articles

Merge Two Spreadsheets / Tables in Excel & Google Sheets

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

 

CombineSheets Intro

 

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.

 

CombineSheets Ribbon

 

  1. In the Reference box, click on the small arrow to the right.

 

CombineSheets-DropDown-Reference

 

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

 

CombineSheetsSelect Reference

 

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

 

CombineSheets Add Reference

 

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

 

CombineSheets All References

 

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

 

CombineSheets Labels

 

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

 

CombineSheets Summary

 

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.

 

CombineSheets Expanded

 

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.

 

CombineSheets Expand All

 

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

 

CombineSheets Expand one

 

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.

 

CombineSheets Change Data

 

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.

 

CombineSheets GS Formula

 

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

 

CombineSheets GS Copy Formula

 

  1. You can get the headings from a single sheet be creating a formula that refers to that sheet.

 

CombineSheets GS Headings

 

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