Charts changing section colors with slicers

[kalvinswisher]

Hello!

I have an interesting problem that I am occurring. I have created some charts that is looking at racial data through pivots. When using some slicers to within the data that I have, it takes away some of the racial information that is located. You can see with the images below. While doing this, this also changes the colors that are indicated for each of the racial indicators. Does anyone know how to keep the racial indicators one consistent color when using slicers? Would I have to use some type of VBA coding to solve this issue? You can see what I am talking about without using the slicer where "White" is shaded green, but now with using the slicer of M/F (male or female) we can see that "White" is now blue.

 

[mrshl9898]

There's a useful write up here about the issue as well as a couple of workarounds. Solution 2 should be pretty straightforward, but let us know if you get stuck.

Pivot Chart Formatting Changes When Filtered - Peltier Tech

When you apply a filter or slicer to a PivotChart the custom formatting can change with each change in the filter. Here's what you can do about it.

peltiertech.com

 

[kalvinswisher]

There's a useful write up here about the issue as well as a couple of workarounds. Solution 2 should be pretty straightforward, but let us know if you get stuck.

Pivot Chart Formatting Changes When Filtered - Peltier Tech

When you apply a filter or slicer to a PivotChart the custom formatting can change with each change in the filter. Here's what you can do about it.

peltiertech.com

Thanks for the reply, but I have tired that website that you attached. Solution 2 doesn't help me out in my situation because now it is picking up "Grand Total" in the chart when using the slcicer as shown below. The previous way that I have done it takes away those grand totals, which is what I would like, but I always end up with colors changing when using "F" in the slicer option (back to the photos in my original post). Would you know of a VBA that might be able to solve this issue? Is there a way to automatically assign colors to categorical values?

 

[mrshl9898]

Can't see why this wouldn't work without Grand Total. Just delete it or change the Chart range.

I've done VBA before to change the Colors of chart points before but I haven't seen them referred to by name. Just consecutive numbers 1,2,3etc.

Hopefully someone will kindly prove me wrong

 

[kalvinswisher]

Can't see why this wouldn't work without Grand Total. Just delete it or change the Chart range.

I've done VBA before to change the Colors of chart points before but I haven't seen them referred to by name. Just consecutive numbers 1,2,3etc.

Hopefully someone will kindly prove me wrong

Yeah, sorry - The "Grand Total" shouldn't be selected. I also see a mistake on my end as well. "Black" should have some color coding on there as well, but it doesn't. As seen in the Pivot table "Black" has 1 entry. When that is fixed, we can see that "Black" takes over the Orange color that is indicated as "Asian".

I am super surprised that there is no way to have colors static for names.

Does anyone else know?

 

[kalvinswisher]

Yeah, sorry - The "Grand Total" shouldn't be selected. I also see a mistake on my end as well. "Black" should have some color coding on there as well, but it doesn't. As seen in the Pivot table "Black" has 1 entry. When that is fixed, we can see that "Black" takes over the Orange color that is indicated as "Asian".

I am super surprised that there is no way to have colors static for names.

Does anyone else know?

View attachment 20063

When talking about "Asian" please refer back to the first I picture posted. that indicator is now Orange.

 

[TiaGtz]

Finally figured out how to do this on my own. This info should really be more easily findable online so I am posting my answer here in the hopes to help others and in case I forget how :P
Once the slicer is set up, right click into your pivot table on whatever you've put into Axis (categories) in the legend. Go to field settings; layout & print; check the box labeled 'show items with no data'
That is the source of the issue here so now just adjust your data labels and you're good to go!
If you need help with data labels visit this link Hiding data labels with zero values