X axis labels with "super-categories" or "headers"

Question

How do I make the X axis labels of a column chart looks like this? i.e. with, for lack of a better name, "super-categories" or "headers", like the labels "All sites", "All sites excluding LR" etc. in the image below.

Excel 2010 made this one automatically. A cell happened to be selected when I inserted a chart; if that cells happens to be in an area with data, then Excel will take a guess at what your chart should look like and make that automatically. That's what happened to me. I didn't even know this was possible; I just stumbled upon this.

Now I'd like to know how to reproduce this manually. i.e. without relying on Excel's automated reaction described above.

Answer 1

I figured out how to do it manually, without relying on Excel's automated behaviour, and without having to lay out the series data in any particular way (i.e. next to each other and next to the labels).

The critical bit is the layout of the category axis labels, and selecting that entire range as your axis label range.

If you lay out your labels like in the image below, and select as your axis label range the entire range where your categories, super-categories, super-super-categories etc., then you get the plot further below.

Answer 2

You simply need to get the "super-categories" just like a header (occurs only once), but in a column to the left of the individual categories. The value will be in the last column. Here's an example when inserting a simple bar chart: