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

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/excel-pivot-tables/pivottable-report-adding-a-data-field-that-calculates-the-difference-from-the-previous-item.html
SHARE




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.

 
img1
 

  • We have created pivot report using data sheet.

 
img2
 

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

 
img3
 

  • The value field settings dialog box will appear

 
img4
 

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

 
img5
 

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

 
img6
 

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

 
img7
 

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

 
img8
 

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

 
img9
 

  • Click on OK twice.

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

Please follow and like us:
0


Leave a Reply

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

To avoid automated spam,Please enter the value *

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>