- Joined
- Jan 2, 2013
- Messages
- 3
- Reaction score
- 0
Hello!
I was wondering if anyone could help me with a problem I'm trying to solve on Excel.
Basically, I'm using a Pivot Chart and Slicers to create an Area graph, with cost on the y axis and time on the x axis going from 2012 to 2020. It works brilliantly as a dynamic tool, as you can filter by several criteria and the graph updates itself automatically. It's great to have all your results in one place rather than needing to produce loads of graphs for all different filtering scenarios.
I have one issue (unfortunately quite a major issue) which is that there are too many data sets being displayed (about 90 bands on the chart), which means the legend is hugely long, and the graph shows too much info to be visually digested.
What I ideally want is for the 'top' data sets (i.e. those which represent the majority of the combined cost on the chart) to be displayed, and group all the other sets into one data set called 'others'. The method for doing this might just be to show the top 10 data sets, then group all the rest. Even better would be to pull out the top data sets which make up 80% of the combined cost, and group those that remain. AND for this ranking to change as you edit the pivot table. A lot to ask I guess. Happy to use VBA if necessary.
The only way I can think of doing this is to copy paste from the pivot table into another sheet, manipulate the data, then make each graph manually. But then this loses all the advantages of the pivot chart!
If anyone has any thoughts, I'd greatly appreciate your input.
Many thanks
Sam
I was wondering if anyone could help me with a problem I'm trying to solve on Excel.
Basically, I'm using a Pivot Chart and Slicers to create an Area graph, with cost on the y axis and time on the x axis going from 2012 to 2020. It works brilliantly as a dynamic tool, as you can filter by several criteria and the graph updates itself automatically. It's great to have all your results in one place rather than needing to produce loads of graphs for all different filtering scenarios.
I have one issue (unfortunately quite a major issue) which is that there are too many data sets being displayed (about 90 bands on the chart), which means the legend is hugely long, and the graph shows too much info to be visually digested.
What I ideally want is for the 'top' data sets (i.e. those which represent the majority of the combined cost on the chart) to be displayed, and group all the other sets into one data set called 'others'. The method for doing this might just be to show the top 10 data sets, then group all the rest. Even better would be to pull out the top data sets which make up 80% of the combined cost, and group those that remain. AND for this ranking to change as you edit the pivot table. A lot to ask I guess. Happy to use VBA if necessary.
The only way I can think of doing this is to copy paste from the pivot table into another sheet, manipulate the data, then make each graph manually. But then this loses all the advantages of the pivot chart!
If anyone has any thoughts, I'd greatly appreciate your input.
Many thanks
Sam