CODEWITHSUNDEEP
excelexcel-2010vba
Karthick S
Karthick S

Reputation: 3304

Excel 2010: Shading per series for Bar Charts

I have a table of data similar to this:

enter image description here

I want to create a bar chart like this:

enter image description here

But I get this:

enter image description here

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

enter image description here

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.

Upvotes: 1

Views: 151

Answers (1)

m e
m e

Reputation: 291

If you want to do something like this:

enter image description here

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

enter image description here

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.

Upvotes: 1

Related Questions