Charts: Any way to make a shape stick to a point in a scatterplot, not an absolute location on the chart?

[TomCon]

I have a scatter chart and have placed a shape (rounded rectangle) around some points. But, the scale of the axes is set to "Auto". So, when new points come in, the chart may rescale. And, when this happens, the rounded rectangles are no longer around the points that i put them around.

Any way to make these rectangles stick to the points that they are originally drawn around, so that even if the axis re-scales, they are still around the same point?

Thanks!
Tom

 

[Jon Peltier]

If you just draw the shape, it will go wherever it wants, not where you want.

But you can add a dummy series to the chart which will anchor the shape to the appropriate points.

In the example below I have some dummy X and Y data, and a third column with mostly blanks but matching Y values for the points I want to highlight (by enclosing in a shape). I have also drawn a rounded rectangle just below the data. The first chart is just the XY data from columns A and B as blue circles. In the second chart the selected data in column C has been added as a second series (orange squares). The third chart uses the following trick to replace these squares with the shape.

1. Copy the shape.
2. Select the added series.
3. Paste (use Ctrl+V).

The new series now uses the pasted shape as its markers. If you put more data into column C, the point that appears will automatically use the same shape as its marker. Since the shapes are tied to chart data as markers, changing any scale will cause the shapes to move along the new scale with the underlying points.

 

[TomCon]

Wow...thanks so much for taking the time to write this response, you really spelled it out in great clarity and detail...and it does solve the exact issues posed! I've now updated my chart and it works and solves the issue. And indeed as you point out, now that its done once, i can add more points just by filling in the column on the sheet with a value.

...But, to add (This is definitely not at all a complaint), just to say it is a bit cumbersome, and i was really hoping there was just some secret property i could set on the shape to make it have this behavior of sticking to a coordinate in the chart, rather than an absolute position. Other small side-effects: with this method it puts an entry in the legend, which i would have preferred not to have. Also, actually i had simplified the description a bit for posting, but actually i had different sized shapes, as a few of them surrounded more than one point. So, really it was a very customized situation; not so regular as a series.

But...despite the above comment, i'm now using this as the solution going forward, can live with the few things i mentioned, and glad to have a way to have the chart re-scale as it needs to, while keeping the shapes in place!

So, thanks again for the great effort and clarity!

Tom

 

[Jon Peltier]

Well, a cumbersome method is better than no method at all. And once you've used it a couple times, it will not feel so bad.

You can always remove an unwanted legend entry. Select the legend, then click on the legend entry you don't want, then click Delete. That's also a little cumbersome, but it works and it's pretty quick.

 

[TomCon]

Yes, definitely agreed, better to have a way to do it.

And heh, another winner. I had often wondered if there was a way to delete a single legend entry, didn't know there was. So, that should be quite useful.

Thanks again!
Tom

 

[Jon Peltier]

If anyone is interested, I've turned this into a tutorial on my blog: Stick a Shape to a Point in an Excel Chart.