Pull Data From Multiple Sheets & Consolidate in Excel & Google Sheets

This tutorial will demonstrate how to pull data from multiple sheets and consolidate in Excel and Google Sheets.

 

 

You can consolidate data in Excel by manually creating linking formulas or using Paste Special. However, Excel’s Consolidate feature is a faster and less likely to introduce errors. This tutorial focuses on Consolidate.

The Consolidate feature can be used in either of two ways – combining data by position or combining data by category – from multiple sheets into one. If you combine data by position, each of the sheets needs to have an identical layout, whereas if you are combining by category, each sheet needs to have the same row and/or column headers.

Consolidate by Position

1. Open the Excel file that contains the sheets whose data you wish to consolidate and create a new blank sheet.
2. Select the cell where you want the data to be placed, and then, in the Ribbon, select Data > Data Tools > Consolidate.

 

 

3. Select the Function to consolidate by (Sum, to add the sheets together).
   Then, to select data from within the current workbook, click on the little up arrow.

 

 

4. Select the worksheet and data to add to the consolidation and then click the little arrow again (this will have switched to a down arrow).

 

 

5. Click Add to add the cell references to the All references list in the Consolidate window.

 

 

6. Repeat this process by selecting the data in each sheet you wish to consolidate.

 

 

7. Click OK to consolidate the data to your selected sheet.

 

 

Include Labels

Note that this method only brings in the numbers; it does not bring in any row or column labels.

To bring across the row and column headers, make sure Top row and Left column are checked in the Consolidate dialog box.

 

 

The row and column headings will then be returned with the data.

 

 

Link to Original Data

The data will come into the consolidation sheet as raw numbers; they will not be linked to the individual sheets unless you specifically create links to the sheets.

In the Consolidate dialog box, check Create links to source data.

 

 

Now when you click OK to create the master sheet, each of the consolidated cells contains a formula linked to the individual source sheets, and the total amount is the sum of the linked formulas.

 

 

An Outline will automatically be created, enabling you to expand and condense the data as necessary.

Consolidate by Category

If your worksheets are not laid out identically, they can still be consolidated – as long as the row and column headings are identical.

Select each of the individual sheets and add the references to the Consolidate dialog box. Complete Steps 1–7 from the previous section.

 

 

Notice that in the references depicted above, some of the data starts at Row 6, some at Row 5, and some at Row 9. Similarly, while A is the column used in three of the references for the starting point, B is used in one of them – the four worksheets are clearly not identically laid out.

However, all sheets have the same row and column headers, so the consolidation will work correctly.

 

 

If you show formulas in Excel (CTRL + `), and then click on the expand button in the row headers, you will see the linked formulas and that cell references differ among sheets.

 

 

Note: If some of the column and/or row headings are not identical, then separate rows or columns are created for them on the master spreadsheet. For example, if you had Apr as the heading in some sheets instead of April, you’d end up with the data as shown below:

 

Consolidate Data in Google Sheets

Google Sheets does not have a consolidation feature available. You can, however, consolidate data manually or by using Paste Special as explained here.