Dynamic Scatter chart based on criteria


New Member
Oct 2, 2020
Office Version
  1. 365
  2. 2010

I want to create a X/Y scatter chart to plot projects Final Amounts Invested amounts & % and use slicers to select one or more than one location.
I have numerous locations and each location has a different number of projects to be included.
This number of projects changes each month
Data source is a pivot table
I want each location to be a series with a different color in the scatter chart. I want the color to stay with the series when slicers are used to select a particular location.
I attempted to create a dynamic range based on a Location criteria and then use this range for each location in my scatter chart but I cannot figure out the offset & index formula
See the image. Imagining that the table is a Pivot, what is the formula to use in Series X values & series Y values so that if there are more or less data for each location, the range is dynamic?
I do not want to manually adjust my ranges for each location every month.

Do I need to create Names range with offset based on a criteria? I cannot figure out how to do so and then use this range in the scatter graph.

Thank you for your help.


  • Dynamic range.PNG
    Dynamic range.PNG
    34 KB · Views: 4

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Watch MrExcel Video

Forum statistics

Latest member
Super Symmetry