locked
How to highlight year over year differences in pivot table RRS feed

  • Question

  • Hello. I have years and categories in a pivot table. I used the % of parent, year to get %'s for each year. I would like to highlight any year over year differences that are above X%, but I  DO NOT want to create another column that calculates those differences. Thanks. 
    Friday, September 9, 2016 9:38 PM

Answers

  • Hi Albo44,

    According to the following steps, I reproduce your scenario using my sample data in my computer and get the results.

    Firstly, I give the sample data and create a pivot table. The Percentage column describes the %'s for each year you get.

    Secondly, right click on 2nd value and choose value field settings.  Choose “% Difference from” ->Select Date as base field-> Select (previous) as base item->Click ok.

    After above operation, I get the new values  which shows  % changes with respect to previous year in the pivot report the in 2nd column. The new values override the old values.

    Finally, Select any cell in the % change column. Go to Home > Conditional Formatting > New rule, specify the 1% as boundary in the below screenshot.  And we get the expected results: the orange shows the differences between the percentages that are over 1%, the blue is less than 1%.

      

    For more details, you can review this article. If this isn’t what you want, please give me more details and sample data.


    Regards,
    Angelia

         

    Monday, September 12, 2016 6:50 AM

All replies

  • Hello. I have two years of information in my power pivot table. I used built in % of parent for years. How can I highlight differences between the percentages that are over 1%? Obviously it wouldn't be a new column with the differences but excel would just know. Gracias. 
    • Merged by Olaf HelperMVP Saturday, September 10, 2016 7:01 AM Same question from same OP
    Friday, September 9, 2016 9:06 PM
  • Hello,

    Highlighting values is part of the frontend = MS Excel, and there you can do it easily with the build-in feature conditional formatting.

    And please avoid double post.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Saturday, September 10, 2016 6:50 AM
  • Hi Albo44,

    According to the following steps, I reproduce your scenario using my sample data in my computer and get the results.

    Firstly, I give the sample data and create a pivot table. The Percentage column describes the %'s for each year you get.

    Secondly, right click on 2nd value and choose value field settings.  Choose “% Difference from” ->Select Date as base field-> Select (previous) as base item->Click ok.

    After above operation, I get the new values  which shows  % changes with respect to previous year in the pivot report the in 2nd column. The new values override the old values.

    Finally, Select any cell in the % change column. Go to Home > Conditional Formatting > New rule, specify the 1% as boundary in the below screenshot.  And we get the expected results: the orange shows the differences between the percentages that are over 1%, the blue is less than 1%.

      

    For more details, you can review this article. If this isn’t what you want, please give me more details and sample data.


    Regards,
    Angelia

         

    Monday, September 12, 2016 6:50 AM