Excel Combine 2 Pivot Tables

  1. Excel Merge 2 Pivot Tables
  2. Combine 2 Pivot Tables Excel 365
  3. Excel Combining Pivot Tables
  4. Combine Two Pivot Tables
  5. Excel Combine 2 Pivot Tables Two

To create the second PivotTable, do the following: Select the first PivotTable by clicking anywhere inside it and then clicking Select in the Actions group (on the contextual Analyze tab).

One of the key features of Pivot Tables is to summarize the information quickly. Another great advantage is its ability to look at percentages – percent of total, percent of grand total, percent of row total, running total in etc.

  1. If your data is in different workbooks or worksheets, you have two ways to get a pivot table from it, first one gets all the data in a single sheet by copy-paste and then make a pivot table from it, another one is to use this feature of MS Excel wizard to make a pivot table from multiple sheets.
  2. 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.

Yet another fantastic feature is the ability to group data in Excel – either by existing columns, or by creating your own custom logic. Data can be grouped by Text Columns or even Date Columns. And the result is instantly summarized. Amazing stuff!

All is good so far… The problem crops up when you have created some grouping, and then decide to build another pivot table to get another view of the same data, while keeping the original pivot table in place.

The second pivot table automatically groups the data based on the first pivot’s grouping. And if you change the grouping on the second pivot, the first pivot table changes too.. Bo hoo hoo 🙁

This can be frustrating and sometimes difficult to troubleshoot or fix.

Why does a Pivot Table share its Grouping with another Pivot Table?

That’s because both the pivot tables are sharing the same pivot cache. To understand better, when Excel creates a pivot table, it makes a copy of the entire source data, and creates a temporary pivot cache in the memory. This duplicated cache is now stored with the Excel file, doubling its size. If your source data was huge, the excel file soon soars in size too.

Excel

To save hard drive space and memory, when the second pivot table was created, it used the same cache as the first pivot table. This makes sense, but then since the cache is shared, change in the cache for one pivot table affects the other pivot table too.

The solution is to have a separate pivot cache for the second pivot table.

How to Create a Separate Cache for the Second Pivot Table?

Although this method will use up more memory, it is a good solution, works well, and hardly takes any time to implement.

Select the source data, go to the Formulas tab, and click on Define Name button.

Key in a unique name in the popup. Let’s say you call this DataSet1. This creates a Unique Named Range.

Now click on the Define Name button once more, and create another name for the same data set. Let’s call this DataSet2.

Create Pivot Tables with Unique Data Sets

Now create the first pivot table based on the first Data set (DataSet1). Group on whatever fields you want. Once you are happy with the result, do the same thing for the second pivot table.

Just remember to use the second data set for the second pivot table (DataSet2). Group the data on a different field.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the Pivot Table techniques in our
Pivot Table Masterclass Training
!


You will notice that the grouping of the second table stays, and it does not alter the grouping order of the first pivot table. That’s because we are using different defined names.

Thus, Excel creates two different pivot caches, and even though both refer to the same data set, it is transparent to Excel, and is of no consequence.

Now you can enjoy the benefits of two different views, one with one set of grouping, and another with another set of grouping.

Although this method inflates the size of the file, it is a quick and dirty method, that works well. Try it out… and let me know how it goes…

Cheers,Vinai Prakash,
Founder of ExcelChamp.Net

Additional Resources:
If you found this tip useful, you may want to subscribe to the ExcelChamp Weekly Excel Tips Newsletter.

And check out these cool Excel Tips too…

Cheers – Vinai

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

A Pivot Table is used to quickly analyze a large amount of data. It allows you to combine information from different tables and sheets and calculate the overall result. This universal analytical tool significantly expands the capabilities of the Excel program.

You can generate new totals for using original parameters by changing rows and columns in places. You can filter the data by showing different elements. And also you can clearly detail the area.

Pivot Table in Excel

For an example we use the sale of goods table in different trading branches.

You can see from the table what, when and what amount was sold in departments. You will have to calculate manually using calculator to find the amount of sales for each department. Or you can make another Excel spreadsheet where you can show the totals using formulas. These methods of analyzing information are unproductive. It's easy to mistake using such approaches.

The most rational solution is to create a Pivot Table in Excel:

  1. Highlight A1 cell so that Excel knows what information he should use.
  2. In the “INSERT” menu, select the “Pivot Table”.
  3. The 'Create PivotTable' menu opens where we select the range and specify the location. The range field will be filled in automatically since we have set the cursor in the data cell. If the cursor is in an empty cell you need to set the range manually. The PivotTable can be made on the same sheet or on the other. Do not forget to specify a place for data if you want the summary data to be on an existing page. The following form appears on the page:
  4. We will create a table that will show the amount of sales by department. We select the column names that we need in the list of fields in the summary table. We get results for each department.

