Multiple worksheets in a PivotTable – Part 1

At the request of Gaëtan Eechaudt, I will show you how you can process multiple worksheets in a PivotTable.

When you work with a PivotTable, it is always best when all the data you want to use in the PivotTable, is in one worksheet.
But, this might not always be possible, as in Gaëtan’s case.
For this, we use the PivotTable Wizard.
We also need the “Multiple consolidation ranges” option.

Those who work with the 2003 version, or previous versions, have no problem as the Wizard is started automatically while creating a PivotTable.

But those who work with Excel 2007 or 2010, must use the “PivotTable and PivotChart Wizard” as shown above.
This is not standard, so we have to add this to the “Quick Access” toolbar.
I suppose everyone knows how to add a button but, I will just quickly go over this procedure in the 2010 version.
Those who work with the 2007 version and don’t know how to do this, read Lesson 4 of Excel 2007 course.

This is for the 2010 users.
Click the “File” (1) tab in the “Ribbon”.
This brings us to the “Backstage” view.
Click “Options” (2).

Select “Quick Access Toolbar” (1).
Select “Commands Not in the Ribbon” (2).
Select the “PivotTable Wizard and Pivot Chart” (3) command, and click the “Add” button (4).
Then click the OK button.

The “PivotTable Wizard and Pivot Chart” is added to the “Quick access” toolbar.
As you can see in the example below, the workbook consists of four worksheets. That is one for each province.

What we are going to do is, create a PivotTable with the data from all the worksheets.
The only requirement is that all worksheets have the same structure.

Click the “PivotTable Wizard and Pivot Chart” button in the “Quick Access” (1) toolbar.
This opens the window of the Wizard.
Select “Multiple consolidation ranges” (2).
Then click Next (3).

In step 2a of the wizard select “I will create the page fields.”
This allows us to create the fields themselves, which we will be able to filter, later.
Click the Next button.

In step 2b of the wizard, select the range (1).
This is then listed in the “range” of the Wizard (2).
Click the “Add” button (3).

Now we add the second range.
Select the second worksheet, which is “Limburg” (1).
Select the range (2).
Then click the “Add” button (3).

Now, we do the same thing again, for the following worksheet.
So select the worksheet (1).
Select the Range (2).
Then click the “Next” button (3).

Now coming to the last worksheet, “West Flanders”.
Do the same thing again.
So, select the worksheet (1).
Select the Range (2).
Then click the “Next button (3).

The different ranges of the various worksheets are added.

We will continue in Part 2

FYI: Swotster.com thinks of its students, so you also remember swotster.com

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats