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.
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.
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.
There are a couple of upcoming free webinars that might be of interest to you. Click the links below, to see the details.
- Excel Dashboard Webinar: Today, Jan. 21, 2015 – Mynda Treacy, from My Online Training Hub, is offering a free one-hour webinar on How to Build Excel Dashboards. There are 3 different time slots for today’s webinar – 10 AM, 1 PM, and 8 PM (Eastern Time zone).
- PASS BA Marathon: Feb. 3, 2015 – Sign up now, for this free online preview of the upcoming Business Analytics Conference. There will be 6 live webinars, including “Productivity Revolution in Excel”, led by Chandoo (Purna Duggirala) and Avi Singh.