Multiple charts with consistent colours throughout

Question

I have 3 very intricate pivot charts displaying data (from the same data source) and the data hierarchy is as follows:

Unit

Division

Subdivision

The charts have slicers with all the units, divisions and subdivisons, and so there are many variables as to how the charts look once filtered. I was wondering if it's possible to make it so that the units are always the same colour on all 3 charts. For example, unit1 is always red, unit2 is always yellow, etc.. on all 3 charts.

If possible I'd prefer a solution without VBA as I've never used it and find it very confusing.

Thanks

Answer 1

Hello,

re 1:  I copied the pivot table and paste with paste special > values. Then I copied and pasted each unit's values into a new column. This can be automated with VBA or formulas, though.

re 2: The pivot table and any slicers applied to it will not have any impact on this table and chart. If you use different filters, you will need to copy and paste the resulting pivot table again.  This can be automated with VBA. 

re 3. Each column is a series. Excel plots each series in a different color. The color depends on the series number, not the series title. The first series will be colored with Accent1 of the current theme. Series 2 will get Accent2 of the current theme. 

If series are deleted after the chart has been created, each series will retain the color it was originally assigned.  

If you want a specific unit  to always have a specific color, you may want to ensure that the unit is always the same series number.

Answer 2

Thanks for your answer Teylyn. I have a few follow-up questions:

1. How did you separate the values into 3 different columns in the pivot table? I tried copying and pasting the original value column and deleting necessary data to obtain that format but excel wouldn't let me delete the data within the pivot table.
2. Will those colours remain constant once the chart is filtered? Ex: if I had a slicer and chose to only view "Pacific" and "UK" instead of all the data, would the blue and orange remain the same colours?
3. With that format will the separate ranges be automatically coloured differently or do I have to do it for each individual one? The reason I ask is that my real data source is way too large to do individually.

Answer 3

Hello,

as far as I understand your question, you would like to change the color of individual data points in a pivot chart that has only one series. In a pivot chart you would need to format the data points manually or with VBA, although it might be challenging to identify the data point with VBA.

A non-VBA solution would require that the data from the Pivot Tables be copied to another range in the workbook. With the correct layout of the data and a Stacked Column chart, the data points can be colored individually. The screenshot illustrates the data layout for a stacked column chart.

Answer 4

Sorry, heres a picture of an example similar to my situation. It wouldn't let me "vary colours by point", but in my actual report I have that option selected.

Basically what I want is for anything under "Asia" to be coloured red, anything under "Europe" to be coloured yellow, and anything under "North America" to be coloured blue - on both charts even when they are filtered differently.

My actual report has thousand of rows of data and numerous filter options, so this is a very small sample in comparison.

I hope this makes things clearer.

Answer 5

Hi,

Your question is not clear.  Please share a screenshot.