We can use the Power Table Wizard in Excel to create a pivot table from multiple worksheets. Here, we will use multiple consolidation ranges as the source of our Pivot Table. The steps below will walk through the process of creating a Pivot Table from Multiple Worksheets.
Figure 1: How to Create a Pivot Table from Multiple Worksheets
Consolidate Multiple Data Sources in a Pivot Table. Ashish's answer is good, but is a bit Excel for Windows-centric. Let's go through Ashis's answer and get it to work for us on the Mac. The problem to solve is that we want to use data from multiple worksheets to produce a pivottable. Ashish is making the assuming these conditions are met.
Figure 2.1: Setting up the Data
Figure 2.2: Setting up the Data
Figure 3: Pivot Table and Pivot Table Wizard Step 1
Figure 3.1: Pivot Table and Pivot Table Wizard Step 2a
Figure 3.2: Pivot Table and Pivot Table Wizard Step 2b
Figure 3.3: Pivot Table and Pivot Table Wizard Step 2b
Figure 3.4: Pivot Table and Pivot Table Wizard Step 2b
Figure 4: Pivot Table and Pivot Table Wizard Step 3
Figure 5: Created Pivot Table with Pivot Table fields
Figure 6: Creating a single data list
Figure 7: Single data list
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
To create a Pivot table from Multiple Sheets in Excel, you can use the data consolidation feature in Excel. It is important that these sheets have the data setup in the same manner i.e. they have the same column headings – e.g. sales data from different regions, or inventory data across stores.
Consider the following data from a store showing the quarterly sales of different products across two stores. We now want to create a report that shows the sales across each store by the category. We can achieve this by creating a Pivot Table for data across these multiple sheets.
Launch the Pivot Table and Chart Wizard using the keyboard shortcut – Alt+D, P and select ‘Multiple Consolidation Ranges’
Select -> I will create the Page Fields option and press Next
Now select the range for the data on which you wish to create a Pivot table – select the column headings as well.
Select data from both the sheets and create one Page Field for each sheet. Label the Page field appropriately.
Select to create the Pivot table in a new Worksheet and click on Finish. If you wish to create the pivot table in same sheet, input the desired cell information from where the pivot table should start.
Next modify the fields in the newly created Pivot table -
To see data only, across a particular store, filter on the Stores Field, for example to see only data across Store 1
However, there are limitations to such a pivot table and it is best that you consolidate the data into a single work sheet. You can then create a Pivot table for that sheet and create reports across any/all fields.
Note: These steps work for Excel 2003 as well. However, the screenshots shown here are for Excel 2007.
Hope you find the information presented here useful. Feel free to share your feedback or suggestions in the comments section below.