Tanjima Hossain
If you are looking for ways to compare two pivot tables in Excel, this article is for you. So, let’s start with the main article to apprehend the examples related to this task.
Download Workbook
3 Examples to Compare Two Pivot Tables in Excel
Here, we have the following two datasets containing the sales record for 2020 and 2021 for different products of a company. In this article, we will try to compare these two tables either from different values perspectives or from different texts perspectives.
Example-1: Using Formula to Compare Two Pivot Tables in Excel
Here, we will use the GETPIVOTDATA function to calculate the differences between the sales values of different years.
Step-01: Creating Two Pivot Tables in One Sheet
In this step, we will create two pivot tables in one sheet using the two datasets for the years 2020, and 2021.
- Go to the Insert tab >> PivotTable dropdown >> From Table/Range
After that, you will have the PivotTable from table or range dialog box.
- Select the range of the first table for the Year 2020 in the Table/Range
- Click on the New Worksheet option and press OK.
Then, you will be taken to a new sheet where you will have two portions; PivotTable3, and PivotTable Fields.
- Drag down Product to the Rows area, Year to the Columns area, and Sales to the Values
Now, to insert the second table in this same sheet we have to insert the table again.
- Click on cell A14 (or another cell) and then go to the Insert tab >> PivotTable dropdown >> From Table/Range
Afterward, you will have the PivotTable from table or range dialog box.
- Select the range of the second table for the Year 2021 in the Table/Range
- Click on the Existing Worksheet option and press OK.
Then, you will get a new PivotTable and its related fields.
- Drag down Product to the Rows area, Year to the Columns area, and Sales to the Values
In this way, you will be able to insert two pivot tables in one sheet and then we have created a space for gathering the differences for different products following the second table.
Step-02: Insertion of Formula to Calculate Differences
Here, we will use the GETPIVOTDATA function to calculate the differences between the sales values.
- Type the following formula in the cell B26
=GETPIVOTDATA("Sum of Sales",$A$5,"Product",A26)-GETPIVOTDATA("Sum of Sales",$A$16,"Product",A26)Here, the Sum of Sales is the Field from which we want to get the values, $A$5 is a cell of the first PivotTable from which we want values, Product is the Field name, and A26 is the Item name of this Field. For the second portion $A$16 is a cell of the second PivotTable from which we want values.
Formula Breakdown
- GETPIVOTDATA(“Sum of Sales”,$A$5,”Product”,A26) → becomes
- GETPIVOTDATA(“Sum of Sales”,$A$5,”Product”, “Apple”)
- Output → 4415
- GETPIVOTDATA(“Sum of Sales”,$A$16,”Product”,A26) → becomes
- GETPIVOTDATA(“Sum of Sales”,$A$5,”Product”, “Apple”)
- Output → 2442
- GETPIVOTDATA(“Sum of Sales”,$A$5,”Product”,A26)-GETPIVOTDATA(“Sum of Sales”,$A$16,”Product”,A26) → becomes
- 4415 – 2442
- Output → 1973
- 4415 – 2442
- GETPIVOTDATA(“Sum of Sales”,$A$5,”Product”, “Apple”)
- GETPIVOTDATA(“Sum of Sales”,$A$5,”Product”, “Apple”)
- Drag down the Fill Handle
Then, you will get the values for the rest of the cells.
In this way, you will be able to calculate all of the differences between the sales values for the years 2020 and 2021.
Read More: Compare Two Tables and Highlight Differences in Excel (4 Methods)
Example-2: Comparing Values with Pivot Table by Combining Two Tables
Here, we will combine the following two datasets for the years 2020 and 2021 and then convert them into a PivotTable to show the differences.
2.1. Calculation of Difference Between Two Columns
Here, we will show the process of calculation of the differences between the show values using the Difference From option.
Steps:
- Combine the columns Product, Year, and Sales of the Year 2020, and 2021.
- Go to the Insert tab >> PivotTable dropdown >> From Table/Range
After that, you will have the PivotTable from table or range dialog box.
- Select the range of the new table in the Table/Range
- Click on the New Worksheet option and press OK.
Then, you will be taken to a new sheet where you will have two portions; PivotTable6, and PivotTable Fields.
- Drag down Product to the Rows area, Values to the Columns area, and Sales twice to the Values
Then, you will have Sum of Sales and Sum of Sales2 in the Values area.
- Click on the dropdown symbol of the Sum of Sales2 field and choose the Value Field Settings
After that, you will get the Value Field Settings wizard.
- Set the Custom Name as Variance, go to the Show Values As tab and select the Difference From option and then choose Year as the Base field and next as the Base item.
- Click on OK.
Then, you will get the following Pivot Table where in the first Variance column you will get the differences between the sales values for the years 2020 and 2021.
Here, we have also an extra column named Variance and Total Variance where we don’t have values that we will hide.
- Choose the Variance column and Right-click
- Select the Hide
In this way, we have hidden the second Variance column and now we will hide the Total Variance column.
Finally, we will have the following pivot table with the differences in the sales values.
2.2. Calculation of Percentage Difference Between Two Columns
Here, we will calculate the differences in percentage form in the following table.
Steps:
- Click on the dropdown symbol of the Sum of Sales2 field and choose the Value Field Settings
After that, you will get the Value Field Settings wizard.
- Set the Custom Name as % Variance, go to the Show Values As tab and select the % Difference From option and then choose Year as the Base field and next as the Base item.
- Click on OK.
Then, you will get the following Pivot Table where in the first % Variance column you will get the differences between the sales values for the years 2020 and 2021.
Here, we have also an extra column named % Variance and Total % Variance where we don’t have values that we will hide.
After hiding the second % Variance and Total % Variance we will get the following table with percentage differences in the sales values for the two years.
Read More: How to Compare Two Tables for Differences with Excel VBA (3 Methods)
Example-3: Comparing Texts with Pivot Table by Combining Two Tables
In this section, we will compare the Salesperson names for the two years 2020 and 2021.
Steps:
- Combine the columns SalesPerson, Year of the Year 2020, and 2021.
- Go to the Insert tab >> PivotTable dropdown >> From Table/Range
Afterward, you will have the PivotTable from table or range dialog box.
- Select the range of the new table in the Table/Range
- Click on the New Worksheet option and press OK.
Then, you will be taken to a new sheet where you will have two portions; PivotTable7, and PivotTable Fields.
- Drag down SalesPerson to the Rows area and Values area, Year to the Columns
Eventually, you will get the following table where you have the counting numbers under the year columns for different sales persons where 1 is representing that this person works as a SalesPerson for that corresponding year.
Conclusion
In this article, we tried to compare two pivot tables in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
