• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Pivot Tables Year on Year Change

You are here: Home / Excel PivotTables / Excel Pivot Tables Year on Year Change
September 8, 2014 by Mynda Treacy

PivotTables are a treasure trove of features and one that has been brought out of the dungeons in more recent Excel versions is ‘Show Values As’.

In Excel 2007 it was hidden 3 clicks away in the Value Field Settings dialog box (see below):

Show Values As in Excel 2007

Then in Excel 2010 it was given prime position in the right-click Context Menu, and rightly so:

Show Values As in Excel 2010

In Excel 2010 there are also some new functions like Rank and % Running Total In to name a couple.

In this tutorial I’m going to show you how to calculate Year on Year variances, both absolute values using Show Values as > Difference From:

PivotTable showing year on year change

And as a percentage using Show Values As >% Difference From:

PivotTable showing percentage year on year change

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Here is a sample of my data:

PivotTable data

Here is my PivotTable with Order Amount summarised by Ship Mode and Year:

PivotTable before year on year change

Now I want to add columns for the year on year change (YoY Change).

Step 1: Drag another instance of the Order Amount field to the Values area in the field list, so now you have it there twice:

PivotTable value field

Step 2: In the PivotTable right-click any of the cells containing the second Sum of Order Amount > Show Values as > Difference From:

PivotTable show values as difference from

Step 3: Choose Years as the Base Field and Previous as the Base Item in the dialog box that pops up:

show values as base fields

Step 4: Next we can rename the Column Labels so they’re more meaningful. To do this just type straight in one of the cells containing the column label you want to change, like I’ve done in cell C5 below (Tip: the name must not be the same as any of the source data column headers):

change column label

This will update all of the column labels and the name in the field list:

field list

Voila, your PivotTable now shows the year on year change in Order Amounts by Shipping Mode:

finished PivotTable

Year on Year Percentage Change

We can also show values as % Difference From. The steps are the same except in step 2 you choose % Difference From:

PivotTable percentage year on year change

Tip: You don’t need to have the Order Amount column in your PivotTable to display the YoY Change:

tip

Since the YoY Change is not calculated using the actual values you see in the PivotTable, it is calculating using the source data.

Which means you could plot just the Year on Year Change in a PivotChart if you wanted.

PivotChart

More Pivot Table Posts

Sorting in Excel PivotTables

Sorting in Excel PivotTables

Excel PivotTables Unique Count 3 Ways

Excel PivotTables Unique Count 3 Ways

Excel PivotTables Unique Count, or distinct count as it's also know, is easy, but there are different approaches depending on your version of Excel.
Excel Factor 6 Auto Refresh PivotTables

Excel Factor 6 Auto Refresh PivotTables

Excel Factor Entry 1 - Reverse PivotTable

Excel Factor Entry 1 – Reverse PivotTable

Create a Pivot Table Direct From Access

Create a Pivot Table Direct From Access

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.


Category: Excel PivotTablesTag: pivot table
Previous Post:Deleting Rows in Excel Containing Blanks Returned by Double Quotes
Next Post:Excel Custom Data Validation to Limit EntriesExcel Custom Data Validation

Reader Interactions

