View original (French)

Avoid overlapping bubbles in Excel

Matzouz -  
 delthi -
Hello,

I want to create a bubble chart in Excel, and some data points have the same coordinates, so the bubbles and corresponding data labels are overlapping, which makes the chart invisible.

Is there a trick to avoid this overlapping and to have at least the data labels slightly offset?

Thanks
Configuration: Windows XP Internet Explorer 6.0

9 answers

  2. Matzouz
     
    Thank you for your response,
    but from what you did, if I understood correctly, there was no overlapping bubble, meaning two bubbles having exactly the same coordinates, making one invisible if they are the same size.

    How can we make it so that both can be visible? Or at least so that we can display both data labels?
    0
    1. JeeP
       
      You just need to sort your base file by the column that manages the bubble size. This way, the smaller ones can be displayed automatically above the larger ones... :-)
      0
  3. tontong
     
    Indeed, the bubbles did not overlap; they overlapped. It is normal for bubbles with the same coordinates to overlap.
    For the labels, simply offset them either manually with the mouse or by using the XYchartLabel add-in.
    0
  4. Matzouz
     
    Well then, we're agreed on the reason for the overlapping of the bubbles!

    In any case, I didn't quite grasp what happens in the link you sent me.
    Are you actually changing the size of the bubbles to avoid their overlapping?

    As for the XYchartLabel marco, I have no idea how to get it to work, or where to find it, etc...
    0
  6. tontong
     
    For overlapping bubbles, it is always possible to play with colors.
    There are two factors that influence the overlap of the bubbles: the size of the largest bubble and the relative size of the bubbles.
    The complementary macro is downloadable here:
    http://appspro.com/Utilities/ChartLabeler.htm
    0
  7. Matzouz
     
    Ok
    I will try to use it

    Thank you Mr. Tong Tong
    0
  8. Matzouz
     
    I use the XY Chart Labeler macro,
    but it seems to me that each label has to be handled one by one, and if there are more than five labels, they will inevitably overlap unless you intervene manually.
    Does anyone have a solution?

    Thank you
    0
  9. tontong
     
    Hello,
    The position of data labels is not fixed; they can be moved together or individually.
    With XYchartlabeler, select: move chart labels --> choose a series from the dropdown list provided with "select a data series".
    To move a single label, check "move only this data label" and then choose a label from the provided list. Adjust the desired number of increment points and move with the arrows.
    0
  10. delthi
     
    Hello,
    by adding a border and no fill, the circles overlap and we can see them all. Less aesthetic though.
    0