@@MatthewMelton-t9s I still haven't figured it out either, but I'd love to have that 13th column at the end of the pivot chart be AVG of the 12 months. Help us @excelcampus
I do have a use case for the grand total appear on the graph. On a calculated field from the pivot table, this value works out as an average of all the values in the calculated field. This is useful as a straight line across a combo bar-stracked line chart. Thanks!
Yes, I’m very late to the party, but I likewise have a scenario where I want to use grand totals in a graph. I have 3 components that feed into a total over time (2016-2023) and the ideal way to show this would be an area graph with the grand total, and line graphs with the components within it. But within the PivotTable I can’t get the grand total into the chart. Hoping you’ve made a video on this at some stage in the last 3 years. But this was great info!
Hey Jon, this is the only video that shows how to do this so clearly and concisely. Thanks for getting straight to the point and for explaining a common issue in easy to follow steps!
I do have a scenario where I would like the grand total to appear on the pivot graph. It a column graph showing ratios of applicants to hires over time by applicant source. The grand total would be the ratio of all applies to hires regardless of source. Great tutorial.
As others have commented here, I needed to place TOTALS above the bars of a 100% stacked column. The work around that I came up with depends on including a TOTAL row (block) in the stack, making the cells of this row (block) size very very small in comparison to other numbers in the stack, giving it data labels (positioning them "on top"), choosing the contents of the data labels to be "values from an address" (an extra row in your table labeled "total-real" but not included in your original selection for the graph),; all done in a regular stacked column. When the above is completed, go to change chart and select 100% stacked column. You will have to do cosmetic touch-up of the labels. Not pretty but if your boss really wants the total on top of each bar in a 100% stacked column, s/he will be glad to buy you a cup of coffee. ps the only other site that I found that claimed how to do this: ua-cam.com/video/wixVdBQ0CqU/v-deo.html had an answer that I could not reproduce on a Mac/bootcamp/MS-Excel side, as it depened on a copying a format from one graph to another.
awesome video!! The case that works for me in the grand total is having a % avg of nps and I want to split the outperformers (above avg) with the bad performers (below avg) but this works as well
You asked for a scenario where you need the grand total in the chart: one example would be a utilization report showing the individual teams utilization and the combined utilization of these teams
Hello, You asked when the grand total would be useful. It could be when you have the chart or the pivot table splitting the total in categories. My example would be to see the revenue per country, it makes several curves alongside each other but then the Grand total is also important to see the overall yearly business
My favorite channel, today I saw one list on LinkedIn "Top 10 youtube Channels for Excel Learning". i wished to found the "excel campus" there and I found it.
I actually had to use grand totals data bar. I have a calculated field that gives me averages towards an year. I wanted to show not only monthly but YTD average. It would be nice to have the total shown as a bar but couldnt manage to do it on a pivot table. I used yout first solution thought since i habe to keep working with pivots.., thank you!
I actually have a situation where I need to have the grand total in the pivot chart. Long story short there are multiple people completing an action each day. So I use a line chart to show how each person is trending day by day (with the number of actions being the y value and the day being the x value). However I would also like to show the grand total of each day so that I can show a trend for everyone overall. I haven’t found a way to properly implement this yet while keeping it dynamic
Have the same problem.. i have a grand total which was the average of each column.. after some trial, it kinda work by moving the any of the axis data to the filter tab.. So it just leave out the total in the chart.. Useful if you have many column and want the axis to be in the slicer
I have added slicer filters to my pivot chart. The client wants to be able to toggle on and off the grand total so we can see how the smaller components are affecting the total. (In this one graph we are analysing a business group's revenues by its (different business sites). Two of the sites contribute considerably more than the other 20 contribute. Thus the 20 small ones are just a blur of lines. It would be great to show a grand total line and maybe also to be able to show the 20 smaller sites as a single line (subtotal).
I think i have a great example of where i would use total in the chart, that was actually what i was looking for when i found this video. I'm in HR-analytics and im currently making a chart that compares the %-ratio og women to men in different teams. However, I can't add a combined colum to show avarages across the teams since i don't know a way to add the grand total (except in a regular chart as you showed). All the colums would still end up showing 100%, so therefor a grandtotal would fit in perfectly here.
Though my comment is 2 years late, could you post a how to video if you ever figure this out? In my case, I need to show the grand totals only on a stacked pivot table.
Thank you the video, It worked for me. But I have filters in Chart, And I want to have change the numbers in text box when I change the filters in chart, without making any change in pivot table. Can I do that? Can you please help me with that?
So that Get Pivot table ON is helpful in case that pivot data shifts i guess. If you always have 12 months do you really need to set that to ON? it would stay where it is, right (the grand total value). Ok, u dont always have all 12. Got it. I have been trying to get that Grand total bar in the chart for a while now today and no luck, but this seems to be a decent kind of workaround. I need 2 grand totals for mine. One for this yr and one for last yr. i can just space them out. Ok, so u use a text box. This is cool. Best solution out there... by far. Sick stuff. But i guess there is no easy way to get that grand total to show in graph without that copy and paste exercise. Hmm. I have a $ by brand chart and i wanted to see the grand total, but displaying value is good. Just frustrating that it cant be easily displayed without that copy and paste of the pivot table maneuver. Thanks though. Q: is there a way to just bold the value there in that text box, not the entire thing, just the number?
Grand Total as a seperate BAR is necessary when you want to show Budget v/s Actual where you have Bud v/s Act Bars representing for item 1 item 2 Item 3
Thanks for your clear explanation! I do have a chart where I want to see the total: the data has our total personnel in FTE per month, with a lot of additional characteristics (M/F, salary range etc). I want use a line chart that shows the total workforce, and add slicers. I don't want to use stacked columns because I want to show more than one type of data (also percentages) in the same chart, and lines show the development over time much clearer. Is this possible in pivot charts? It seems I can't expand the data range to include the totals. Thank you!
Hi Jon, Do you have any tutorial showing how to add average underneath grand total where I grouped data per month. How can I have monthly average (not the daily average). Hope my question is clear.
My example of wanting grand totals in a chart is when I have (a) 5000 client records (b) revenue values for each record (d) revenue for each day in a 6 month time period. For this scenario I don't want the chart to show the trend over time for each of the 5000 records as this would be hard to view. I would like to view just the movement in the grand totals for all records on each day over time.
I have a line chart with the count of number of approvals, denials & other/non-decision. The question to answer: how have these counts changed over time? AND how has the number of requests changed over time? Yes the total will significantly taller than the rest... but that's what a 2nd vertical axis is for.
John, great tip. Question, you used char(10) to get the line break, I usually use the alt+enter key combo to insert the line break, is there a reason to prefer the char(10) over the alt+enter method? Also, I am often asked to create stacked column charts to compare multiple years of actuals by cost types. For example FY14 through FY19 actuals were the actuals include labor, material, subcontract, other, etc cost types. I would love to do this in a pivot chart but the inability to put a grand total above the year columns, results in me having to do it in a regular chart and adding the grand total as a line chart with no line or markers and adding the data label. Some of the slicers might cause the chart to not show values for all years (new or retired slicer elements), so putting a text box over each may not be feasible. Any thoughts on getting sub totals over stacked column pivot charts?
Hello good video !!! , my problem is : I have a week , and each day I have sub-items inside, I need to chart by day each week with stacks chart so I need to have the grant total for each day , so how can I use that in pivot chart ? Cheers
Hi! I have scenario to you, create a grand total as a data point. Scenario is a company you want analyze the staff training - the pivot chart in bars showing the percentage of staff who complete, incomplete and not started their training by department and you want to show last bar the grand total of the company. Any idea?
I need Grand Total in my Pivot Chart for a Stacked Column showing "Days to Ship" by Account. Each account is showing pct of orders shipped in 1 Day, 2 Days, 3+ Days. The Grand Total is showing Shipping for all accounts and would be useful in my chart. And since I'm using Stacked Percentage, it doesn't produce the Skewed Y Axis, as in your example.
Hi Jon, can you please help me with a problem. I used this method to display my weekly revenue. But when I add or delete a week in the pivot table the graph displays the revenues completely wrong
Hello! I have a scenario in which I need the total value in the graph. I have different segments being analyzed per year. So I did stacked columns with segments per year. But I would like to also show in the graph the total for year above the stacked columns. I don't know how to do this! Can you help me please?
hello sir, thanks for this video. facing a problem in excel365, the shape when refers to pivot table shows a red wiggly line under the referenced value. Could you please help me out?
Why I need Grand Total along with other Data on the Chart? 1. We have a Filter of Name of Students 2. We have Various Test-IDs and each test has marks of 3-4 subjects. Now on a Pivot Chart we need to show the %ages of various subjects of students performance and also the Grand Total %age for respective Test. Here The Grand Total are in each Row. How this can be achieved?
Helo, I want to add a calculated column field in pivot table which gives me a constant average over the months (grand total/no of months) . The pivot table is like this: Jul 11.79159 Aug 76.98681 Sep 108.87933 Oct 46 Nov 24 Dec 1.00384 Grand Total 268.66157 How can I get a column in excel pivot table that fills 268.66157/6 for each months?
Great tutorial! I have an example where the "total column" in the pivot chart graph is required (for me). My chart is formatted in mm:ss format. These figures are also averaged i.e. each row of data is an average time taken for each user of our system. I then configure the grand total column to also indicate the time average for all users. The grand total time average is typically lower than the highest indiviual row average of individual users e.g. user 1 takes 01:00 minute, user 2 takes 05:00 minutes. This is an average of 03:30 minutes. So in this instance, my grand total average column is (03:30), which is less than the highest individual user average (05:00). This would not skew the chart, so could do with having this data displayed within it? Any ideas please?
Hello Harsha, we are sorry about the trouble. You may follow this link for the blog post: www.excelcampus.com/pivot-tables/grand-total-pivot-chart/ and under the "Download the Excel File" you may click the green link to download the excel file. Hope that helps. Thanks! ;-)
Hi Jon, excellent videos. I am yet experiencing issues on my excel interpreting qualitative data for my research relating to Global disasters affecting healthcare staff attending hospitals. Are you able to help? I can provide my email if required.
Hi Vanguri - I'm sorry you're not getting any data. Try clicking the Grand Total Pivot Chart link in the description and scroll down on that page to where you can download the file. I hope that helps!
Per request: Grand Total function would be useful on averages. So you can see how the entire group average compares to individual items in the list.
Exactly what I wanted to comment.
Still haven't found this out....
@@MatthewMelton-t9s I still haven't figured it out either, but I'd love to have that 13th column at the end of the pivot chart be AVG of the 12 months. Help us @excelcampus
I do have a use case for the grand total appear on the graph. On a calculated field from the pivot table, this value works out as an average of all the values in the calculated field. This is useful as a straight line across a combo bar-stracked line chart. Thanks!
Yes, I’m very late to the party, but I likewise have a scenario where I want to use grand totals in a graph. I have 3 components that feed into a total over time (2016-2023) and the ideal way to show this would be an area graph with the grand total, and line graphs with the components within it. But within the PivotTable I can’t get the grand total into the chart. Hoping you’ve made a video on this at some stage in the last 3 years. But this was great info!
@paulbarry3400 I am searching for the same answer right now , lol . Have you found it ?
Hey Jon, this is the only video that shows how to do this so clearly and concisely. Thanks for getting straight to the point and for explaining a common issue in easy to follow steps!
Thanks so much! Would u be able to show us how to add the totals as a trend line with value labels for each category in a Pivot Chart ?
Yes, i have the same question. Adding grand total to a trend line type of pivotchart is definitely useful if you want to see total spend
Thank you, sir, your explanation is smooth and very helpful, please don't stop.
This is exactly the information that I was looking for. Thank you for showing how to solve this kind of problem.
This is just awesome, thank you so much! It's utterly unbelievable Excel charts don't include that out of the box, but hey...
Extremely useful video. i wanted this in my Charts since long. Thumbs Up !!
Estuve buscando en muchos videos en español una solución como la que muestra, excelent!!!. Congratulations!!
Thanks, Jon. Was able to successfully finish off my dashboard, thanks to this video! Keep up the good work! LL
I do have a scenario where I would like the grand total to appear on the pivot graph. It a column graph showing ratios of applicants to hires over time by applicant source. The grand total would be the ratio of all applies to hires regardless of source. Great tutorial.
did you manage to do it
As others have commented here, I needed to place TOTALS above the bars of a 100% stacked column. The work around that I came up with depends on including a TOTAL row (block) in the stack, making the cells of this row (block) size very very small in comparison to other numbers in the stack, giving it data labels (positioning them "on top"), choosing the contents of the data labels to be "values from an address" (an extra row in your table labeled "total-real" but not included in your original selection for the graph),; all done in a regular stacked column. When the above is completed, go to change chart and select 100% stacked column. You will have to do cosmetic touch-up of the labels. Not pretty but if your boss really wants the total on top of each bar in a 100% stacked column, s/he will be glad to buy you a cup of coffee. ps the only other site that I found that claimed how to do this: ua-cam.com/video/wixVdBQ0CqU/v-deo.html had an answer that I could not reproduce on a Mac/bootcamp/MS-Excel side, as it depened on a copying a format from one graph to another.
awesome video!! The case that works for me in the grand total is having a % avg of nps and I want to split the outperformers (above avg) with the bad performers (below avg) but this works as well
Love your videos and explanations. Awesome!
John thanks for the free lessons.
You asked for a scenario where you need the grand total in the chart: one example would be a utilization report showing the individual teams utilization and the combined utilization of these teams
Hello,
You asked when the grand total would be useful. It could be when you have the chart or the pivot table splitting the total in categories. My example would be to see the revenue per country, it makes several curves alongside each other but then the Grand total is also important to see the overall yearly business
My favorite channel, today I saw one list on LinkedIn "Top 10 youtube Channels for Excel Learning". i wished to found the "excel campus" there and I found it.
Nice trick man. Thank you
Excellent , really enjoyed It , Thank you
I actually had to use grand totals data bar. I have a calculated field that gives me averages towards an year. I wanted to show not only monthly but YTD average. It would be nice to have the total shown as a bar but couldnt manage to do it on a pivot table. I used yout first solution thought since i habe to keep working with pivots.., thank you!
Jon,thanks for sharing level tricks
I actually have a situation where I need to have the grand total in the pivot chart. Long story short there are multiple people completing an action each day. So I use a line chart to show how each person is trending day by day (with the number of actions being the y value and the day being the x value). However I would also like to show the grand total of each day so that I can show a trend for everyone overall. I haven’t found a way to properly implement this yet while keeping it dynamic
did you manage to do it
Have the same problem.. i have a grand total which was the average of each column.. after some trial, it kinda work by moving the any of the axis data to the filter tab.. So it just leave out the total in the chart.. Useful if you have many column and want the axis to be in the slicer
I appreciate this so much! Thank you.
Really helpful thanks Jon
SUPER HELPFUL!
YOURE SO HELPFUL!!!
You're welcome, Novi! 😀
Amazing - Always thumbs up
Excellent! Thank you so much.
very cool trick, thanks for sharing, Jon!
Jon, you are the best!
Thanks Frank! I appreciate your support! 🙂
God damn you saved my life. AWESOME!
Thanks Mr Jones for this new tip 👌👌
Very helpful
Thanks
Thank you so much!
Thank you Jon.
Hey! great video! if I want the grand total on the chart like you showed, how can I do that?
Really Helpful Tips Thank You Jon :)
I have added slicer filters to my pivot chart. The client wants to be able to toggle on and off the grand total so we can see how the smaller components are affecting the total. (In this one graph we are analysing a business group's revenues by its (different business sites). Two of the sites contribute considerably more than the other 20 contribute. Thus the 20 small ones are just a blur of lines. It would be great to show a grand total line and maybe also to be able to show the 20 smaller sites as a single line (subtotal).
Many thanks Jon!
Thanks Zahid! 🙂
I think i have a great example of where i would use total in the chart, that was actually what i was looking for when i found this video.
I'm in HR-analytics and im currently making a chart that compares the %-ratio og women to men in different teams. However, I can't add a combined colum to show avarages across the teams since i don't know a way to add the grand total (except in a regular chart as you showed). All the colums would still end up showing 100%, so therefor a grandtotal would fit in perfectly here.
Though my comment is 2 years late, could you post a how to video if you ever figure this out? In my case, I need to show the grand totals only on a stacked pivot table.
Thank you so much!!!!!
Great tip - thanks
Thanks so much.
Perfectly explained
Thank you! 😀
Amazing!!!! Thank you! :-)
Thank you the video, It worked for me. But I have filters in Chart, And I want to have change the numbers in text box when I change the filters in chart, without making any change in pivot table. Can I do that? Can you please help me with that?
Excellent boss
Awesome 👍
Thank you for the video, one question only, how do I choose other currencies other than $ in the formula?
Great.. Thanks a lot. 👍
thanks brother
Great Videos!!!!!
Thanks! 😀
Thanks fam
So that Get Pivot table ON is helpful in case that pivot data shifts i guess. If you always have 12 months do you really need to set that to ON? it would stay where it is, right (the grand total value). Ok, u dont always have all 12. Got it. I have been trying to get that Grand total bar in the chart for a while now today and no luck, but this seems to be a decent kind of workaround. I need 2 grand totals for mine. One for this yr and one for last yr. i can just space them out. Ok, so u use a text box. This is cool. Best solution out there... by far. Sick stuff. But i guess there is no easy way to get that grand total to show in graph without that copy and paste exercise. Hmm. I have a $ by brand chart and i wanted to see the grand total, but displaying value is good. Just frustrating that it cant be easily displayed without that copy and paste of the pivot table maneuver. Thanks though. Q: is there a way to just bold the value there in that text box, not the entire thing, just the number?
Grand Total as a seperate BAR is necessary when you want to show Budget v/s Actual where you have Bud v/s Act Bars representing for item 1 item 2 Item 3
Brilliant
Great video, how do you link multiple grand totals to a text box?
Thanks for your clear explanation! I do have a chart where I want to see the total: the data has our total personnel in FTE per month, with a lot of additional characteristics (M/F, salary range etc). I want use a line chart that shows the total workforce, and add slicers. I don't want to use stacked columns because I want to show more than one type of data (also percentages) in the same chart, and lines show the development over time much clearer. Is this possible in pivot charts? It seems I can't expand the data range to include the totals. Thank you!
Hi Jon, Do you have any tutorial showing how to add average underneath grand total where I grouped data per month. How can I have monthly average (not the daily average). Hope my question is clear.
My example of wanting grand totals in a chart is when I have (a) 5000 client records (b) revenue values for each record (d) revenue for each day in a 6 month time period. For this scenario I don't want the chart to show the trend over time for each of the 5000 records as this would be hard to view. I would like to view just the movement in the grand totals for all records on each day over time.
I have a line chart with the count of number of approvals, denials & other/non-decision. The question to answer: how have these counts changed over time? AND how has the number of requests changed over time? Yes the total will significantly taller than the rest... but that's what a 2nd vertical axis is for.
Jon, this is so great but no matter what I do, my grand total continues to round up. What am I doing wrong??
For the total in chart data, it would be beneficial if we were to be using the average instead of the sum. Do you know how to display that?
Thanks. Would you pls advise me how to turn its million instead of using $dollar sign?
John, great tip. Question, you used char(10) to get the line break, I usually use the alt+enter key combo to insert the line break, is there a reason to prefer the char(10) over the alt+enter method?
Also, I am often asked to create stacked column charts to compare multiple years of actuals by cost types. For example FY14 through FY19 actuals were the actuals include labor, material, subcontract, other, etc cost types. I would love to do this in a pivot chart but the inability to put a grand total above the year columns, results in me having to do it in a regular chart and adding the grand total as a line chart with no line or markers and adding the data label. Some of the slicers might cause the chart to not show values for all years (new or retired slicer elements), so putting a text box over each may not be feasible. Any thoughts on getting sub totals over stacked column pivot charts?
Hello good video !!! , my problem is : I have a week , and each day I have sub-items inside, I need to chart by day each week with stacks chart so I need to have the grant total for each day , so how can I use that in pivot chart ? Cheers
Hi! I have scenario to you, create a grand total as a data point. Scenario is a company you want analyze the staff training - the pivot chart in bars showing the percentage of staff who complete, incomplete and not started their training by department and you want to show last bar the grand total of the company. Any idea?
I need Grand Total in my Pivot Chart for a Stacked Column showing "Days to Ship" by Account.
Each account is showing pct of orders shipped in 1 Day, 2 Days, 3+ Days.
The Grand Total is showing Shipping for all accounts and would be useful in my chart. And since I'm using Stacked Percentage, it doesn't produce the Skewed Y Axis, as in your example.
here is the seniors that the grand total could be used in the chart: how about the 12-month average in the pivot chart?
already buy a power bi course
Hi Jon, can you please help me with a problem. I used this method to display my weekly revenue. But when I add or delete a week in the pivot table the graph displays the revenues completely wrong
Hello, how do you fix the grand total if it doesn't update per slicer input? Please let me know! :)
This will work on the Power chart also?
I am facing an error. When I insert a text box and apply formula, the box does not show it.
Hello! I have a scenario in which I need the total value in the graph. I have different segments being analyzed per year. So I did stacked columns with segments per year. But I would like to also show in the graph the total for year above the stacked columns. I don't know how to do this! Can you help me please?
Hi Jon, how to create pivot chart with cumulative by months continue over more than a year?
What if the grand total is in percentage and we need to show it as a part of the chart points itself
hello sir, thanks for this video. facing a problem in excel365, the shape when refers to pivot table shows a red wiggly line under the referenced value. Could you please help me out?
Hi! How can I take from the bar chart the Grand Total value? I just can't
Actually, i need that grand total as part of Pivot chart, i have some requirement to have that.
how to add Pivot table analyze and design to the excel menu?
what if i wanna make a chart based on the grand total from pivot table, how can I do that ?
How to add +10 In grand total in pivot table for tally the total amount only
It seems this trick does not work on Power Pivot Charts. Any idea ?
Why I need Grand Total along with other Data on the Chart?
1. We have a Filter of Name of Students
2. We have Various Test-IDs and each test has marks of 3-4 subjects.
Now on a Pivot Chart we need to show the %ages of various subjects of students performance and also the Grand Total %age for respective Test.
Here The Grand Total are in each Row. How this can be achieved?
The scenario is this one for me : the grand total is an average (the values are percentages). So I'm still stuck.
8:06 how to show grand total column in chart like this?
Helo, I want to add a calculated column field in pivot table which gives me a constant average over the months (grand total/no of months) . The pivot table is like this:
Jul 11.79159
Aug 76.98681
Sep 108.87933
Oct 46
Nov 24
Dec 1.00384
Grand Total 268.66157
How can I get a column in excel pivot table that fills 268.66157/6 for each months?
At 1:38, what does "Generate GetPivotData" do?
Great tutorial! I have an example where the "total column" in the pivot chart graph is required (for me). My chart is formatted in mm:ss format. These figures are also averaged i.e. each row of data is an average time taken for each user of our system. I then configure the grand total column to also indicate the time average for all users. The grand total time average is typically lower than the highest indiviual row average of individual users e.g. user 1 takes 01:00 minute, user 2 takes 05:00 minutes. This is an average of 03:30 minutes. So in this instance, my grand total average column is (03:30), which is less than the highest individual user average (05:00). This would not skew the chart, so could do with having this data displayed within it? Any ideas please?
did you find a solution ?
@@yoyoyoyoyo6714 Afraid not. I still need to have the average in a separate box on the graph.
@@jeffparker3241 thanks thats si annoying
Average of the Averages is where the Grand total as a end bar would be good...
John please help me with the link you have posted, i am unable to get the file
Hello Harsha, we are sorry about the trouble. You may follow this link for the blog post: www.excelcampus.com/pivot-tables/grand-total-pivot-chart/ and under the "Download the Excel File" you may click the green link to download the excel file. Hope that helps. Thanks! ;-)
subtotals?
Hi Jon, excellent videos. I am yet experiencing issues on my excel interpreting qualitative data for my research relating to Global disasters affecting healthcare staff attending hospitals. Are you able to help? I can provide my email if required.
Hi Sir
am not getting any data
Hi Vanguri - I'm sorry you're not getting any data. Try clicking the Grand Total Pivot Chart link in the description and scroll down on that page to where you can download the file. I hope that helps!