Comments

  1. Casey

    January 31, 2021 at 12:18 am

    In your example, how could a CAGR column (annualized growth rate from 2010 to 2012, = (‘2012’/’2010’)^(1/2)-1) of data and have that data be formatted as a percentage even while the sales data stays formatted as currency?

    Reply
    • Mynda Treacy

      February 1, 2021 at 1:06 pm

      Hi Casey,

      Please post your question on our Excel forum where you can also upload a sample file that shows the format and layout of your data and we can help you further.

      Mynda

      Reply
  2. Steve

    April 22, 2020 at 1:12 pm

    Is there a way to filter within the pivot table by the % difference from rather than the sum of the base value? I am using Excel 2019.

    I am trying to show a Y/Y or Q/Q Top 10 and Bottom 10 % changes in revenue from a list of hundreds of locations. I’d like the time period to be controlled by a slicer or timeline.

    Please help!

    thanks

    Reply
    • Mynda Treacy

      April 23, 2020 at 3:33 pm

      Hi Steve, Yes, select the row/column label you want the top two displayed for > click on the filter button > value filters. If you get stuck, please post your sample Excel file and question on our forum where we can help you further.

      Reply
  3. CT

    March 22, 2019 at 10:15 am

    I found this page trying to create a chart like the last image in your article, which chart did you use?

    Reply
    • Catalin Bombea

      March 22, 2019 at 9:19 pm

      Hi CT,
      It’s a column chart.
      Cheers,
      Catalin

      Reply
  4. Barry Dunbar

    February 10, 2019 at 3:10 am

    Hi Mynda,
    I have a pivot table problem with top 20 customer and year on year sales. I’m looking to have the current top 20 customer in the current year and have these customer sales figures for prior year and prior year+1. As you know when you have the top 20 customer sales the pivot table works out the top customer over the grand total sales. How can I do a work around to ignore the grand total?

    Reply
    • Mynda Treacy

      February 10, 2019 at 9:53 am

      Hi Barry,

      You can’t, sorry. If you have all of the years in the PivotTable then the filter for top N will be taking all of the years into consideration. There’s no way to tell it to filter on a single year unless you filter for that year.

      Mynda

      Reply
      • Patrick Rutherford

        April 3, 2020 at 6:35 am

        This is late but a solution. If you can add columns to the base data, add two. In the first one use the countifs and sumifs functions to add all the sales for a customer in the customers first row. Then use the countifs and rank functions to list the customers ranks.

        Example for row 2500 and customer name in column A and sales amount in column B. This sumifs formulas are in column C and rank formulas are in D, year is in column E

        Sumifs: =if(countifs($A$1:$A2500, A2500)=1,sumifs($B:$B,$A:$A,A2500,$E:$E=2020),0)
        Rank: =if(countifs($A$1:$A2500,A2500)=1,rank(C2500,$C:$C,0),index($D:$D,match(A2500,$A:$A,0))

        Make column D the first column in your pivot table and filtered on it.

        Reply
        • Mynda Treacy

          April 3, 2020 at 6:18 pm

          Thanks for sharing, Patrick!

          Reply
  5. Seth Carwyn

    July 13, 2017 at 6:17 am

    Hi,

    I have a pivot table showing the % difference in sales from 2016 to 2017 by customer. It works well except for those customers who had no sales in 2016. The % difference field shows a 0% change even though there are 2017 sales showing. Is there some setting I’ve overlooked that will generate a % difference value if 2016 sales don’t exist? I’ve played around with the PivotTable option “For empty cells show”, but it doesn’t matter if a 0 is entered or not –there is no % change generated.

    Reply
    • Mynda Treacy

      July 13, 2017 at 2:59 pm

      Hi Seth,

      This is mathematically correct. A percentage change requires a starting value for 2016 other than zero. You cannot calculate a percentage change if 2016 had no sales.

      Mynda

      Reply
      • Mirjam

        August 6, 2020 at 6:50 pm

        Hi Mynda,

        My question goes into the same direction. I need to have a table that compares % changes from year to year, e.g. 2020 vs 2019. If a customer has no sales in 2019 the field should say “New” and if there are no sales in 2020 it should say “Lost”. I understand that this fantastic pivot function “show difference in %” can only be used if there are values for both years but maybe you have an idea how I could do that.
        I was thinking of having a formula for the “New/Lost” bit already in the base data, then having the pivot for the % changes for customers having sales in both years and then somehow using a static table combining both.
        However, it all seems very cumbersome to me, maybe you can point me in the right direction? I can provide sample data/table if needed.
        Appreciating your help!
        Mirjam

        Reply
        • Mynda Treacy

          August 6, 2020 at 10:32 pm

          Hi Mirjam,

          The PivotTable won’t return a text value of ‘new’ or ‘lost’ in place of a numeric result, so this type of solution would need to be built outside of the PivotTable. If you get stuck or need some pointers, please post your question and sample Excel file on our forum where we can help you further.

          Mynda

          Reply
  6. Steven

    January 26, 2017 at 4:32 am

    Hi,

    I am using a PowerPivot sourced pivot table. When I follow the steps below I get an #NA error in the % Difference field. Any idea what is going on? I’ve tried it with all years or just 2 years selected and tried with years in the column field and without.

    Reply
    • Mynda Treacy

      January 26, 2017 at 3:40 pm

      Hi Steve,

      Not sure. It should work the same with Power Pivot. I tested it just now and got the same results with both an implicit measure and explicit measure. You can post your file and question on our Excel forum and we’ll take a look.

      Mynda

      Reply
  7. Jay

    October 25, 2016 at 11:44 am

    Hello,

    I’m trying do do something very similar to this and it works great. However, when I try to sort by the new year over year difference column it does not show the difference in the ascending order as I would like.

    Is there a way to sort on a “Difference from” column?

    Reply
    • Mynda Treacy

      October 25, 2016 at 1:22 pm

      Hi Jay,

      No, unfortunately the sort won’t work on columns that use reference points to calculate.

      Mynda

      Reply
  8. Marek

    October 1, 2016 at 3:40 am

    Welcome to Mynda
    You are unparalleled. Thank You for sharing your knowledge and skills.

    Reply
  9. George Sinkinson

    September 9, 2016 at 2:24 am

    Just having a senior moment …

    Can you please show how you went from the year-on-year pivot table to the chart at the bottom?

    Reply
    • Mynda Treacy

      September 9, 2016 at 8:41 am

      Hi George,

      In the Excel file available for download in the blog post above you’ll see the PivotTable feeding the chart is slightly different to the PivotTable above it. Here is the link to download the workbook to save you looking for it.

      Mynda

      Reply
  10. Carl

    August 8, 2016 at 7:17 pm

    Great! Found this my self, but i am stucked at the problem two (2) problems.

    a) Removing the first year.
    In your example 2010. If you like it not to show that, as it doesn’t add any information.

    b) Dynamic base year
    You are using “previous”, if you use a base year, like 2010, in how is every thing going from 2010 (my electricity bill in my case 🙂 Have you find a solution for having a dynamic base line year? Preferably from a “Excel slicer”?

    Reply
    • Mynda Treacy

      August 9, 2016 at 3:18 pm

      Hi Carl,

      I agree it’s annoying having 2010 display when there is no YoY change value for that year, but the PivotTable has to start somewhere and it cannot calculate a change if the year is not present in the PivotTable. In other words, if you remove/filter ut 2010 then you get a blank YoY change for 2011.

      So, the best option is to simply hide the column for the first year, or at least the YoY change column that contains blanks.

      Mynda

      Reply
  11. Mary Beth Riley

    March 9, 2016 at 11:09 am

    Thanks for your posts.

    I was driving myself crazy today trying to find a good way to present year over year analysis without over-complicating the chart. This is a perfect solution. My only issue is the negative growth crosses over the axis names, so I need to decide if it the title look better at the top or bottom on the chart.

    Reply
    • Mynda Treacy

      March 9, 2016 at 11:28 am

      Glad we could help, Mary Beth.

      Reply
  12. Bertha

    November 5, 2015 at 1:48 pm

    I want to calculate amounts year over year in absolute value not in percentage, how can I do. Is there any tool to get it?
    Thanks.

    Reply
    • Catalin Bombea

      November 5, 2015 at 7:55 pm

      Hi Bertha,
      Please take a closer look at the tutorial, you have both versions there, to display absolute values (Show Values as > Difference From:) or percentages (Show Values as > % Difference From:).
      Catalin

      Reply
  13. Adi

    September 10, 2014 at 3:42 am

    Hi Mynda,

    Thanks. I have a very basic question – how did you get the ‘Years’ column to appear in the pivot table, though it is not present in the raw data set.. I got the rest of the tutorial. 🙂

    Thanks,
    Adi

    Reply
    • Mynda Treacy

      September 10, 2014 at 6:22 am

      Hi Adi,

      I grouped the dates into years. You can read more about how to group data in PivotTables here.

      Kind regards,

      Mynda

      Reply
      • Adi

        September 10, 2014 at 4:13 pm

        Thanks Mynda,

        I tried that, and could group it into years. However the difference I noted was that your Pivot Table Field List explicitly has a field “Years”, while mine does not. Sorry to bother you with this question – but would be glad if you could tell me why this happens.

        Adi

        Reply
        • Mynda Treacy

          September 10, 2014 at 4:20 pm

          Hi Adi,

          Group into Months and Years, then you will have Years in your field list.

          Kind regards,

          Mynda

          Reply
          • Adi

            September 10, 2014 at 10:07 pm

            Bingo!!! 🙂 Thanks a ton Mynda!!!!!!!

  14. Nikolas Larum

    September 9, 2014 at 10:40 pm

    Dear Mynda,

    Helpful as always, but I seem to have somehow missed a basic step. How did you manage to get “Years” as a base field? I can’t find the column in your raw data. I run multiple reports from our systems that give individual dates. In order to sort by month or year, I have been resorting to adding that as a separate column in the raw data in order to be able to choose it (usually having to run a text formula and recopying the values or doing a text to columns), which takes additional time.

    You obviously know a better way. Please share. 🙂

    Thanks,
    Nikolas

    Reply
    • Mynda Treacy

      September 10, 2014 at 11:16 am

      Hi Nikolas,

      You can group dates into Months/Years using the Group functionality in PivotTables. You can learn how to group data in PivotTables here.

      Note: in order the Group to work your dates must be Excel Serial number dates.

      Kind regards,

      Mynda

      Reply
  15. Paul

    September 9, 2014 at 4:46 pm

    Hi Mynda,

    Useful as always!

    Is there any way to hide the blank column in the base period? I have a report with some 30 fields, each of which reports for this year and last year and needs the % variance.

    i.e. Current year Last year Variance, Current year Last year Variance, etc. but it comes out as

    Current year Blank Last year Variance, Current year Blank Last year Variance

    which makes it difficult to read and uses up a lot of real estate!

    Reply
    • Mynda Treacy

      September 9, 2014 at 8:49 pm

      Cheers, Paul.

      If you have Excel 2010 or 2013 you can create a Named Set that excludes the first year from your report.

      For the Named Set option to be available you must either create an OLAP PivotTable or use PowerPivot.

      Kind regards,

      Mynda

      Reply
      • Paul

        September 9, 2014 at 9:04 pm

        Hi Mynda,

        Many thanks for that. I’ll need to explore it!

        Kind regards.

        Paul

        Reply
  16. Jatin

    September 9, 2014 at 3:57 pm

    Really useful…..thanks..

    Reply
    • Mynda Treacy

      September 9, 2014 at 3:58 pm

      You’re welcome, Jatin.

      Reply
  17. Jon Acampora

    September 9, 2014 at 8:25 am

    Great tutorial Mynda! This is a very practical application for a pivot table and quite a time saver!

    Reply
    • Mynda Treacy

      September 9, 2014 at 11:11 am

      Thanks, Jon. Glad you liked it 🙂

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.