Excel 2010: Shading per series for Bar Charts

Question

I have a table of data similar to this:

I want to create a bar chart like this:

But I get this:

or, when I add major gridlines, I get this:

However, I want a quick way to visually differentiate between the different quarters (Q1... Q4) by shading them with a different background color each or marking a border around them.

I don't want to export the chart as an image and edit it because: 1. This is a weekly report, it would be very repetitive and error prone. 2. It would be time consuming when I need to do it for 100s of records. 3. My manager prefers the data and the chart to be sent as part of the report. Hence, changing it to other formats is not possible.

Is it possible to create such a graph using Excel 2010? If so, how? I don't mind writing a macro for this, but am currently lost on the approach.

Answer 1

If you want to do something like this:

It's a bit tricky, but can be done. First you must add a fourth data series so you have the data like this:

Then you have to put the three "real" data series in the secondary axis. You must set the maximum values both in secondary and primary axes to the same value (30 in my example). Next, you delete the secondary axis. And finally, in the fourth series settings you let it overlap and put the separation to zero. Sorry, I don't know the exact english name of the settings, as my Excel is not in english.