Show monthly values & % changes in one pivot table

Posted on November 6th, 2012 in Excel Howtos , Pivot Tables & Charts - 35 comments

Pivot tables are great help when analyzing lots of data. One of the common questions managers & analysts ask (when looking at monthly sales data for example) is,

How is the monthly performance of our teams (or regions, products etc.)?

A pivot report can answer this question in a snap.

A typical Monthly sales report using pivot tables - this gives incomplete analysis

But the answer is incomplete!

Why? Because, we don’t want sum of sales by month & sales person alone. We want to know their performance! Something like below:

Show monthly values & % changes in one pivot report - Excel Pivot Table examples

Performance eh?!? How to measure it?

There are many ways to measure performance. For our monthly sales data, we can measure performance by comparing,

  • Sales with targets
  • This month value with previous month value
  • This month value with same month last year value
  • One person’s sale with rest of team etc.

One of the most common ways to measure performance in situations like this is to see how this months value has changed compared to previous month.

How to show monthly values &  % changes in pivot?

Do you know that with just a few clicks, we can add % changes to our pivot? Follow these steps:

1. Create a pivot report with months & sales persons (or months & products, months & regions etc.)

2. Add Net sales (or any other metric) to value field area of pivot report

3. Now, add net sales once again to value field area

That is right. You can add same metric more than once to pivot table value field area.

At this point, our report looks like this:

Add same metric two times to the pivot report - this is how it looks after we are done

Showing monthly differences in pivot report using value field settings4. Right click on 2nd value and choose value field settings.

5. Click on Show values as tab and follow below steps. (see image aside)

  1. Choose “% Difference from” from the drop down
  2. Select Month as base field
  3. Select (previous) as base item
  4. Click ok

This will show % changes with respect to previous month in the pivot report!

Showing monthly values & % difference in one pivot report - end result

Extending this to make it even more awesome

1. Clean up the titles

Change the titles to Sales & % change. To do this, just select the first column title and type over. Repeat for 2nd column.

2. Add conditional formatting

Select any cell in the % change column. Go to Home > Conditional Formatting > New rule

[Resource: Introduction to Excel Conditional Formatting, more]

Specify the rule as mentioned in below illustration.

Conditional formatting monthly sales pivot report - instructions

3. Show just icons

We can go one more step and show just icons. Since pivot tables show tool tips on hover, we can easily find % change for any month / sales person by just pointing on that cell.

Pivot table tool tip demo

Finalized monthly report

Our final report looks like this:

Show monthly values & % changes in one pivot report - Excel Pivot Table examples

Download Example Pivot Report

Click here to download this example pivot report. Examine various settings & conditional formats to learn this better.

How do you use value field calculations in Pivot reports?

Although most of my pivots use simple sum or count type of summaries, often I use custom calculations like % difference from, running total , % of row etc. to understand the data better. These are very simple to setup yet give powerful insights.

What about you? Do you use value field settings to modify your pivot reports? What other summary techniques you use? Please share your tips, ideas using comments.

More on Pivot Tables

Along with formulas, Pivot tables are best friends of Excel analysts. They can take massive amounts of data, process and summarize in just a few clicks. To learn more about them, use below resources.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

