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.


Download Practice Workbook


3 Cases to Find Difference between Two Columns in Excel Pivot Table

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.

Dataset1

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.

How to create table

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

Utilizing Difference from Value Field Settings Option

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

Dragging Field

So, the Pivot Table will be as follows.

Utilizing Difference from Value Field Settings Option

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.

Removing Grand Total

Then, you’ll get the following output.

Utilizing Difference from Value Field Settings Option

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.

Utilizing Difference from Value Field Settings Option

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

Utilizing Difference from Value Field Settings Option

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.

Value Field Settings Option

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

Excel Pivot Table Difference between Two Columns Utilizing Difference from Value Field Settings Option

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

Excel Pivot Table Difference between Two Columns Utilizing Difference from Value Field Settings Option

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.

Rename the Field Name

  • 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).

Hide Columns

Finally, your output is ready!

Excel Pivot Table Difference between Two Columns Utilizing Difference from Value Field Settings Option

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.

Excel Pivot Table Difference between Two Columns Utilizing Difference from Value Field Settings Option


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.

Excel Pivot Table Difference between Two Columns Showing Difference between Two Columns in Percentage

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

Excel Pivot Table Difference between Two Columns Showing Difference between Two Columns in Percentage


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

Utilizing Difference from Value Field Settings Option

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

3rd method

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

Excel Pivot Table Difference between Two Columns Formula to Find Difference between Two Columns

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

=Sales - Cost

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

Excel Pivot Table Difference between Two Columns Formula to Find Difference between Two Columns

After doing that you’ll get the following output.

Excel Pivot Table Difference between Two Columns Formula to Find Difference between Two Columns

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

Excel Pivot Table Difference between Two Columns Formula to Find Difference between Two Columns


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.

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo