Pivot Table From Data on Multiple Sheets

A frequent question about pivot tables is how to create one from data on different worksheets, or even in different file. Last week, I updated my page on this topic, which shows several ways you can accomplish this goal.

• Multiple Consolidation Ranges
• Microsoft Query
• Power Query

Multiple Consolidation Ranges

You can still use the old Pivot Table Wizard, to build a pivot table based on Multiple Consolidation Ranges, but it doesn’t give great results.

The resulting pivot table only has one row field, and all the other fields are treated as values.

This video, below the video timeline, shows the steps for creating a pivot table from multiple consolidation ranges.

Video Timeline

• 0:00 Data on 2 Sheets
• 0:24 Open PivotTable Wizard
• 0:50 Select Sheet Ranges
• 1:08 Page Field Settings
• 1:29 Adjust the Pivot Table
• 2:04 Show Sum
• 2:15 Page Field

Microsoft Query

Another option is to use Microsoft Query, and combine the data using a Union query. It’s a bit clunky and complicated, but you can download a sample workbook provided by Excel MVPs, Kirill Lapin (KL)  and Héctor Miguel Orozco Diaz. It has a macro to automate the process.

Power Query

The good news is that if you have a version of Excel 2010 or 2013 that supports the Power BI tools, you can use Power Query to combine the data from different sheets or files. Power Query is an add-in that you can download from the Microsoft website.

After you combine the tables, create a pivot table from the combined data. You can even combine tables in which all the columns aren’t identical, like the two tables shown below. The steps are shown in the video below.

Video: Power Query to Combine Tables

To see how Power Query combines two tables, you can watch this short video.

Learn More About Power Query

Expand your Excel skills — learn Power Query at your own pace, in this online course from experts Ken Puls and Miguel Escobar. The course starts with the Power Query basics, and gradually builds up to advanced techniques. There are 15 modules, broken into 55 videos (over 12 hours of content). See all the course details and sign up now.

______________