Text x-axis in Bybble chart

[rodys]

Hi there,

I would like to create a bubble chart to compare changes in financial performance between quarters. The bubble chart shows on the y-axis the EBIT, and the bubble size is the total amount sold. I would like to show on the x-axis the actual name of the location. I got as far as the screenshot enclosed but manually overwritten the x-axis with a text box. Does anyone know how to automatically load the names of the locations on the x-axis?

 

[Jon Peltier]

We'll use a helper series for this. The first data range below is my bubble chart data, and the top right chart is the bubble chart.

The second data range is for the helper series. Copy this range (4 rows x 3 columns), select the chart, and select Paste Special from the Paste dropdown on the Home tab. Select New Series, By Columns, Categories in First Column, then click OK. A set of tiny bubbles appears along the X axis in the bottom left chart. I have changed the Y-axis minimum to zero, but if you want a different axis minimum, change the axis minimum to that and also change the zeros in the copied data range to the desired axis minimum.

Format the X-axis data labels so they use a custom number format of " " (that's right, a space enclosed by double quotes), which hides the axis labels but leaves a space for your custom labels. Select the tiny bubble series, click the plus icon floating next to the chart, click the arrow next to Data Labels, and select More Options. Check X Value and uncheck the other Label Options, then select Label Position Below. This is the bottom right chart.

Finally, you need to format the tiny bubbles to use no outline and no fill color (not shown).

 

[rodys]

That is awesome! That worked, thanks heaps