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