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

**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.**

*2021*__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
in the*Year 2020***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
to the*Product*area,*Rows*to the*Year*area, and*Columns*to the*Sales**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
in the*Year 2021***Table/Range** - Click on the
**Existing Worksheet**option and press**OK**.

Then, you will get a new **PivotTable **and its related fields.

- Drag down
to the*Product*area,*Rows*to the*Year*area, and*Columns*to the*Sales**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**

- 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

**and then convert them into a**

*2021***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**, and*2020*.*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
to the*Product*area,*Rows*to the*Values*area, and*Columns*twice to the*Sales**Values*

Then, you will have ** Sum of Sales **and

**in the**

*Sum of Sales2***area.**

*Values*- Click on the dropdown symbol of the
field and choose the*Sum of Sales2**Value Field Settings*

After that, you will get the ** Value Field Settings **wizard.

- Set the
**Custom Name**as, go to the*Variance***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

**and**

*2020***.**

*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
field and choose the*Sum of Sales2**Value Field Settings*

After that, you will get the ** Value Field Settings **wizard.

- Set the
**Custom Name**as**%**, go to the*Variance***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

**and**

*2020***.**

*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

**and**

*2020***.**

*2021*** Steps**:

- Combine the columns
**SalesPerson**,**Year**of the**Year**, and*2020*.*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
to the*SalesPerson*area and*Rows*area,*Values*to the*Year**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.