35 Responses to “Show monthly values & % changes in one pivot table”

  1. Shabbir says:

    Simply WOW!

  2. Rudra says:

    Hi Chandoo,

    All these days I was using show value as ‘% row total’ or ‘% column total’ or ‘% grand total’ only. Now after seeing this tutorial I will start using others also..Thanks a lot for explaining this.
     

  3. Nick says:

    Awesome stuff.  Really can do a lot of nice things in pivot tables, though they do have some limitations.  Depending on your data, using the free PowerPivot add-in might be a good fit.  Can really do a lot more, but not always needed.  Also, maybe a future post about the GETPIVOTDATA() function.  Just an idea, good stuff as usual!

  4. George says:

    Is there a way to duplicate this (the symbols bit) in excel 2003?

  5. LeonK says:

    Hi Chandoo.
     
    Excellent feature.  I used your previous examples to achieve this is a regular spreadsheet.  Doing it in a pivot table is fantastic and never occurred to me.  I have just incorporated the method into all end-of-month reports for tomorrow.
    Very many thanks

  6. Rahim says:

    Its Simply AWESOME :)
    Thanks for sharing ! 

  7. Rahim says:

    Pivot Table Query:
    When I apply %of Columns in Pivot Table and Filter the Data by selecting some specific months It changes the percentages everytime because it assumes it as 100% and divide the rest months percentage by it BUT I dont want this…. instead of this Pivot table shows the actual percentages in % of column.. Any Idea ???

  8. Jomili says:

    In the two pivots in the attached workbooks, how did you turn off the “Column Labels” and “Row Labels” dropdowns?

  9. José Lôbo says:

    Hi, Chandoo I think pivot table are useful to make a report one time, but if you really want a “monthly and dynamic” reports I would like to use another technics, formulas to extract dates and data.

  10. Sally says:

    I don’t know what I am doing wrong but I’m getting #N/A instead of a %. I have tried several times completely from the beginning, and have (well thought I have) followed the instructions carefully.
     

    • Chandoo says:

      Hi Sally… Does your data have any blanks or missing months?

      Can you retry this from the data in attached file

      • Sally says:

        Solved it thank you – when I used your figures, I realised the “month” I am to select is actually the first column (the date) rather than the values.
        Looks wonderful now – will certainly add dimension to my sales reports.
        Thank you
        Sally

        • Sally says:

          Report looks great now. I have even added Slicers and a graph. Playing around by comparing a particular month of each year etc – very impressive and so easy (once you follow the instructions correctly)
          Thank you Chandoo for sharing your knowledge so freely.
          Sally

          • Sally says:

            Back again … I’m just so excited about what I can now do so easily. I’ve read your comment about using it for running totals and have added that into my table as well – something I was doing manually up until now.
            Thank you again …
            Sally

  11. Nikki says:

    This is amazing Chandoo! Could you please teach us on Formulas : Calculated Field / Calculated Item? Ihave never known how to make use of this….

    Just love your site :)

  12. vishwanath says:

    Hi,

    every useful ………………… 

  13. Jitendra says:

    Amazing, Awesome, Interesting ….

  14. Yasmeen says:

    Iam very much confused with the example..can some one share some easy way to do the same example.
     
    Thanks!!

  15. Pavi says:

    @Yasmeen…

    Please download the sample file and use it…

    Regards.
    Pavi   

  16. Brent says:

    thanks!

  17. Bhushan Sabbani says:

    Hi Chandoo,

    It it really great & fanastic feature you have shown,

    But i have an data in pivot as below (It has total 10 divi.) 
    Report Filter has Month to select.

    Column A is Marketing Person
    Column B is Divi. A Target
    Column C is Divi. A  Sales
    Column D i want Variance of Target minus sale of Divi A
    Column E i want % Variance of Target minus sale of Divi A.

    I have tried various option of Column total % i am not getting the proper variance. 

    I will greatful if you help me.

     
     

  18. K Ebrahim says:

    Hi Chandoo

    Been following your website for a while and i think you are doing a pretty neat job and your Excel skills are quite superb.

    I do have a question about what if i need 2 sets of Arrow ICON SETS for a similiar report i am working on.

    Within the Conditional Formatting comman, the built-in XL2007 Arrow Icon set is fine where (Favourable) increase is Green UP arrow while (UnFavourable) decrease is Red DOWN arrow. This suits my one need.

    However, the other need is Opposite in rationale (such as showing for expense comparison).  In this case (UnFavourable) increase should be Red UP arrow, while (Favourable) decrease should Green DOWN arrow.

    The icon set in XL2007 only has the Red DOWN pointing arrow and Green UP pointing arrow and not the other way round. 

    Opting for “Reverse Icon Order” only changes the order but not the up/down orientation of the arrows.

    Any suggestions as to how can this be achieved would be appreciated.

    Thanks in advance

  19. Swapnil says:

    Hi Chandoo, a very neat & useful article, however, the last point is not much clear.i.e.”We can go one more step and show just icons”, how to do it?

    • amit chauhan says:

      Just  select manage rule > edit rule from conditional formatting & check show icon box from bottom right and apply…

  20. Zahid says:

    Thanks Chandoo. I really want to use this but the problem is I can no longer sort by values which I need to be able to do when. Do you know what the solution might be?

  21. Jon says:

    Very helpful. I’ve got a quick question – what If I also want a column showing the Year over Year % difference by Month? For example April 2013 as a % of April 2012?

    Thanks for any insight.

    - Jon

  22. Dado_run says:

    Thank you very much Chandoo.
    But how I can handle conditional formatting in a pivot table when I have errors in the calculation of % change?
    It seems that Excel is not applying conditional formatting when I have an error in the column.

    • Alice says:

      To Fix this – go to Pivot Table -> Options
      Options
      Format
      For Error Cells Show…Change the blank field to 0 (zero)

  23. JR says:

    NOT WORKING
    I keep getting an error when I try to add the field twice. It reads: “Multiple data fields of the same field are not supported when a PivotTable report has calculated items”. Please advise

  24. Jon says:

    I’ve got a graphing question,

    I use this technique often (showing a sum and % in the value field). When I make a pivot chart from this table it graphs both the sum and the % – how can I get it to graph only the sum?

    Thanks

    -jon

  25. […] Magic Trick 612: Pivot Table Year Over Year Sales Calculation From Dates Listed As Text – YouTube How to show monthly values & % changes in one pivot table – Excel Pivot Table Examples | Chandoo… The problem you describe with trying to do this Calculated Items is a good reason why those aren't […]

  26. Vignesh says:

    Dear,

    I am basic in Ms excel.
    How to add points for products in an excel file?…

Leave a Reply