CODEWITHSUNDEEP
excelpivot-table
themaverick
themaverick

Reputation: 5

How to find % change between values in 2 columns in a Pivot table?

I have created a Pivot table on a dataset in Excel. I have 2 columns for yearly values and want to insert a new column in the pivot table for % change between them.

I've tried using the "Field Settings" to find percent difference, but it doesn't give the desired result.

The desired result is shown in this picture

Upvotes: 0

Views: 18626

Answers (2)

chucklukowski
chucklukowski

Reputation: 2034

This assumes you don't have gaps between years.

  1. Add the amount field to the values section of the pivot table, so that it is there twice.
  2. Right-Click one of the values in the newly added
  3. Choose Show Values As -> % Of ... ->
  4. Base Field should be: Year and Base Item should be: (previous)
  5. Rename column to % Difference
  6. It will show for every year, so hide the column for years that you don't want it

Upvotes: 1

JvdV
JvdV

Reputation: 75840

Not too hard, when you have your pivot table:

  • Select any cell
  • Go to Excel Ribbon > PivotTable Analyse > Fields, Items & Sets > Calculated Field

  • Give a title, e.g.: % Difference and add a formula:

    = ('2018-19'-'2017-18' )/'2017-18'

  • Confirm and once confirmed, change the datatype of this field to percentage

enter image description here

Note, things might be named a little different in your Excel since I'm using a Dutch Excel 2019 version.

Upvotes: 1

Related Questions