Dynamic Chart Adding New Variables to Line Plot

Hi All,

I'm looking to build what I thought would be a rather simple dynamic chart that adds variables as rows of data are added to excel (with multiple not-changing columns). I feel as though I've done something similar before, but can't seem to get it to work based on the dynamic range tutorials I've followed via google searches (using basic offset formulas and defined named ranges). I'm not sure if it's because I have my data laid out incorrectly (and should flip-flop the X and Y axis), but please let me know if you have any thoughts.

I've attached a very simple excel template that shows how my data is laid out and how I'd like the line chart to look. Ultimately, the "year headers" will remain the same (2017-2021) and there will be no additional columns added (should act as the X Axis). However, I do have some dynamic formulas that will add rows/countries (ex: Chicago and Barcelona) depending on what a user selects in another part of the sheet. The formulas in these cells either returns (ex: B6, B7, B8, B9, etc) a blank or a city.

I'm hoping to be able to have the Line chart add/remove cities based on user input. It sounds easy and I thought I'd have this settled in a matter of minutes, but I can't seem to figure out how to accomplish this.

Let me know if you have any questions. Thanks!

Hi! Thanks for the response. It seems like a neat idea and maybe I'm executing this incorrectly, but when I add a city (or multiple cities) to the table and then remove one (or two or three), the table doesn't adjust and the line graph still shows a space in the legend for that recently-removed variable. I think this may be a good approach for adding variables, but I'm looking for a more dynamic/flexible solution that will accommodate adding/removing variables and a table that grows/srhinks. Thanks!