Bottom Line: Learn how the Average is calculated in the Grand Total and Subtotal rows or columns of a pivot table.
Skill Level: Intermediate
Video Tutorial
Download the Excel File
If you'd like to follow along with the video using the same file I use, you can download it here:
Precise Averages
If you've ever made a photocopy of a photocopy, you know that it never comes out quite as clear as a photocopy of an original. The sharp edges become a bit duller. The same is true when we take the average of averages. It's not as accurate as taking an overall average of the original data that the averages came from.
This is a question that comes up often when we are looking at the Grand Total row of a pivot table.
The example above shows a pivot table with a summary report of Average Qty by Region. If you select all of the cells in the Values area (for each region), you'll notice that the Average calculation in the status bar is different from the result in the Grand Total row of the pivot table.
Why is this?
Understanding Pivot Table Calculations
The Grand Total average in the pivot table is adding up all of the cells in the quantity column of the data set and dividing it by the total number of orders.
The status bar average, however, doesn't take into account that the West Region had four times the number of orders as the East Region. Instead, it gives them equal weight when it averages the four regions' averages together, skewing the number down.
It's important to understand that each cell in the values area of the pivot table is doing a calculation on the source data. This includes the subtotal and grand total rows or columns.
The calculation is based on the filter context for each cell. In this example, the Grand Total row is calculating the Average of all of the cells in the Qty column of the source range. It is NOT actually taking the average of the cells in the Values area of the pivot table.
Totals for Other Calculation Types
Another way to look at this is with the Count calculation type. If the pivot table did the calculation based on the cells in the values area, the Total Count of Qty in cell C8 would be 4 because there are 4 numbers in the cells above.
Of course this would be incorrect. Instead, the pivot table does the Count calculation on the source data range based on the current filter context applied to the pivot table. If the pivot table has a filter applied (in the filters area or with a slicer), then the result in the Grand Total Row or Column will reflect that.
Tying out the Numbers
One easy way to see this is to use a Table for the source data range of your pivot table, and then tie out the numbers with the Total Row.
You can turn on the Total Row by selecting any cell in the Table, going to the Design tab on the Ribbon and selecting the Total Row checkbox.
By default, the total row will show the sum of all the cells using the SUBTOTAL function. I explain that more in this post: The SUBTOTAL Function for Excel Tables Total Row Explained. But you can change the calculation to an average by choosing Average from the drop-down menu accessed by clicking on the small down arrow.
You can then filter the source data range for the same filters that are applied to the pivot table. The calculations in the Total Row of the table should match the results of the Grand Total Row/Column of the pivot table.
Free Training Webinar on the Power Tools
Not sure what a pivot table is or how to use them?
Right now I'm running a free training webinar on all of the Power Tools in Excel. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.
It's called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.
You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.
The webinar is running at multiple days and times. Please click the link below to get registered and save your seat.
Click Here to Register for the Free Webinar
Conclusion
I hope this brief explanation helps to clear up why the Grand Total average in a pivot table can be different from the average in the status bar. It's important to have a precise average based on the original data set instead of an average of averages.
Now, if you can think of a reason why you'd prefer to have an average of averages, I'd love to hear it. Leave a comment below with that reason, or with any other comments or questions you might have!
Great explanation, Jon! I don’t use AVERAGE much in my PVT tables, but this is good information to know if/when I do use them.
Thank you Windle! Happy to hear it helped. 🙂
What I can’t figure out is how to make a pivot table show you the difference in amounts when comparing two or more years data.
Hi Yvonne,
Great question! You can put the Year field in the column or rows area of the pivot table, then change the calculation type to Difference From… Year field and Previous item.
I’m not sure we have any training on this exact technique but I’ll add it to the list for future posts.
I hope that helps. Thanks again and have a nice weekend! 🙂
Jon, thanks for explaining the pivot table average is actually a weighted average!
Jon thanks for the explanation. I was recently using pivot tables for calculating average work times for the staff and did not now why these differences appeared. An additional question I still haven’t resolved maybe you could do a video about it, when I change the time format from a.m-p.m to military hours and I scan an employees bar code instead of bringing me dd-mm-yyyy it brings mm-dd-yyyy put it actually confuses the date and brings me the value month as the day and the value of day as the month leading to wrong dates.
Great explanation. Unfortunately I would need the other average (average of averages you call it) in my pivot and haven’t been able to do so yet.
The reason is that my pivot shows a sum of open cases per day and I grouped the dates per week which then I would like to show as average. So the sum of the cases from that week divided by 5 (as in my case we have 5 working days in a week). When I try this with the built-in Average it gives the the incorrect solution. Do you have any tips how to get there?
I’ve got batches of products with the same product code, but each product has a different effective and expiry date associated with them.
I want a pivot table to show me the average number of days an individual product code within each batch lasts for. (I have thousands of unique product codes).
So I want the average per product code, not the entire data set.
Thanks for explaining, but there are definitely situations where the pivot table average is not useful.
Jon,
I have a need for for the average of averages, I have a monthly participation rate in a pivot table. I am try to calculate the weighted average, year to date, of the monthly participation rate.
Thanks for explaining the average calculation: I found out the hard way already, but was still helpful having it explained so clearly with the examples. But I need an average over a sum.
My source data contains multiple production orders with duration per order. I summarize value field (duration) per week (columns in pivot). I need the grand total to reflect the average of summarized values per week.
I want to column average in Pivot table
You may consider average of averages when certain values in your database are not between tolerance specifications and are not representative.
Hi Jon, thanks for your explanation… It’s really helpful for me… Is it possible get the Grand Total as Sum (Avg (Qty)) in Pivot?… That is, as per your table, I need the Grand Total as 9,290 (which is sum of 1663 + 1558 + 1457 + 4612).
Thanks in Advance!!!
Thank you so much, this was most helpful
Hi Jon I had been following you for 2 years and had learnt a lot from you. Thank you.
I recenly come across this challenge and is hoping you or any one in this community can provide me a solution.
I had built a pivot table based on numbers of orders, and the % of the grand total of order for each sales.
what I need now is to show the average percentage amount all the sales people.
I can of course set up a formula, but the list of sales is constantly changing and they are also grouped by manager who I do not want to be included in the avg. Is there any way to resolve that?
This is the formula to calculate from top to bottom of the pivot table, which means including the avg % of the manager, and I don’t want it:
=AVERAGE(D17:D65) (Result: 6.12%)
This is what I want with only the sales selected:
=AVERAGE(D18:D27,D29:D41,D43:D53,D55:D64) (Result: 2.27%)
But when we add or take of any sales person, the selection referenced on the above formula won’t change and might select the value I do not want to include
Help~