Dynamic area for scatter chart|Dashboards & Charts|Excel Forum|My Online Training Hub

Hi, I’m trying to set up a dynamic reference for a table with data re male and female. My goal is to build a scatter chart with 1 serie with data re male and the other re female. Currently, each time I refresh the data, I sort the table by male/female then edit manually the graph series. Is there a way I could avoid that manual sort and let excel plot the “male” and female” series ?

Mynda suggested using a pivot, but while I read the tip on how make a scatter chart from a Pivot, when I select the data using the columns form the pivot, these are not dynamic, so when I filter in my slicer I lose the second serie because Excel has not readusted the area for each serie according to the filter.

I have attached a sample, so you can better understand my issue : filter on level 8, and although there are male and female on that level, all data show as "female".

Thanks so much for taking a look at this, it's driving me nuts, there must be a way !