Sabrina has attended:
Excel Advanced course
Pivot Table
Hello,
I have a quick question on Consolidated Pivot Table over different worksheets in one workbook. Could I use the same Pivot Table for different workbooks ? I copied and pasted the pivot table in the new workbook but the ranges used refer to the copied workbook rather than the workbook in use so not really helpful. Ideally I would like one report picking up the data from different workbooks (same cells selection) rather than creating one pivot table per workbook. Do you know a way to do this ? Thank you.
Regards,
Sabrina
RE: Pivot Table
Hello Sabrina,
Thank you for your question. Yes, there is a way to create a consolidated pivot table. You can use the Pivot Table and Pivot Chart Wizard. Ensure that the layouts of all your data sheets are identical and rectangular in shape, in other words no empty or half-filled rows or columns inside. To get the wizard, click File - Options - Customize Ribbon. Click the New Tab button to add a new tab to your ribbon. Choose commands from - All commands. Scroll down in the list and click Pivot Table and Pivot Chart Wizard. Click Add to add it to your new tab. Before you start using the wizard, open all the workbooks you wish to use.
Select the new tab in your ribbon and click Pivot Table and Pivot Chart Wizard. In the dialogue box, select "Multiple Consolidation Ranges". Click Next. Select "Create a single page field for me". Click Next. Now, one at a time, select and add all the corresponding data ranges from all your workbooks. Once all the ranges are added, click Finish. This should then create your consolidated pivot table.
Good luck!
Marius Barnard
Excel Trainer
