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.
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:
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:
4. Right click on 2nd value and choose value field settings.
5. Click on Show values as tab and follow below steps. (see image aside)
- Choose “% Difference from” from the drop down
- Select Month as base field
- Select (previous) as base item
- Click ok
This will show % changes with respect to previous month in the pivot report!
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.
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.
Finalized monthly report
Our final report looks like this:
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.
54 Responses to “Show monthly values & % changes in one pivot table”
Simply WOW!
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.
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!
Is there a way to duplicate this (the symbols bit) in excel 2003?
In excel 2003 to get the arrows, use Font wingdings 3 and =char(135) and =char(136)
Then apply conditional formatting as necessary.
You can do it with custom number formats. Do this after calculating % difference values:
this is how it looks:
See the example file here:
http://img.chandoo.org/pivot/show-monthly-sales-and-change-xl2003.xls
excellent, thanks dude (why my company don't upgrade, at least to 2007, I'll never understand).
because they don't know how Excel 2007/10/13 could bring into productivity comparing to Excel 2003... ;p
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
Its Simply AWESOME 🙂
Thanks for sharing !
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 ???
In the two pivots in the attached workbooks, how did you turn off the "Column Labels" and "Row Labels" dropdowns?
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.
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.
Hi Sally... Does your data have any blanks or missing months?
Can you retry this from the data in attached file
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
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
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
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 🙂
Hi,
every useful .....................
Amazing, Awesome, Interesting ....
Iam very much confused with the example..can some one share some easy way to do the same example.
Thanks!!
@Yasmeen...
Please download the sample file and use it...
Regards.
Pavi
thanks!
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.
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
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?
Just select manage rule > edit rule from conditional formatting & check show icon box from bottom right and apply...
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?
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
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.
To Fix this - go to Pivot Table -> Options
Options
Format
For Error Cells Show...Change the blank field to 0 (zero)
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
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
[…] 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 […]
Dear,
I am basic in Ms excel.
How to add points for products in an excel file?...
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.
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
Thanks Chadoo. I tried doing conditional formatting as you advised. It did not work. Could you please advice what could be the issue..
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!
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?
i have two year data (Jan to Dec) and i want to make a comparision sheet how to prepare
[…] or styles on the cell using Home > Cell Styles. Some example of zebra style formatting and increase/decrease icons and creating/editing […]
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?
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.
Thank you for these step by step instructions. They are fabulously clear and simple.
Thank you!
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
.
Thanks. It's very usefull. Works well.
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.
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?
never mind - made a mistake.
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
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.
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.