Conditional formatting with Column Chart

[elg0007]

I have a small column chart that pulls the x value from column Q (eg. Volvo, Honda, Toyota, etc) and plots it vs a rating in column R. The vehicles in Q come from a dropdown menu of 8 choices, and I want the colors of the bars to change depending on what vehicle is chosen. The order is not set- eg, Volvo could be selected first or second or third, etc. Is there a way to set the bar colors for each vehicle WITHOUT using a macro? Thanks!

 

[Jon Peltier]

This is covered in my tutorial Conditional Formatting of Excel Charts. Here is how it could work for your situation.

I gather you have lots of other stuff in the columns to the left of your vehicle selection column Q, but I'll put the stuff there anyway.

I set up the data range in A2:I10. Column A links to the selected vehicles in column Q, that is, the formula in A3 is

=Q3

etc. Row 2 has the vehicle names, and it might be useful to put them in the same order as users encounter them in the dropdowns.

Select B3:I10, type this formula, and hold Ctrl while pressing Enter to fill the selected range with the formula:

=IF(B$2=$Q3,$R3,0)

This puts either a zero or the rating for the car listed at the top of the column into the column.

Make a chart using this added range, plotting series by columns (top left chart). Format any one of the column series so the Overlap is 100 and the Gap Width is something like 75 (top right chart).

This data is probably better rendered in a horizontal bar chart, so make a bar chart using the table, plotting by column (bottom left chart). Format the vertical axis to plot Categories in Reverse Order and so the Horizontal Axis Crosses at Maximum Category; format any one of the column series so the Overlap is 100 and the Gap Width is something like 75 (bottom right chart).