It’s simple, fast and high-quality.

Important nuances:

  • The first line of the specified range must be filled.
  • Each column should have its own header in the basic table because it's become easier to set up a summary report.
  • You can use the Access tables, SQL Server, etc. as a source of information in Excel.

How to make a Pivot Table from multiple tables?

Excel Merge 2 Pivot Tables

You need often to create summary reports from multiple tables. There are a couple of tablets with information. We need to combine them into one common table. Let’s imagine that we have stock leftovers in two stores.

The order of creating a Pivot Table from several sheets is the same.

Create a report using the PivotTable Wizard:

  1. Call the 'PivotTable and PivotChart Wizard' menu. To do this click the Quick Access Toolbar button and click on 'More Commands'. Here on the 'Options' tab we find the 'PivotTable and PivotChart Wizard'. Add the tool to the Quick Access Toolbar. After this do next:
  2. We put the cursor on the first table and click on the 'Wizard' tool. The window opens and we set right there check-mark that we want to create a table in 'Multiple consolidation ranges'. It means that we need to combine several places with information. The report type is the 'PivotTable', 'Next'.
  3. The next step is to create fields. “I will create the page fields”-'Next'.
  4. We set the range of data which helps us compile a consolidated report. We select the first data range together with the header - 'Add'. Set the second range together with the names of the columns - 'Add' again.
  5. Now select the first range in the list. We put the tick at the field number one. This is the first pivot report field. Give him the name 'SHOP1'. Then we select the second range of data and again enter new name of the field is 'SHOP2'. Click 'Next'-“Finish”.
  6. Choose the place for the summary table. You can do it on an existing sheet or a new one. It is better to choose a new sheet so that there are no overlaps and displacements. At us it turned out so:

As you can see, just a few clicks you can create complex reports from several sheets or tables of different amounts of information.

How to work with Pivot tables in Excel

Let's start with the simplest: adding and removing columns. For example, consider the sales Pivot Table for different departments (see above).

We had a task pane where we selected the columns in the fields list. You can see it to the right of the summary table. Just click on the plate if it disappeared.

Add one more field to the summary table to make a report. To do this, check the box next to 'Date' (or next to 'Product'). The report immediately changes. A sales dynamic appears by day in each department.

Group the data in the report by months. Make right-click on the 'Date' field to do this. Click 'Group'. We choose 'Months'. The result is a summary table of this type:

If you want to change parameters in the summary table, you should just uncheck ticks next to the existing rows fields and set them in other fields. We will make a report which based on the goods names, but not on departments.

That is what happens if we remove the “Date” and add a 'Department':

Excel 2016 combine 2 pivot tables

But this report can be done if you drag fields between different areas:

If you want the line name to become the column name, so than select this name and click on the pop-up menu. Click 'Move Date to Columns'. In this way we move the date into columns.

We put the field 'Department' afore the names of goods using the menu section 'Move to Beginning'.

Let’s show details for a particular product. For the example we will use the second summary table where the stock leftovers are displayed. Highlight the cell. Right-click - 'Expand/Collapse' - “Expand” - “Amount” - “OK”.

Combine 2 Pivot Tables Excel 365

Select the data field that you want to display in the menu that opens.

A tab with report parameters becomes available when we click on the summary table. With its help you can change titles and data sources and also group information.

Checking the correctness of bills

Excel Combining Pivot Tables

It is easy to check how well the service providers charge the rent using Excel spreadsheets. Another positive aspect is saving. If we monitor gas and energy expenses each month, we will be able to find a reserve for saving money to be able to meet a bills on apartment.

At the beginning we propose you to compile a summary table of tariffs for all utility bills. The data will be different for different cities.

For example, we made a tariffs summary table:

For educational purposes take a family of 4 people who live in 60 square meters’ apartment. You need to create tables for calculation for each month to monitor utility payments.

The first column = the first column in the summary table. The second one is the formula for calculating with the next syntax:

= counter reading *rate / meter living area * number of persons

For easement we recommend you to make an in-between column. You will record there all the meter readings (variable component).

Combine Two Pivot Tables

Our formulas refer to the sheet where the summary data with tariffs is located.

Excel Combine 2 Pivot Tables Two

You can also add housing benefits to the formulas if they are applied in the calculation of utility payments. You should request all the information on charges in the accounting department of your service organization. Just change the data in the cells when tariffs change.