How to Pull Data from Multiple Worksheets in Excel (4 Ways)

Data Pulled from Multiple Worksheets Using Power Query

While working in Excel, we often have to work with a lot of worksheets in a workbook.

Today I will be showing how you can pull data from multiple worksheets to a single worksheet in Excel.

Download Practice Workbook

How to Pull Data from Multiple Worksheets in Excel

Here we have three worksheets in a workbook. They contain the sales record of some items over three months: January, February and March respectively.

Sheet1 of a Workbook

Sheet2 of a Workbook

Sheet3 of a Workbook

Our objective today is to pull data from these three worksheets into a single worksheet to use for calculation.

1. Pulling Data by Formulas

If you want to perform any operation on the data from multiple sheets, you can perform this through formulas.

Place the name of the sheet (Sheet_Name!) before the cell reference when there are cell references of multiple sheets in a formula.

Let’s try to find out the total number of each product sold in the three months.

Select any cell in any worksheet and enter the formula in this way:

=January!D4+February!D4+March!D4

Formula with Cell Reference from Multiple Worksheets

Then drag the Fill Handle to copy the formula to the rest of the cells.

Formulas with Cell References from Multiple Worksheets

See, we have got the total sales of three months for each product.

Explanation of the formula

  • Here January!D4 indicates the cell reference D4 of the sheet name “January”. If you have the sheet name as Sheet1, use Sheet1!D4 instead.
  • Similarly February!D4 and March!D4 indicate the cell reference D4 of the sheet named February and March respectively.
  • Thus you can pull data from multiple sheets into one formula in a single sheet and perform any desired operation.

2. Pulling Data by Consolidate Operation

We can pull data from multiple worksheets and use them in an operation using the Consolidate tool from the Excel toolbar.

  1. Select an empty range of cells in any worksheet and go to the Data>Consolidate tool under the Data Tools section.

Consolidate Tool in Excel Toolbar

  1. Click it. You will get the Consolidate dialogue box. Under the option Function, select the operation you want to perform on the data from multiple worksheets.

For the sake of this example, select Sum.

Consolidate Dialogue Box in Excel

  1. Click on the Import icon right to the Reference box.

Consolidate Dialogue Box in Excel

  1. The Consolidate box will be compressed to Consolidate – Reference box. Select the desired range of cells from the first sheet. Then again click the Import icon to the right.

Selecting Range of Cells in Consolidate Box

  1. You will find the cell reference of the selected range inserted in the Reference box. Click the Add button right to the Add references box.

Add Button in the Consolidate Box

  1. You will find the references of the selected range inserted in the Add references box.

Cell References Inserted in the Consolidate Box

  1. Select the other ranges of cells from the other worksheets and insert them in the Add references box in the same way.

For the sake of this example, select D4:D20 from the worksheet February and D4:D20 from the worksheet March.

Inserting Cell References in the Consolidate Box

  1. Then click OK. You will find the sum of the three selected ranges from three worksheets inserted in the empty range.

Consolidate Operation Performed in Excel

3. Pulling Data by Macros

Until now, we have pulled data from multiple worksheets to perform some operations.

What to do if we do not want to perform any operation, only collect data from multiple worksheets and arrange them vertically in one worksheet?

Look at the data set below.

Here we’ve a new workbook with three worksheets, each having the sales record of four weeks of the months January, February and March respectively.

Sheet1 of a Workbook

Sheet2 of a Workbook

Sheet3 of a Workbook

Our objective is to collect data from these three worksheets and arrange them in one worksheet.

We can execute this by running the following Macro (VBA Code).

Click here to know how to create and run a Macro in Excel.

VBA Code

Sub Combine()

    Dim I As Long

    Dim xRg As Range

    On Error Resume Next

    Worksheets.Add Sheets(1)

    ActiveSheet.Name = "Combined"

    For I = 2 To Sheets.Count

        Set xRg = Sheets(1).UsedRange

        If I > 2 Then

            Set xRg = Sheets(1).Cells(xRg.Rows.Count + 1, 1)

        End If

        Sheets(I).Activate

        ActiveSheet.UsedRange.Copy xRg

    Next

End Sub

VBA Code in Excel

To run this Macro, press Alt + F8 on your keyboard.

A dialogue box called Macro will appear. Select this Macro (Combine) and click on Run.

Macros Dialogue Box in Excel

You will find the data from the three worksheets arranged vertically in a new worksheet called “Combined”.

Pull Data from Multiple Worksheets Using a Macro

This site helped us understand and develop the code.

4. Using Power Query

This is our final task today.

Look at the workbook below.

Here we have a workbook with three worksheets each having a table of sales records of some items, of the months January, February and March respectively.

Sheet1 with a Table

Sheet2 with a Table

Sheet3 with Table

 

Our objective is to collect data from these worksheets and merge them into a single table.

We will accomplish this using the Power Query of Excel.

[Power Query is available from Excel 2016. If you use any older version, you have to download and install it manually.]
  1. Go to Data>Get Data tool under the Get & Transform Data section from any worksheet.

Get Data Tool in Excel Toolbar

  1. Click on the drop-down menu. From the available options, choose From Other Sources > Blank Query.

Choosing Blank Query

  1. Click on Blank Query. The Power Query Editor will open. In the Formula bar, write this formula:
=Excel.CurrentWorkbook()
[Power Query is case-sensitive. So write the formula as it is.]

Writing Formula in the Power Query Editor

  1. Click on Enter. You will find the three tables from the three worksheets arranged one by one. Select the ones that you want to pull.

For the sake of this example, select all three.

Then click the small right arrow beside the title Content.

Power Query Editor

  1. You will get a small box. Click on Expand and then check (put a tick on) all the boxes.

Power Query Dialogue Box

  1. Then click OK. You will find all the items from three tables brought to a single table in Power Query Editor.

Table in the Power Query Editor

  1. Then go to File > Close and Load To… option in the Power Query Editor.

Loading Power Query Table in Excel Worksheet

  1. You will get the Import Data dialogue box. Choose Table.

Then if you want the combined table to be in a new worksheet, choose New Worksheet.

Otherwise, choose Existing Worksheet and enter the cell reference of the range where you want the table.

Import Data Dialogue Box

  1. Then click OK. You will find the data from the three worksheets arranged in a single table in a new worksheet named Query.

 

Data Pulled from Multiple Worksheets Using Power Query

Conclusion

Using these methods, you can pull data from multiple worksheets to a single worksheet in Excel. Do you have any questions? Feel free to ask us in the cement section.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo