TRENDING »
Loading...

Easily Create Pivot Table From Multiple Worksheets

Did you have a problem where you have data spread into different sheets of a workbook and wanted to create a pivot table from multiple worksheets?

Excel Pivot Table Multiple Sheets

Image credit: depositphotos.com

The simplest way to create this is by appending the data from each of the sheets and pasting it one below the other and then creating a pivot table. But what if the data spans across more than 1 million rows in each sheet. It would be impossible to bring it into a single sheet. Hence we can use this technique to consolidate multiple worksheets into one PivotTable

Here we will solve this problem by keeping all the data in the sheets intact. Just by formatting and using a SQL query, we will create a Pivot Table from multiple worksheets.

I have provided the link to the workbook at the end of this article which you can download and use to understand this process in easy steps as we go along to make a pivot table across multiple sheets.

In the file, I have three sheets. The data is month-wise sample sales data. 

Easily Create Pivot Table From Multiple Worksheets

Now if we want to create another summary sheet with Pivot Table with the source of three sheets we have to perform few steps as mentioned below.

1. Select the table in the Jan worksheet and in the name box type a name for the range. For example, here I will type - 'Jan_Data'.

Easily Create Pivot Table From Multiple Worksheets


Easily Create Pivot Table From Multiple Worksheets

2. Next click a cell in the Table and Press 'Ctrl + T' to convert the range to a Table. This will help if we add more than data in the range it would be automatically included in the named range.

Easily Create Pivot Table From Multiple Worksheets

3. Do steps 1 and 2 to other month sheets with names - 'Feb_Data' and 'Mar_Data'.

4. Insert a new blank worksheet and rename it - 'Summary'.

5. Save the workbook.

6. Click on Data in the ribbon and then click on the menu named 'Get Data'. Then navigate to 'From Other Sources' and finally click on 'From Microsoft Query'.

Easily Create Pivot Table From Multiple Worksheets



7. You will see a window that asks you to select a Data Sources. Click on Excel Files and click on OK.

Easily Create Pivot Table From Multiple Worksheets

8. In a dialog, you will have to navigate where the current workbook is saved and select it. Then click OK.

9. In the next window, move all the fields to right by selecting each field group and clicking the button highlighted in the below screenshot. Do this for all three tables - Jan, Feb, and Mar.

Easily Create Pivot Table From Multiple Worksheets

10. Click Next.

11. When the below message appears, click OK.

Easily Create Pivot Table From Multiple Worksheets


12. In the Microsoft Query window, Click SQL Button.

Easily Create Pivot Table From Multiple Worksheets

13. In the SQL window, delete all the text and type as given below. Replace table names if you had given different names in step 1.


Easily Create Pivot Table From Multiple Worksheets



14. Click OK to continue.

Easily Create Pivot Table From Multiple Worksheets

15. In Microsoft Query Window, click on File Menu and then click on 'Return Data to Microsoft Excel'

Easily Create Pivot Table From Multiple Worksheets

16. In the Import Data dialog, Click on Pivot Table Report, Select the place where you want the Pivot table to be inserted, and click OK.

Easily Create Pivot Table From Multiple Worksheets

17. You would see the pivot table inserted as specified.

Easily Create Pivot Table From Multiple Worksheets


18. Change or add the fields as you would normally do with the regular pivot table.

Easily Create Pivot Table From Multiple Worksheets


Using this technique it is very easy to create a pivot table from multiple sheets.

Hope you liked this tip, please consider subscribing to our blog for more interesting articles.



Try the quiz:




Share with your friends

Add your opinion
Notification
You can subscribe to our blog or follow us on social media to get notified about the new posts.
Done
Title
Text Content
Close
Cookies used on the website!
We and our partners use cookies to personalize content and ads, to provide social media features and to analyze our traffic.
You consent to use of these cookies if you continue to use this website.
Learn more »