Stacked column Chart EXCEL Dynamic

Question

Is there a way to create a dynamic stacked column chart in Excel 2016 and above, that functions like this:

1. Legend is updated with new row entry, and that new item is connected to multiple cells in that row.
2. Axis is fixed it has 4 names for it. See picture.

So what needs to be dynamic is series name and values. But for series values it is taking a Range for a value (as example ("C3:G3"), while for series name can be added more, updated and deleted. If new series name is added then it takes the corresponding value based on the row it is in.

So far nobody could answer this before, and I couldn't find anything about dynamic stacked column diagram. I am working on developing Tool for university which will be used for dynamic calculations, so any help would be welcome. Tnx in advance.

Answer 1

Dynamic charting is fairly easy to control with dynamic range names for dynamic range selection.

What is NOT easily done dynamically is adding/removing chart series.

Since you have tagged with VBA, it looks like you are open to a VBA solution, and that is what you will need to achieve this.

Conceptually: you need to create an event based sub (or manually trigger a sub) that creates the chart from scratch, based on the data in the range. The sub will then need to be set up to create a series for each row of data. Start with the macro recorder, create and format the chart you want, then stop the macro recorder and make the range for the chart data dynamic.

Overall, it might be worth looking into using a different data architecture, so you don't have to work AGAINST how Excel typically and logically flows data into charts, but rather use a different data layout or chart that goes more easily with the way Excel works. That will make your life easier.