I have a chart which is linked to a pivot table & also controlled through multiple slicers working on the same pivot data, however whenever I am refreshing my data (after making small changes in it such as revising a value, adding a row etc...) all the formatting which i did with the chart goes off the track. I want some data specifically plotted on primary axis (as column chart) & some on secondary axis (as line chart), whereas after refreshing all the data values get plotted on the primary axis (as column chart only).
Any help on the above will be highly appreciated.
Unfortunately this is a known bug in PivotCharts.
You could try saving your formatting etc. as a Chart Template and then apply the template again each time the formatting gets messed up. The other options are to use a regular chart linked to either a manual chart table, or use dynamic named ranges linked to the PivotTable as your 'regular' chart source data.
In the past, I've used a macros to automatically update the chart. You will need to create a template first. You can set up your macros to update the chart once data has been entered or when the spreadsheet first opens, or whenever you want. This is what I've used in the past.
ActiveSheet.ChartObjects("Chart 1").Select
ActiveChart.ApplyChartTemplate ("C:Location Name of templateKPI.crtx")
