Excel: Pull Data from Multiple Sheets into One Sheet

You can use the Consolidate function in the Data tab in Excel to pull data from multiple sheets into one master sheet.

The following example shows how to use this function in practice.

Example: Pull Data from Multiple Sheets into One Sheet in Excel

Suppose we have the following dataset in a sheet called year1 that shows the total sales in four different retail stores during the first year of business:

Suppose we have the same data for the second year of each business in a sheet called year2:

And suppose we have the same data for the third year of each business in a sheet called year3:

Now suppose we would like to pull data from each sheet into one master sheet that summarizes the total sales made by each store during the three years.

To do so, we can create a new sheet called summary.

We can then make sure cell A1 in this sheet is active and then click the Data tab along the top ribbon and then click the icon called Consolidate within the Data Tools group:

In the new window that appears, choose Sum as the function, then type the name of the range for the first sheet you’d like to use and click Add.

Repeat this step for the next two ranges.

Then check the boxes next to Use labels in Top Row and Left column:

Once you click OK, the data from each of the three individual years will be summarized in one sheet:

This summary sheet contains the sum of the sales for each store in the three individual sheets.

For example, the North store had individual sales values of 22, 14 and 14 in the individual sheets.

The sum of these values is 22 + 14 + 14 = 50.

The same calculation is performed on the East, South and West stores.

Note that you can also consolidate the data using a different summary statistic (e.g. count, average, max, etc.) by choosing a different value from the dropdown menu for Function within the Consolidate window.