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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Pull Data from Multiple Worksheets in Excel: 4 Quick Ways

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

Sample Worksheets to Pull Data in Excel

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


1. Use Formula to Pull Data from Multiple Worksheets

If you want to perform any operation on the data from multiple sheets, you can perform this through formulas. Here is how to do that.

Steps:

  • 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!D5+February!D5+March!D5
  • Then drag the Fill Handle to copy the formula to the rest of the cells.

Use Formula to Pull Data from Multiple Worksheets

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

Formula Explanation:

  • Here January!D5 indicates the cell reference D5 of the sheet name “January”. If you have the sheet name as Sheet1, use Sheet1!D5 instead.
  • Similarly February!D5 and March!D5 indicate the cell reference D5 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.

Using a 3D Reference Formula:

You can also do this by using a formula with a 3D reference. The formula is as follows to create a 3D reference in Excel.

=SUM(January:March!D5)

Use 3D Formula to Pull Data from Multiple Worksheets

Read More: How to Pull Data from Multiple Worksheets in Excel VBA


2. Pulling Data from Multiple Worksheets by Using Consolidate Feature

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

Steps:

  • Create a blank dataset with the product names and add a column named Total Sales. Keep the cells under this column blank.

create a new set of data

  • Now, C5:C19 range of cells in any worksheet and go to the Data > Consolidate tool under the Data Tools section.

select data > go to Data tab > Consolidate tool

  • 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.
  • Now, click on the Import icon right to the Reference box.

Pulling Data from Multiple Worksheets by Using Consolidate Feature

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

Pulling Data from Multiple Worksheets by Using Consolidate Feature

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

  • You will find the references of the selected range inserted in the Add references box.
  • 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 D5:D19 from the worksheet February and D5:D19 from the worksheet March.

Pulling Data from Multiple Worksheets by Using Consolidate Feature

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


3. Using Macros to Pull Data from Multiple Worksheets

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 have a new workbook with three worksheets, each having the sales record of four weeks of the months of January, February, and March respectively.

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

The VBA code is as follows.

Sub PullDatafromMultipleSheets()

    Dim Q As Long
    Dim aRng As Range

    On Error Resume Next

    Worksheets.Add Sheets(1)

    ActiveSheet.Name = "VBA"

    For Q = 7 To Sheets.Count
        Set aRng = Sheets(1).UsedRange

        If Q > 7 Then
            Set aRng = Sheets(1).Cells(aRng.Rows.Count + 1, 1)
        End If

        Sheets(Q).Activate
        ActiveSheet.UsedRange.Copy aRng
    Next

End Sub

Now, follow the steps below to apply this code.

Steps:

  • First, press Alt+F11 and go to the VBA editor.
  • Now, go to the Insert tab and click on Module. A new module will be opened.

Inserting a new module in the VBA editor

  • Now, copy the code and paste it here.

Macros to Pull Data from Multiple Worksheets

  • Now, save the Excel file by pressing Ctrl+S.
  • So you will face the following window first.

saving Excel file as XLSM

  • Click on No and save the file as a Macro-Enabled file.

  • Now, click on the Run button/press F5 or press Alt+F8.
  • A dialogue box called Macro will appear. Select this Macro (PullDatafromMultipleSheets) and click on Run.

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

Read More: Extract Data from One Sheet to Another Using VBA in Excel


4. Using Power Query to Pull Data from Multiple Worksheets

This is our final task today. Again we are back to our initial sheets to show this method. 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.

Steps:

  • First of all, we have to convert our data in each sheet into tables. Select any cell inside the data and press Ctrl+T. Then press OK.

creating a table

  • Now, go to Data > Get Data tool under the Get & Transform Data section from any worksheet.
  • Click on the drop-down menu. From the available options, choose From Other Sources > Blank Query.

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

Applying Excel.CurrentWorkbook function in power query

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

Using Power Query to Pull Data from Multiple Worksheets

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

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

Using Power Query to Pull Data from Multiple Worksheets

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

close power query and load data

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

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

Read More: How to Pull Data From Another Sheet Based on Criteria in Excel


Download Practice Workbook


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


Related Articles

<< Go Back To Extract Data Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

2 Comments
  1. Hello! I’ve used this guide successfully to create a big table (sheet 1) with the tables i have in several sheets (sheet 2 to 20+) of the same workbook, the point is i’m constantly creating new sheets and tables (one per day of the month), how can i add the info of those to the big table without creating a mess?

    I’ve tried a couple of things but Power Query always counts the sheet of the new big table (sheet 1) as part of the info that needs to be considered and i don’t want that, if there’s no way to fix it i guess i always can put the big table in a different workbook.

    • Reply Mahfuza Anika Era
      Mahfuza Anika Era Feb 27, 2024 at 1:29 PM

      Hello ANGEL,

      Thank you for your query. Counting the sheet of the new big table (sheet1) can be ignored. Suppose, you have added a new table named “April” and you want to include it in the big table. Unfortunately, the mother table cannot be updated automatically. What you can do is to create another query after inserting a new sheet and table.

      After adding a new sheet and table, follow the same process to pull data from different sheets using power query. But the problem is the previous query will be included in your new query. To remove that:

      1.Click on the dropdown icon.

      2.Uncheck Query1 >> Press OK.

      The new query is now removed.

      3. Click on the following icon >> Expand >> OK.

      The tables are expanded now. You can remove the marked column as it is not necessary.

      4.Now, close and load the table in a new worksheet. So, the previous mother sheet (sheet 1) will not be included now.

      You have to do this process every time you add a new sheet and table.

      Regards
      Mahfuza Anika Era
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo