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.

## How to Compare Two Pivot Tables in Excel: 3 Practical Examples

Here, we have the following two datasets containing the sales records 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 text 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
in*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
*Product*to thearea,*Rows**Year*to thearea, and*Columns**Sales*to the*Values**field.*

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 thearea,*Rows**Year*to thearea, and*Columns**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**

- 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: **How to Create Pivot Tables for Meaningful Data Analysis

__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**, 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
*Product*to thearea,*Rows*to the*Values*area, and*Columns**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
, 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

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

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 %
*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 Drill Down in Excel Without Pivot Table

__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 thearea and*Rows*area,*Values**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 **represents that this person works as a *SalesPerson *for that corresponding year.

**Download Workbook**

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