Comparing two sets of data on one chart? |Dashboards & Charts|Excel Forum|My Online Training Hub

Hello,

I'm a data analyst. We use templates to create performance presentations for our clients. One of the templates is comparing the 'focus client' vs all other peer clients. The template is set up like this:

* Raw data is loaded to a "source" tab

* Source data is populating pivot tables and charts on another tab (very sloppy - many pivots and many charts on one page)

* Using a bar chart and a data table, we show FOCUS CLIENT performance vs peer group performance (in a varying number of categories - depends on the particular chart) by year, for three years. As it stands now, we must FORCE the bars for the peer group to be different colors. It'll be easier to refer you to the attached file at this point, rather than try to explain further.

My issue is this: THERE MUST BE A BETTER WAY!! In the current format, the table legend colors keep acting unpredictably, due to the forcing of the peer group bar colors. You'll see that the decision was made that the legend would only correspond to the FOCUS CLIENT and NOT the peer group of clients (you'll see).

Any suggestions for improvement that would be palatable to my superiors?

P.S. I've deleted some of the source data and some of the resultant tabs, in order to be able to load the wkbk here. Functionality is still intact.