Bar graph for two sets of data on the same axis? See example in body of message

[ajjava]

I'm not sure if this is possible, but figured I'd be the (possible) hero in my department and ask.

The desired result is below. We're trying to compare CLIENT 1 to PEER GROUP.

As you can see, it's plotting two different sets of data...but not really. I had to manually color the "peer group" data.

As for the Years, it's ok that the labels (and their color) ONLY correspond to CLIENT 1.

Is there a way to make this a dynamic graph, bypassing the need for manual color-coding of the bars?

The bottom axis values are being derived from the first two rows (195, 196), in case that isn't clear.


DESIRED RESULT

DATA TABLE

 

[Misca]

Try adding new lines after each year and add the Peer Group data to the new rows. This should take care of the coloring problem. Unfortunately the data table shows the empty rows as well. If this is a problem and you'll need to display the data table in the chart (and not underneath it in separate table / range) the only way to automate this would be using a macro that takes care of the manual color change.