Creating a Percent Change Field in Excel

​By Erin J. Holmes, Associate Vice Provost for Institutional Research, University of Alaska Anchorage

This tip involves creating a percent change field using the “Show Values As” function in an Excel 2013 Pivot table. Percent change is a valuable figure when examining trend data in an Excel pivot table. Many IR analysts create these data by hard coding the equations into cells adjacent to the pivot table. This works well until the pivot table is expanded and the equations are over-written by the pivot table!  

This problem can be avoided by using the “Show Values As” function in the value field settings in the pivot table. I demonstrate the value of this function with simple student credit-hour information, but it can be applied to any data you wish to examine for changes over a period of time. 

Step One: Create a data set to use as the basis for your pivot table. Below is a sample data set arranged by College, Fall Term, and Student Credit Hours (SCH).

Excel Data Table 

PctChange1.JPG
Step Two: Create a pivot table using your data. NOTE: If you are not familiar with how to create a pivot table, search on the Internet, as there are several great sites with step-by-step instructions. I have included a few links at the end of this Tech Tip.

Basic Pivot Table 

PctChange2.JPG
Step Three: After the basic pivot table has been created, go to the Pivot Table Field List pane. Drag the target analysis field (SCH for this example) to the “Values” area. Click on the second line under Values (circled in red below) and then select “Value Field Settings.”
 
Next, select the “Show Values As” tab and then select “% Difference From” in the drop-down option list. Excel will then ask you to choose a “Base Field” and a “Base Item.” Because the table is to display percent change from Fall 2009 to Fall 2013, select “Fall Term” for the Base Field and “Fall 2009” for the Base Item.
 
Finally, change the custom name to “Pct. Difference” as general table cleanup. Click ”OK.”

 PctChange3.JPG

  Final Pivot Table with Pct. Difference
PCtChange4.JPG
 
There are helpful Pivot Table links on the Excel Experts website and YouTube

 

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 8
 
Terry posted on 6/18/2015 11:45 AM
I do lots of trend data and I have always hard coded the Percentage Change field. Thanks!
Lisa posted on 6/18/2015 11:48 AM
This is a great tip! I have mainly used the Sum, Count, and Average value fields and haven't experimented much with other options. I'm excited to try this trick out. Thank you.
Lynn posted on 6/18/2015 1:26 PM
This is really a helpful tip! It takes no time to get the percent difference.
Lee posted on 6/18/2015 1:35 PM
Thanks Erin! I use Excel frequently, but this is new to me and very helpful.
Dina posted on 6/18/2015 4:12 PM
Very cool! I am looking forward to using this.
Shawn posted on 6/19/2015 9:51 AM
Very useful tip, thanks!
Keith posted on 6/19/2015 10:26 AM
I have not used this function much. Is it possible to calculate the Pct. Difference between multiple columns of values?
Julia posted on 6/19/2015 10:53 AM
Thanks for the tip Erin! I also really appreciated the links to learning how to create pivot tables at the end.