Possible to conditional format fill color of pivot chart bars?

Hi All,

Is there a way to condition the fill formatting of bars in a bar type pivot chart on the values of one of the columns in the pivot table? Here's some screenshots illustrating what I'd like to do:

example1.jpg
example2.jpg

I manually clicked on each bar and changed the formatting to red for this example. In my actual work, the "category 1" column has many many values so I'd rather not have to do that, especially since that formatting is reset with some changes to the pivot table.

Is there a built in excel setting I can use to do this? Is it possible to do with VBA? I don't need the color choice to be made automatically, I just want to avoid having to click into every bar to set the fill color. So I'd be fine making a table of all of the valid values of category 1 and the color choice I want for each if I could somehow have that automatically translate over to the pivot chart.

Any ideas on the best way to accomplish this?

Thanks,

Dave

You would need to:
Step 1. Seperate your values into seperate columns
Step 2. Set column overlap to 100%

Because this is a pivot table, this becomes more difficult.

You could.... add two columns to your original data table called A-Value & B-Value. then have =if([category1]="A",[VALUE],"") in the first column, and =if([category1]="B",[Value],"") in the second column.

If that is not clear, I would suggest upload the workbook, rather than an image file. It is easier for people to take the excel file, and construct a solution for you. I am confident the suggestion above will work, but because you uploaded image files, I am unable to test it.

Can you take the latest version you uploaded, and create a version showing how you would want it sorted, using the "Value" field? The formatting won't be correct, but I am not sure of what you are envisioning in terms of how this would be sorted.

Perhaps this is what you mean. If not, you should be able to sort by the values column and get what you are looking for, I am guessing.

It is definitely a roundabout way, but sometimes that is what you needs to do with pivot tables.
I believe you HAVE to have the value column included, and it needs to be first in the pivot table because of the overlap priority.
Thanks for the feedback.