« What is the advantage of buying the collectors addition of Call of Duty Modern Warfare 2?
How is algebra used in the healthcare field? »

Excel: Is it possible to create a pivot table using source data from more than one worksheet? Excel 2007?

Excel: Is it possible to create a pivot table using source data from more than one worksheet at once?
I cannot find the Wizard on Excel 2007- Was that removed?

Yes you can create pivot using multiple Worksheets. But this function is not as flexible as you create pivot table using single sheet.
Sturcture of all the tables must remain same to take the advantage of this feature.
use keyboard short keys to aproach if you are using 2007 version otherwise the multiple table merging option may not be visile to you.

Press Alt + D followed by P

Select third option "Multiple Consolidation Ranges"
Click Next
Select "Select a Single Page Field for me"
Click Next

Now Select First range and Click on ADD
Add
and Select & Add as many ranges you want to consolidate.

Click Next/Finish

Now a Pivot is created using default fields.
Remeber Only First column values will be treated as row label
and First Column Values will be treated as Columns.

you can drag Column values before and after only column values and can change the way of calculation for the column labels.

As I have already stated you that this is not much flexible function but still very useful. you can experience it yourself while using it.

http://xlxpart.blogspot.com

This entry was posted on Friday, December 11th, 2009 at 2:24 pm and is filed under worksheet. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Excel: Is it possible to create a pivot table using source data from more than one worksheet? Excel 2007?”

  1. Risk-averse Says:
    December 11th, 2009 at 7:36 pm

    MS, in its infinite wisdom of what users REALLY need and want, decided to eliminate the wizard.

    I think you can use a "collector" worksheet to pull the data from the various sheets into one so the Pivot table will work.
    References :

  2. Ajax Says:
    December 11th, 2009 at 7:47 pm

    Yes you can create pivot using multiple Worksheets. But this function is not as flexible as you create pivot table using single sheet.
    Sturcture of all the tables must remain same to take the advantage of this feature.
    use keyboard short keys to aproach if you are using 2007 version otherwise the multiple table merging option may not be visile to you.

    Press Alt + D followed by P

    Select third option "Multiple Consolidation Ranges"
    Click Next
    Select "Select a Single Page Field for me"
    Click Next

    Now Select First range and Click on ADD
    Add
    and Select & Add as many ranges you want to consolidate.

    Click Next/Finish

    Now a Pivot is created using default fields.
    Remeber Only First column values will be treated as row label
    and First Column Values will be treated as Columns.

    you can drag Column values before and after only column values and can change the way of calculation for the column labels.

    As I have already stated you that this is not much flexible function but still very useful. you can experience it yourself while using it.

    http://xlxpart.blogspot.com
    References :
    http://xlxpart.blogspot.com

Leave a Reply