Easily Create Pivot Table From Multiple Worksheets

Success Trending    March 13, 2021    Productivity , Tech Skills   

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?

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. 

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

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.

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

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

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.

10. Click Next.

11. When the below message appears, click OK.

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

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.

SELECT * FROM Jan_Data UNION ALL SELECT * FROM Feb_Data UNION ALL SELECT * FROM Mar_Data

14. Click OK to continue.

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

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.

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

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

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: