Merge Two Spreadsheets / Tables in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on April 14, 2023

This tutorial demonstrates 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, go to Data > Data Tools > Consolidate.


CombineSheets Ribbon


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




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


CombineSheets Labels


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


CombineSheets Summary


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.


CombineSheets Expanded


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.


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 also changes.


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


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

AI Formula Generator

Try for Free

See all How-To Articles