Dynamic Multi Level Column Graph

[Skybluekid]

Hi all,

I am after a way to create a dynamic multi level graph, without using a Pivot Table. What i would like is the graph to change, when selecting the month, to show category, depot, fine type, Registration. I have no issue in creating a static multi level graph, but as the data changes every month, it does need to be dynamic.

https://www.dropbox.com/s/738n97dy33mosnk/Dynamic Multi Level Chart.xlsx?dl=0

I have uploaded a sample of the data in the above link. I have tried various ways but it does not quite work.

any suggestions would be helpful.

Thanks in advance

 

[Jon Peltier]

There's not enough data in your worksheet to create a chart. Where are your values?

What you need are blank cells in the data range to encourage the labels in an outer layer to center themselves over several labels in an inner layer. In this screenshot I've indicated blank cells by shading them light gold. The blanks in column C cause the Fine labels to center themselves under the ABC 1 and ABC 2 labels, and the blanks in column B cause the Depot labels to center themselves below the Fine 1 and Fine 2 labels.

Unfortunately you need actual blank cells, that is empty cells. You can't use a formula that returns "", because "" is not a blank, it is just a very short bit of text, and the cell isn't empty, it contains a formula. You can't use NA() or #N/A either, because the new feature in Excel that allows you to plot #N/A like an empty cell only applies to X and Y values, and not category labels.