1. Excel Combine Two Pivot Tables
  2. Combine Two Pivot Tables Into One
  3. Excel Combine Multiple Spreadsheets Into One

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

  • Creating a Pivot Table with Multiple Sheets Alt + D is the access key for MS Excel and after that, by pressing P after that we’ll enter to the Pivot table and Pivot Chart Wizard. Now we can see the Pivot table and Pivot Chart Wizard – Step 1 of 3 as shown below. Here wizard will ask you two questions we need to answer the same as follows.
  • The above steps would combine the data from all the worksheets into one single table. If you look closely, you’ll find the last column (rightmost) has the name of the Excel tables (EastData, WestData, NorthData, and SouthData).
  • The second sheet is projects activities where for each one row in sheet one you can have 100s in sheet two depending on the project activities. So my pivot will take for example the project amount and deduct the daily amount from it. (This one pivot table saves me the hassle of creating a new consolidated sheet every time i need a report.

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.

File

Setting up the Data

Excel Combine Multiple Sheets Into One Pivot Table
  • 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

Table

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

Table

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.

Excel Combine Two Pivot Tables

Select data from both the sheets and create one Page Field for each sheet. Label the Page field appropriately.

Combine Two Pivot Tables Into One

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 -

  • Rename the Page Field to something more appropriate to reflect the data in the Pivot table, for example we are consolidating data across Stores, so rename it to Stores
  • Filter the Column Labels, to only show the Sales data
  • Modify the Values column to calculate the sum, instead of count and select the appropriate number format.
    • Click on any field in the Pivot table, the Pivot Table Field list will appear
    • Go to the “∑ Values” column, click on the field that says “Count of Value”
    • Select “Value Field Settings”
    • In the “Summarize by” field, select Sum. You can also set the appropriate number format by clicking on the “Number Format” button
  • Modify the Pivot Table Options to calculate the grand total only across columns.

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.

Excel Combine Multiple Spreadsheets Into One

Hope you find the information presented here useful. Feel free to share your feedback or suggestions in the comments section below.

Coments are closed

Most Viewed Posts

  • Openssl_libraries
  • Prime Video Search
  • Free Blank Invoice
  • Fall Guys Discord
  • New Movies On Prime 2021

Scroll to top