Prevent chart from change when changing filtering options in the table

Dear all


I have run into a very silly issue with Excel and I am not able to sort it out. I have a huge table with 100+ countries and the appropriate regions (group of countries). I need to create a bar chart for each specific region (e.g. South America, Eastern Europe, etc.). I therefore created a chart after I filtered the table to show only the e.g. South American countries. However, when I cancel that filter, the chart takes all the rows in between the ones, which are relevant to my particular case...! This is very annoying and I have no idea how to solve it other than manually add data series for each country for each chart, which would take me more than a day of work.

I know that I can filter the table, copy it, paste it to another sheet and create chart there, but I need to have everything dynamic, as I might need to change the values later... It would be therefore ideal, if I can keep everything in a single table.

Thanks a lot for all your help!