How to Create an Excel Pivot Table from Multiple Sheets

If Excel data is on different sheets, you can create a pivot table using multiple consolidation ranges. It’s better if the data is all on one sheet, but if you don’t have that option, multiple consolidation ranges will pull all the data into one pivot table.

In Excel 2003 you can open the PivotTable and PivotChart wizard by choosing Data | PivotTable and PivotChart Report. There’s no equivalent command on the Ribbon in Excel 2007, but you can press Alt+D, then type P to open the wizard.

The pivot table from multiple sheets isn’t as flexible as a regular pivot table — all the data fields use the same summary function, and there’s only one row field. However, while you’re setting up the pivot table you can create one or more page fields, and create labels for the data ranges.

Read the Instructions

There are written instructions, and details on the pivot table limitations, on the Contextures website: Excel Pivot Table Tutorial — Multiple Consolidation Ranges


Watch the Video

This video shows you the steps in Excel 2007, to create the pivot table from multiple sheets, and set up a page field.

____________

You may also like...

14 Responses

  1. […] You can check it out here. […]

  2. abdul Nisar says:

    thanx for good knowlege ………very good option ….

    Regards
    Abdul

  3. Zoommii says:

    That is very good, it was impossbible to do by myself since excel 2007 using.
    Thanks again for that.

  4. Corporate Geek says:

    Hi,

    Thanx for the video tutorial. Helped me alot. My Question is, is is possible to work on the same kind of scenerio as shown in the video if my workbook is being SHARED among three people on a network? Can We all log on to the file and update the East, West and possibly Central sheet at the same time and then unshare it so that I am able to update the information since pivot functionality is not there in a shared excel file?

    Thanx again for your help.

  5. sopheah says:

    i was asking if some one can tell me how i can tally election results in excel,given that i have to import those results from somewhere and tabulate them,for instance figures from different polling stations being tabulated together with other polling stations within the same district and then all the districts added together nationally.

  6. Vera Kuchkova says:

    Very usefull and very good explained.

  7. Chirag says:

    Very Useful and well explained.

    Thanks a ton!!

  8. Ijisa says:

    Good information, but my Excel 2007 does have a pivot table choice on the ribbon and I am unable to do what was shown in the video, i.e., consolidate data from multiple sheets. alt+D, P don’t bring up anything.

  9. Ijisa says:

    Never mind, I’m a clutz. I got it. Cheers.

    • Marco says:

      Tried all your suggestions but I’m still having problems to get to PivotChart Wizard in Excel 2007. There is no Pivot Table Wizard in Options. Keyboard shortcut — Alt + D, P don’t bring up anything.
      Could anyone help me?

  10. Marco says:

    Took me some time to discover that you can press Alt+D, then P for the English version. You must type A (not P) in the Portuguese version

  11. Bijendra Singh says:

    excellent piece to knowledge…:)
    It will help me to play with huge data where I have more than 25 lacs rows in three different sheet…..Great.

  12. George says:

    The modification I’m looking for would use a pivot column filter. I have 6+ pivots all with the same values in each pivot tables column filter. There are no other column based filters. (have a couple of Row filters)

    What I had hoped was being able to replace pivotitems with pivotcolumns but it doesn’t appear that function exists.

    Is there another approach?

  13. Miriam says:

    This was the best tutorial I ‘ve had on this subject!

Leave a Reply

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