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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube