John has attended:
Excel Pivot Tables course
Excel pivot charts
I have a stacked area chart that looks at data based on years and can be filtered by country. I am happy with the automatic colouration of the various layers but I would like the bottom layer to be black. I can change this manually which works fine. My problem occurs when I select a separate country through the filter the bottom layer reverts back to the automatic colour. Is there a way of writing a macro or something similar, that would change this layer to black every time I change the filter.
Thank you.
RE: Excel pivot charts
Hi John
Thank you for your question
You could try saving your formatted chart as a template for future charts.
Once you have set up the chart to suit your purposes right click on it and choose save as template.
You can get to your templates to reformat your chart form from the PivotChart Tools Ribbon on the Design Tab. Click the change chart type button to load from the templates folder.
let me know if that helps
Kind regards,
Andrew
RE: Excel pivot charts
Andrew,
That works a treat for me if I'm only changing filters. There is also the problem that when the VALUES get changed (net or gross etc.) even the template does not work. There is also the problem that I have to distribute this to my boss who may then go and send it off to someone else etc. I need something that is internal to the chart so that it will work for anyone that opens the chart and does not matter what value they choose. Sorry I should have mentioned the VALUES part. Sorry but I'm still waiting for a solution.
RE: Excel pivot charts
Andrew can you look at my last response, please?
RE: Excel pivot charts
Hi John
Apologies for the delayed response while we looked into your issue.
I've enclosed a workbook that has code attached that should deal with your issue. As you choose different values, the chart's first series (on the bottom of the area chart) should remain black.
To view the code, press ALT+F11 and double-click Sheet1. It is a special macro that is triggered whenever the PivotTable is updated. You may have to make some modifications to your own version of the code if there is more than one chart on the worksheet.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
