Go Back

How to Create a Pivot Table from Multiple Worksheets

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

Setting up the Data

  • We will use the Data in figure 2.1 and figure 2.2 to create a Pivot Table from multiple worksheets.
  • The second sheet (Vegas Sales) contains identity data in terms of Column labels. However, the Quantity and sales amount are different as shown in figure 2.2

Figure 2.1: Setting up the Data

Figure 2.2: Setting up the Data

Creating a Pivot Table

  • We will click on Los Angeles worksheet
  • We will launch office access key by pressing ALT+D. We will then press P to activate the Pivot table Wizard

Figure 3: Pivot Table and Pivot Table Wizard Step 1

  • We will check the sections as shown in figure 3 and click Next
  • The page fields as checked in figure 3.1 are the filters that will be on the Pivot table

Figure 3.1: Pivot Table and Pivot Table Wizard Step 2a

  • We will select I will create the Page fields in figure 3.1
  • We will click Next
  • We will select the range (Cell B3 to Cell D19) without Column A cells. Cells in column A will serve as our page identifier
  • We will click ADD after selecting the range

Figure 3.2: Pivot Table and Pivot Table Wizard Step 2b

  • We will go to VEGAS worksheet, select the range (Cell B3 to Cell D19), and click on ADD

Figure 3.3: Pivot Table and Pivot Table Wizard Step 2b

  • We will select 1 as our page fields
  • We will click on the range of Los Angeles and enter Los Angeles into field one as shown in figure 3.4. We will do a similar thing for Vegas

Figure 3.4: Pivot Table and Pivot Table Wizard Step 2b

  • We will click on Next. We will select New worksheet and click on Finish

Figure 4: Pivot Table and Pivot Table Wizard Step 3

Figure 5: Created Pivot Table with Pivot Table fields

  • If we want just a large list that contains both data, we will click on Page1, Row, and Column in the Pivot Tables field and drag them out. This leaves us with Product of Values

Figure 6: Creating a single data list

  • We will double click on Cell A4. This will create a new worksheet with all the data

Figure 7: Single data list

Note

  • By clicking on the filter option of Los Angeles as seen in Cell B1, we can change it to Vegas to see the details for Vegas in figure 5
  • The Pivot table automatically returns the Sum of the Values. We have changed this to the product by right-clicking on Cell B3 and used the value field option
  • Double clicking on the dates will drop-down the Quantity and Sales Amount

Instant Connection to an Expert through our Excelchat Service

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar