I am trying to represent a combined bar and line graph, showing revenue as the bar graph and each expense type as a line graph. The idea to see where expenses visually are above revenue, the problem is when i try and filter on the pivot or via the slicer the expense type the entire graph returns to a bar graph as per Graph 2.
Please help and advise how i overcome this problem, preferably without using any VBA coding, but rather the normal functions/tools of pivot table/pivot charts??
Unfortunately, it's a known bug with no solution yet. If you choose one of the suggested layouts, this will not happen.
The only way is to use vba .
Hi Catalin, what do you mean when you say choose a suggested layout?
I am not very well versed in in VBA, therefore have always tried to stay away from it. Do you perhaps have some standard coding that that I can apply that will overcome this problem?
Hi Kevin
In Pivot Table Tools ribbon tab, in Design tab, there is a predefined set of Chart Styles. If you use one of those, excel will not change the settings after filtering.
Here is a good article describing the problem: http://peltiertech.com/pivot-chart-formatting-changes/
This tutorial might help too: https://www.myonlinetraininghub.com/create-regular-excel-charts-from-pivottables
Creating a regular chart is a good alternative, and you will also be able to use the pivot table slicers to filter the chart.
