Overlapping Circles on Scatter Chart overlaps labels

[amartin575]

How does one fix labels that overlap when multiple circles on a scatter plot sit on top of one another? See attached image as example.

[PeteABC123]

Can you click the series name (labels), and drag into a better position?

Or, right click the labels, and format legend should appear.

[63falcondude]

Edit: Disregard, misunderstood

[amartin575]

I cannot click and move them. They are in the center of the circle for all circles. I was wondering if there was a way where it does it automatically or if a VBA macro can move them for me.

[amartin575]

I attached an example of the file.

[MrShorty]

I feel like I have seen a VBA macro that does this, but I cannot find it now -- assuming I didn't dream it.

I did find this discussion https://www.excelforum.com/excel-pro...ervention.html The basic idea is to add an additional data series (formatted to be invisible except for the data label), then attach the data labels to that series. Figure out exactly how you want to determine suitable data label positions, then program those formulas into the spreadsheet cells for the extra data series. Adding a data series, formatting it as invisible, and attaching data labels (removing from existing data series) should all be straightforward on a bubble chart like this. The hard (part especially if you expect it to be fully automated) is to come up with a suitable algorithm that fills in Y (and/or X) values for positioning the labels.

My older version doesn't have the support for data labels that yours does, but here's what I did to approximate it:

1) Add two columns to the data table.
2) In column AG, enter Y values for positioning the data label. In this case, I used something simple like =AD28+COUNTIFS(AD$28:AD28,AD28,AC$28:AC28,AC28)-1 to choose the Y position of each data label point. Note that I used regular A1 notation because I don't know how to get structured table references to have the correct combination of relative and absolute references. The basic algorithm is to move each duplicate x,y pair entry up one unit on the chart.
3) Something like =SIGN(Table2[[#This Row],[Bubble - Cost]]) to get a small bubble size for appropriate points.
4) Deleted the data labels from the first data series. Added appropriate data labels to the second series (I could only add series name or Y value or similar, but it should work for values from cells, too).
5) Format this series to have no fill so it effectively becomes invisible except for the data label.

Would something like that work for you?