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.

Answer 2

VBA solutions can be pretty useful; I make a living building them. But I always prefer to use native Excel functionality if I can. And in this case, I can.

If your data is in a Table (I can't tell if it's a Table or a similarly formatted range with Autofilter buttons), you can make your chart dynamic. To put the data into a Table, select the range and click Ctrl+T.

I have used data in a Table to build a chart, below left. The data is in rows, the series names are in the first column (so you would have to rearrange your data), and X-axis labels in the first row (the header row of my Table).

When I add more data to the Table, below right, the chart automatically updates to include this data without me having to do anything.

You need to keep the chart data in adjacent columns, but you can have other columns to the left or right that are not used in the chart.