PivotTable Report - Adding A Data Field That Calculates The Difference Between Two Data Fields In Excel 2010

In this article, we will learn how to add a data field that calculates the difference between two data fields in pivot table report.

In PivotTable, we can calculate the difference between two data fields.

Let us take an example:

• We have month-wise Sales report for four regions.
• Column A contains region, column B contains date, and column C contains Sales figure.

• We have created pivot report using data sheet.

• In above pivot table, you can see that North region has the highest sale among all the regions.
• To calculate the difference or compare from one field
• Click on Sum of Sales in Values field
• Click on Value Field Settings.

• The value field settings dialog box will appear

• Select Show Values as tab
• Select option Difference From
• In Base field select Region & Base item as North
• Click on OK

• You will see the difference in sales figure in other regions.

• To make things visually different, you can change the number format
• Click on Sum of Sales in Values field
• Click on Value Field Settings.

• The value field settings dialog box will appear
• Click on Number Format

• Select Number tab from Format Cells
• In Decimal places, enter 0
• Select the fourth option in Negative numbers box

• Click on OK twice.

 

 
Similarly, you can calculate the difference from one month’s sale figure with other ones.