Excel Pivot Table - Running total that doesn't end at 100%
I've got a dataset in Excel for which I want to display the running total. However, I want the display to end before I get to 100% - see the below image:
![Pivot chart example]()
The right-most data element is for tickets open longer than 10 days (">10 Days"), but I don't want to include that particular value in the display chart, but instead have the chart end at 10 days and the lines end at 96% (or whatever they would end). The problem I'm having is that, when I uncheck the ">10 Days" data element in my pivot table to remove it from display, Excel re-calculates the running total so that the remaining data elements still add up to 100%.
In essence, I guess I'm looking for Excel to calculate the running total as a total of all data in the set, not just what I've chosen to display. If I uncheck items in the pivot table, I want them hidden from display but I want their values still included for the purpose of the summary.
Does that make sense? Is it possible? My initial research tells me it's not, and I'd considered formatting the pivot table properly, copying my data into a new static table and building a standard chart based on that instead. If I can do it inside the pivot chart so that it stays connected, that's ideal though.
If it matters, I'm using Excel 2013.
The right-most data element is for tickets open longer than 10 days (">10 Days"), but I don't want to include that particular value in the display chart, but instead have the chart end at 10 days and the lines end at 96% (or whatever they would end). The problem I'm having is that, when I uncheck the ">10 Days" data element in my pivot table to remove it from display, Excel re-calculates the running total so that the remaining data elements still add up to 100%.
In essence, I guess I'm looking for Excel to calculate the running total as a total of all data in the set, not just what I've chosen to display. If I uncheck items in the pivot table, I want them hidden from display but I want their values still included for the purpose of the summary.
Does that make sense? Is it possible? My initial research tells me it's not, and I'd considered formatting the pivot table properly, copying my data into a new static table and building a standard chart based on that instead. If I can do it inside the pivot chart so that it stays connected, that's ideal though.
If it matters, I'm using Excel 2013.
>> If I uncheck items in the pivot table, I want them hidden from display but I want their values still included for the purpose of the summary. Does that make sense? Is it possible?
Not in the way you describe - the % calculation will be based on the items that are there.
If you want such a % calculation, then there are two approaches:
1. To do that calculation, based on a total from somewhere else, in a column next to the PivotTable, and create a chart from the combination of the columns in the PivotTable and the new column. You don't necessarily need a new static table.
2. To import into each row the grand total, and then do the calculation based on that grand total. However, that’s harder to do if you want to do a cumulative figure instead of rolling it up in totals, so I don’t recommend it.
Not in the way you describe - the % calculation will be based on the items that are there.
If you want such a % calculation, then there are two approaches:
1. To do that calculation, based on a total from somewhere else, in a column next to the PivotTable, and create a chart from the combination of the columns in the PivotTable and the new column. You don't necessarily need a new static table.
2. To import into each row the grand total, and then do the calculation based on that grand total. However, that’s harder to do if you want to do a cumulative figure instead of rolling it up in totals, so I don’t recommend it.
MIght be worth trying to exploit a "feature" of excel graphs.
If you physically hide a column/row it will not show in a graph. So if you go to the pivot table that the graph is based on and in that sheet hide the column that is showing the >10 data, it shouldn't show in the graph.
You will have to select a cell outside of the pivot table to be able to hide the column though.
Thanks
Rob H
If you physically hide a column/row it will not show in a graph. So if you go to the pivot table that the graph is based on and in that sheet hide the column that is showing the >10 data, it shouldn't show in the graph.
You will have to select a cell outside of the pivot table to be able to hide the column though.
Thanks
Rob H
@Rob Unfortunately this doesn't seem to work with Excel 2013 and my pivot table, though I was briefly hopeful :)
@Phillip I ended up going with this suggestion and it seems to meet the needs, though I'm not a huge fan of having a manual step involved when I refresh the formulas. I'll leave it open for another day or so to make sure nobody else has a better solution and then accept. Thanks!
@Phillip I ended up going with this suggestion and it seems to meet the needs, though I'm not a huge fan of having a manual step involved when I refresh the formulas. I'll leave it open for another day or so to make sure nobody else has a better solution and then accept. Thanks!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial