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.

multiconsol03

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

multiconsol05b

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.

PivotMulti02

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.

powerquerycombine01

Video: Power Query to Combine Tables

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

Free Webinars

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.

______________

This entry was posted in Excel Pivot Table. Bookmark the permalink.

One Response to Pivot Table From Data on Multiple Sheets

  1. Steven O'Toole says:

    HI There,
    I have asked a question which I thought this tutorial would answer here:

    http://www.mrexcel.com/forum/excel-questions/831954-corporate-scorecard-pivot-table-design-question.html#post4057746

    Could you see if you may be able to weigh in on how I could accomplish this? I’m working with Excel 2007

    Thanks,
    Steven

Leave a Reply

Your email address will not be published. Required fields are marked *