fbpx
Search
Close this search box.

Show monthly values & % changes in one pivot table

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

54 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?...

  27. Cynthia says:

    Can I use a pivot table for a month to month comparison? To date our Jan-Jun sales totals 1.1 million. I need to know what is needed to reach 1.8m 2.0m; and 2.2 m for the rest of the year.

  28. Annette says:

    I have a question. How do I get a calculated field to show the running % of actual to goal when I have the columns grouped as months in a pivot table? (I can't sum the individual %'s because the monthly goal is just 1 value added to the table for each month) The result I am getting is just the % to goal within the month even tho I have the actual and goal as a running total

  29. JIby Jacob says:

    Thanks Chadoo. I tried doing conditional formatting as you advised. It did not work. Could you please advice what could be the issue..

  30. Chad says:

    Hey Chandoo!

    Great Stuff! What is your recommendation for using icon sets with % change from previous month when a negative number grows to a larger negative number? The formatting shows this as positive, resulting in an up arrow or positive change when in fact it should be showing a down arrow or negative change. Mathematically the negative over a negative cancels out. Any insight on this would be very helpful. Keep up the great work!

  31. Kate says:

    Hi, need some help please. Want to use a formula with reverse calculations, and I am mathematically dyslexic. Lets say i have 25 days in my month, and by day 25 i need X amount to cover costs etc. What formula can i use that will tell me what daily amount i need to reach this. But, this figure should take into account the amount I made the day before. Please help?

  32. sunil says:

    i have two year data (Jan to Dec) and i want to make a comparision sheet how to prepare

  33. […] or styles on the cell using Home > Cell Styles. Some example of zebra style formatting and increase/decrease icons and creating/editing […]

  34. Rob says:

    I'm working with cumulative data and using Difference from... Previous month to convert it to monthly data. In the first month of activity for a program, monthly data should match cumulative data, but shows up as zero or empty instead, rendering monthly data unreliable. Any suggestions on how to get Excel Pivot Table to calculate Difference from... Previous month differently for cases where there is no data for the previous month?

    • Rob says:

      I found a solution/workaround. By entering a record for each program at time zero (in the source data) with no cumulative data, the calculation is corrected. This provides a previous record for the Pivot Table to calculate differences from.

  35. Sarah says:

    Thank you for these step by step instructions. They are fabulously clear and simple.

    Thank you!

  36. R. Balamurugan says:

    Hai Mr. chandoo,

    This is balamurugan and i know the only the basics of the Excel and now I want to make the report for our product in for the monthly material consumption it should be came monthly vise and the output also we will mention, so that we can compare what is the consumption of the particular month and the output efficiency, we have to make the chart also.

    Awating for your reply
    .

  37. Gabriel_K says:

    Thanks. It's very usefull. Works well.

  38. Pablo says:

    I often use this technique to compare data from 2 different lists (for example, the inventory detail vs the accounting entries) and spot the differences.

  39. Gabriel says:

    Very nice. But what when I have January to be expandable/grouped into its weeks? Any chance the Change is also shown for the weeks when expanded? Like Week1 2017 vs Week1 2016?

  40. Tu Nguyen says:

    Thank you for your sharing.

    In case the report views from differences by row. So how can I do the change rate like that?

    Thank you so much!
    Tu Nguyen

  41. Joe says:

    Hi there,

    I'm wondering if figures variance of M-o-M and Y-o-Y can be shown on the same table. e.g. Oct 2020 vs Sep 2020 and Oct 2020 vs Oct 2019.

  42. Ajenjo says:

    Using pivot table am trying to calculate the difference between December of year 01 (previous year) and January of year 02 (new year).

    Have tried several solutions without success. Mostly to do with adding Amount, in my case, three time and then showing value as Difference From... However, this does not solve the difference between December and January. Any help appreciated. Thank you.

Leave a Reply