SOLVED

Synchronize the colors of multiple PivotCharts

%3CLINGO-SUB%20id%3D%22lingo-sub-1256020%22%20slang%3D%22fr-FR%22%3ESynchronize%20the%20colors%20of%20multiple%20PivotCharts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1256020%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20an%20automated%20Excel%20system%20that%20allows%20with%20a%20simple%20database%20extraction%20to%20feed%20a%20report%20mainly%20composed%20of%20pivot%20charts.%20I%20added%20segments%20to%20release%20the%20data%20by%20weeks%2Fmonths%2Fyears.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThe%20problem%20is%20that%20when%20I%20filter%20my%20report%20the%20colors%20of%20the%20categories%20on%20the%20PivotCharts%20no%20longer%20match%20each%20other.%20In%20addition%2C%20some%20PivotCharts%20have%20different%20categories%20from%20others.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20i%20would%20like%20to%20assign%20a%20color%20to%20each%20category%20which%20will%20not%20change%20when%20I%20filter%20my%20report.%20But%20I%20don't%20know%20how.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22tlid-results-container%20results-container%22%3E%3CDIV%20class%3D%22tlid-result%20result-dict-wrapper%22%3E%3CDIV%20class%3D%22result%20tlid-copy-target%22%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Ethank%20you%20in%20advance%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1256020%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1256322%22%20slang%3D%22en-US%22%3ERe%3A%20Synchronize%20the%20colors%20of%20multiple%20PivotCharts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1256322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F596481%22%20target%3D%22_blank%22%3E%40Rb_01%3C%2FA%3E%26nbsp%3BIf%20you%20use%20PowerPivot%20and%20a%20properly%20designed%20data%20model%20you%20can%20set%20the%20pivottables%20to%20display%20rows%20with%20no%20data%20(they%20would%20appear%20in%20a%20chart%20as%20zero-width%20bars).%20Filtering%20then%20does%20not%20affect%20the%20displayed%20categories%20and%20hence%20the%20colors%20stay%20the%20same.%20See%20attached%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1256493%22%20slang%3D%22fr-FR%22%3ERe%3A%20Synchronize%20the%20colors%20of%20multiple%20PivotCharts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1256493%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%20that's%20exactly%20what%20i%20needed%2C%20you%20have%20my%20thanks.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone,

 

I created an automated Excel system which allows with a simple database extraction to feed a report mainly composed of pivot charts. I added segments to sort the data by weeks / months / years.


The problem is that when I filter my report the colors of the categories on the PivotCharts no longer match each other. In addition, some PivotCharts have different categories from others.

 

So i would like to assign a color to each category which will not change when I filter my report. But I do not know how.

 

Any idea ?

 

thank you in advance
2 Replies
Highlighted
Best Response confirmed by Rb_01 (New Contributor)
Solution

@Rb_01 If you use PowerPivot and a properly designed data model you can set the pivottables to display rows with no data (they would appear in a chart as zero-width bars). Filtering then does not affect the displayed categories and hence the colors stay the same. See attached example.

Highlighted

@Jan Karel Pieterse that's exactly what i needed, you have my thanks.