Excel Pivot Table: Difference between Two Columns (3 Cases)

Certainly, Pivot Table is one of the powerful features in Excel analyzing the larger dataset efficiently. What if you need to find the difference between two columns in the Pivot Table. In this instructive session, I’ll show you 3 methods including step by step process to get the difference between two columns in Excel Pivot Table.

Difference between Two Columns in Excel Pivot Table: 3 Cases

Let’s introduce today’s dataset where the Sales Report for 2021 and 2022 of some Product Categories is provided along with Order Date and corresponding States.

Now, you’ll see the comparison within columns. Let’s explore the methods.

1. Utilizing Difference from Value Field Settings Option

In the beginning, I’ll show you the utilization of one of the calculation options namely Difference From in the Value Field Settings to determine the difference between two columns e.g. Sales in 2021 vs Sales in 2022.

Step 01: Create Pivot Table

• Firstly, you have to create a Pivot Table which is really a simple task. Keep your cursor over any cell within the dataset and then choose Insert tab > Pivot Table > From Table/Range.

• Next, check the Table /Range and circle before the New Worksheet.

• After pressing OK, add (by dragging down the cursor) Order Date to the Rows area, Years to the Columns area, and Sales to Values.

So, the Pivot Table will be as follows.

Step 02: Remove Grand Total Column

If you look closely at the created Pivot Table, you find the Grand Total column which is irrelevant in this task.

• So, go to the PivotTable Analyze tab > Grand Totals > Off for Rows and Columns option to remove the column.

Then, you’ll get the following output.

Step 03: Add Sales Field Again

Now, you have to add the Sales field again to the Pivot Table.

• Just drag the Sales field to the Values area after the Sum of Sales.

After doing that, you’ll get two similar Sum of Sales fields for a year! Let me clarify why you have to do this.

Step 04: Apply ‘Difference From’ Option 

In this step, you have to apply the Difference From option.

• Right-click while keeping the cursor over the Sum of Sales2 field and choose the Value Field Settings.

• Then, click over the Show Values As option and choose the Difference From option from the Show values as.
• Moreover, pick the Years as the Base field and (previous) as the Base item.
• Lastly, press OK.

Therefore, you’ll get the Difference (in the E7:E11 cells) between the Sum of Sales in 2021 and 2022.

Step 05: Rename Field Name and Hide Irrelevant Column

Actually, you got the output but you need to edit some things for a better presentation.

• Double-click on the E5 cell to rename the Sum of Sales2 field to Difference.

• Actually, column C is unnecessary. Though you cannot delete the column as it is inside the Pivot Table, you can Hide the column (just right-click over the column and choose the Hide option).

Finally, your output is ready!

Similarly, you can find the Difference based on the Product Category. To do this, remove the Order Date field from the Rows area and add the Product Category field.

Read More: Calculate Difference Between Two Rows in Pivot Table

2. Showing Difference between Two Columns in Percentage

If you want to get the difference in percentage e.g. % of sales growth or declination rate, this method would be fruitful for you.

• When you apply this method for a newer dataset, do Steps 1-3 as illustrated in the first method.
• Later, go to the Value Field Settings and choose the % Difference From option from the Show values as.

Eventually, you’ll get the Difference in % after pressing OK.

Read More: Pivot Table: Percentage Difference between Two Columns

3. Using Formula to Show Difference between Two Columns in Excel Pivot Table

Fortunately, there is another way (i.e. just deduct the two columns) to find the difference between two columns in the Excel Pivot Table.

Let’s say, you have Cost and Sales columns in your Sales Report. And, you need to find the Profit or Loss.

• Initially, you must create a Pivot Table like the following one.

• Next, click the Calculated Field… option from the Fields, Items, & Sets in the PivotTable Analyze tab.

• Type the Name as Profit and insert the following formula in the Formula box.

• Just, double-click over the fields to add inside the formula.
• Lastly, press Add and then OK.

After doing that you’ll get the following output.

Furthermore, you can do it to get the Sum of Profit yearly and monthly.

Download Practice Workbook

Conclusion

That’s the end of today’s session. This is how you can calculate the difference between two columns in Excel Pivot Table. Anyway, don’t forget to share your thoughts.

<< Go Back to Difference in Pivot Table | Pivot Table Calculations | Pivot Table in Excel | Learn Excel