How to combine multiple sheets into a pivot table in Excel?
There are two ways to go about this: 1) Insert a normal area chart (not a pivot chart) and then select the two ranges. 2) The better solution is to combine both the pivot tables by combining the raw data. In this scenario you would be able to use a pivot chart. Hope this helps. If you are using Excel 2010 or 2013, there is a w feature – Slicer may help you quickly filter multiple pivot tables at once, please do as follows: 1. Create your used pivot tables based on the same data source as following screenshot shown: 2. To combine the data from all the ranges and create a consolidation that does not have page fields, do the following: Add the PivotTable and PivotChart Wizard to the Quick Access Toolbar. To do that: Click the arrow next to the toolbar. Click the arrow next to the toolbar and then click More. I have 2 pivot tables and 2 charts that go with this. However I want to merge them into only 1 chart. I have no intention of combining the raw data together because we are resetting the data from both tables weekly. So what we want is the 2 tables and 1 chart consisting of the 2 tables. Here is my example (sorry its dutch). Combine multiple sheets into a pivot table 1. Click Customize Quick Access Toolbar More Commands as below screenshot shown. In the Excel Options dialog box, you need to: 2.1 Select All Commands from the Choose commands from drop-down list; 2. Then the PivotTable and PivotChart Wizard button.
Supposing you have multiple worksheets with identical column structures as below screenshot. Now you need to create a pivot table from the specified data of these worksheets, how can you do to achieve it? This article will show you method to get it done in details.
Combine multiple sheets into a pivot table
Please do as follows to combine multiple worksheets’ data into a pivot table.
1. Click Customize Quick Access Toolbar > More Commands as below screenshot shown.
2. In the Excel Options dialog box, you need to:
2.1 Select All Commands from the Choose commands from drop-down list;
2.2 Select PivotTable and PivotChart Wizard in the command list box;
2.3 Click the Add button;
2.4 Click the OK button. See screenshot:
3. Then the PivotTable and PivotChart Wizard button is shown on the Quick Access Toolbar. Click the button to open the PivotTable and PivotChart Wizard. In the wizard, select Multiple consolidation ranges option and the PivotTable option, and then click the Next button. See screenshot:
Pivot Table Merge Rows
4. In the second wizard, select I will create the page fields option and click the Next button.
5. In the third wizard, click the button to select the data from the first worksheet you will combine to the pivot table, and click the Add button. Then repeat this step to add other worksheets data into the All ranges box. Select the 0 option in the How many page fields do you want section, and then click the Next button.
Note: You can select 1, 2 or other options in the How many page fields do you want section as you need. And enter a different name in the Field box for each range.
6. In the last wizard, select where you want to put the pivot table (here I select New worksheet option), and then click the Finish button.
Then a Pivot table with specified worksheets’ data is created. You can arrange it in the PivotTable Fields as you need.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
or post as a guest, but your post won't be published automatically.
You’ve probably come across data that looks something like this before. It’s usually created by someone who doesn’t know what data should look like and thinks that the data should live in a summarized format because that’s how they want to view it.
In this example, the sales amounts are scattered over 4 different ranges when they should all be in one column with a descriptive column heading like Sales Amount. The year should feature as a piece of data too in its own column. Salesperson and product should also be in their own column.
If this data was in a proper tabular format, it would be easy to summarize it in a similar manner with pivot tables and pivot charts. As is is now, it’s really hard to do anything with it.
This type of data can be a pain to convert to a proper data set. I’ve already shown you how to convert this type of data into a proper data set by using the unpivot feature found in power query.
In this post, I’m going to show you how to combine these tables and unpivot them using the pivot table wizard. Essentially we will use a pivot table to unpivot the data.
PivotTable And PivotChart Wizard
I very briefly touched on the PivotTable And PivotChart Wizard in my 101 pivot table tips post where I mentioned the keyboard shortcut Alt + D + P to open the wizard.
This is a keyboard shortcut from an older version of Excel and unless you know about it, there’s no place to find it in the ribbon.
You can also access this by adding a command to the quick access toolbar for the wizard. It can be found under the Commands Not in the Ribbon section and it’s labeled PivotTable and PivotChart Wizard.
Consolidate and Unpivot Multiple Ranges with the PivotTable Wizard
Let’s get started and combine the data. Open the PivotTable and PivotChart Wizard using the Alt + D + P keyboard shortcut, then choose Multiple consolidation ranges then press the Next button.
In the next step of the wizard, choose the Create a single page field for me then press the Next button.
Now select the ranges you want to consolidate.
- Use the select range icon to select a range. We select the whole range including years and products in our example.
- Press the Add button and then repeat step one and two until all the ranges have been added.
- Press the Next button when finished adding all the ranges.
Select the location for your new pivot table, either a new worksheet or somewhere in an existing worksheet. Then press the Finish button. You will now have a blank pivot table.
Build Your Pivot Table
With the new blank pivot table, we need to set it up to build out a new tabular set of data. Move the Page1, Row and Column field into the Rows area of the pivot table. Bring the Value field into the Values area of the pivot table.
Format Your Pivot Table Into a Tabular Data Set
Now we will need to change the format of the pivot table to create our tabular data set. Select the pivot table and go to the Design tab then select the Report Layout command and choose the Show in Tabular Form option from the menu.
We also want to turn off any subtotals the report may have. Go to the Design tab then select the Subtotals command and choose the Do Not Show Subtotals option from the menu.
Next we want to repeat all labels just like we’d have in a normal data set. Go to the Design tab then select the Report Layout command and choose the Repeat All Label Items option from the menu.
Now we just need to change a couple of the column headings by typing over them.
Mail Merge Pivot Table
- Page1 – This was our 4 ranges, and in our case represented 4 different sales people. We can rename this Sales Person.
- Row – These were the row headings for our data and in our case it was the product. We can rename this to Product.
- Column – These were the column headings for the numerical values in our data these were the years. We can rename this to Years.
- Value – These were the numerical values in our 4 ranges and in our case they were the sales amounts. We can rename this to Sales.
Now we have a proper data set which has combined all the data from our 4 ranges.
Consolidate Multiple Sheets with the PivotTable Wizard
Even if your data is in a proper unpivoted tabular format, it might be broken up into multiple ranges across different sheets. Maybe your sales data for each salesperson is on a different sheet or each month of data is in its own sheet.
We can use the pivot table wizard to combine the data into one pivot table. It’s the same process, just combining data from different sheets!
Excel has a lot of functionality, and if something seems like a pain and a lot of effort to do manually there is usually a better way.
Power Query can easily consolidate and unpivot data. But not everyone will be using the most recent version of Excel which has power query out of the box or is able to install the add-in because they don’t have the admin rights from IT.
Merging 2 Pivot Tables
It’s good to know there are older methods, even if they’re a bit hidden.