Thread: Bubble chart problems

Trying to do a bubble chart in Excel 2003. I have three problems;

1) I entered 10 series and see 10 bubbles on the chart. I add series No. 11 and enter the data but the 11th bubble does not appear. It seems 10 is the maximum number of series that will be diusplayed, even though you can actually add more than 10 series to the source data. How do I get more than 10 bubbles on the chart.

2) I had to manually add each bubble as a separte series. This seems laborious and when I am repeating something many times while computing I feel there must be a better way. I tried many times to add ranges of data so the whole chart would be created in one fell swoop but could not make it work.

Can all the data points be included in a single series or must each bubble be a discrete series? I want to be able to add columns to the spreadsheet in the future and have the bubble chart add these data points, rather than having to add a new series for every newly added bubble.

See attached file, you will see what I am trying to achieve. The chart is on sheet 2.

3) I want to show the viewer what parameter bubble size represents but cannot work out how to do this.

Hi,

it's all a question of how the data is arranged. For a bubble chart you need X value, y value and bubble size in coniguous cells, then you can plot many values as one series.

I've created a helper table in the attached file to cater for that.

Then I've used the XY Chart Labeller add-in to assign the project names as labels. You can download the add-in here http://www.appspro.com/Utilities/ChartLabeler.htm

This tool lets you attach any range of cells as labels to an XY chart, and since a bubble chart is no more than an XY chart with bubble size added on, the tool works fine here , too.

But it can only assign one label per series, so I added the same data as a second series again. It sits exactly on top of the first series, so it looks as if it's not there, and I assigned that series the bubble size as a label.

hope that makes sense.

Don't know, have not tried, since there was no need with data all being in one series.

FYI you only get 10 series as the values in row 7, which determine bubble size, are being treated as text.

put 1 in a spare cell and then copy. select row 7 values and paste special multiple.

Thanks both for your help. The "numbers being treated as text" situation was the problem. This was weird as they were not entered as text (I think). Also weird was the fact that when I re-formatted the cells of row 7 as numbers, certain cells in that row kept reverting to text for some reason that I do not understand. In the end I highlighted the entire row and then clicked the drop down error icon and selected "convert text to number".

I am still thinking that having to manually add every bubble data point as a separate series in order to get discrete bubbles is not the most efficient method. If there were hundreds of bubbles it would not be practical to use multiple series. Also if multiple series are used, if I insert a bunch of new columns containing new in the future, I will have to manually add a new series for each new column. I am either missing something here or Excel just does not have the capability I seek.

I am still examining the workaround suggested by teylyn, clever

Well, with my approach you can still give each individual data point a different color, if that's what you need. When you add new columns, you simply extend the series range, click the new bubbles and select the colors. That should be much quicker than adding all new series.

WRT numbers stored as text: it is not sufficient to just set the format of the cell from text to a number format. You need to poke Excel a bit to make it turn them into real numbers. The Text to columns is one approach, the other one that Andy mentioned is to paste special and select "multiply" when pasting a 1, or paste special and select "add" when pasting a 0.

the add-on is an XLA file. If your security settings don't allow you to download it, I can email it to you if that's a viable alternative. PM me for details.

Or discuss with your IT admins why you need it and have it included in the standard Excel setup. XY Chart Labeler is created by an Excel MVP and recommended around the world.