Problem with BUBBLE CHART scale and legend

[keesberbee]

Lately I am working with Bubble Charts and I noticed two problems that I
cannot solve.

(1) Comparing multiple Bubble Charts that have dissimilar bubble size
data is not possible. The problem is that if you use the same size
multiplier, the bubbles in each chart have the same size, despite the
difference in bubble size values. For example the bubble sizes of the
next 2 tables are similar:
X1: 2, 3, 4, 5; Y1: 4, 3, 2, 1; Bubble1: 4, 3, 2, 1
X2: 2, 3, 4, 5; Y2: 4, 3, 2, 1; Bubble2: 0.4, 0.3, 0.2, 0.1
Of course there are some ways to overcome this problem, but they cause
a lot extra work or are not dynamic. I am looking for a Macro or VBA
procedure that generates bubble sizes in multiple charts that are sized
according to the same bubble size scale.

(2) I also noticed that Bubble Charts in Excel don’t have a proper
legend: they only inform about the third dimension (e.g. revenues). I
am looking for a legend that informs the user about the bubble sizes
and their values or ranges. A fourth dimensions can even be included in
the charts: ‘colour of the bubbles’. Also for this matter I would like
to have a clear legend. My question: is there any macro or VBA
procedure for the bubble legend?
Hopefully, someone can help me: you will help me a lot!

 

[Guest]

Hi kees:
Even without VBA or a Macro you can get you want I think.

(1) Excel refers the bubble sizes to the lowestvalue in the chart. So if
you add a bubble with size 0.1 to the Bubble1 series you get bubble sizes
with the right comparable values. So, add the 0.1 buble and make this bubble
invisible by changing the pattern and line colour to "none". Apparently,
nothing has changed but now the 1 to 4 values are sized compared to this 0.1
bubble.

(2) Not sure you can get what you want, but you could add data labels to the
bubbles, appearing close to the bubbles. The data lables could reflect the
names of the series, or the size. This is preferable anyway as it wld take
less efforts from the user because the data are close to the visual display.
Also, you could define for all bubbles different colours.

HTH,
GL,
Henk

 

[keesberbee]

Thanks Henk for your answer.

About comparing 2 independent bubble charts with different data i am
convinced that the solution proposed by you is most effective and the
best one to choose.

About the legend part, i know it is possible to include the bubble
sizes into the bubbles but that solution is not the one i am looking
for. I am looking for a legend in which fixed bubble sizes are showed
and represent a range of numbers. For example the smallest fixed bubble
size represent revenues between 0 and 1 million euros, a somewhat bigger
fixed bubble size represent revenues between 1 million and 2 million
euros, and so on. I know it is possible because i saw it one time but i
dont know how to do it.

 

[Jon Peltier]

(1) See this page for a workaround:

http://peltiertech.com/Excel/Charts/ControlBubbleSizes.html

(2) There is no way built into Excel to make this kind of legend. You would
have to carefully draw a bunch of circles and use text boxes for labels.

- Jon

 

[Del Cotter]

(2) I also noticed that Bubble Charts in Excel don’t have a proper
legend: they only inform about the third dimension (e.g. revenues). I
am looking for a legend that informs the user about the bubble sizes
and their values or ranges.

A while ago I wanted to make a legend that explained and labelled the
error bars on the data, not just the data point, and I tried making a
drawing, but it wasn't very elegant: the drawing didn't look exactly
like the data, making the result a bit amateurish in appearance.

Then I realised I didn't have to put up with that; I could have a
drawing that, by definition, looked exactly like an Excel data point,
just by making the "legend" out of a custom range with data labels. The
result was much better.

I'm sure you could do the same with a bubble range whose content would
be something like this:

x-position y-position area explanatory text
---------- ---------- ---- ----------------
1.4 0.28 1 bubble area = 1
1.4 0.50 2 bubble area = 2
1.4 0.74 3 bubble area = 3

Example:

http://www.branta.demon.co.uk/excel/bubblelegend.xls

You also need one of the add-ins that gives you the ability to define a
range as the input to data labels (Excel has needed this capability
since the 1980s, and it's a scandal that it still han't got it in 2006)

 

[Del Cotter]

Lately I am working with Bubble Charts and I noticed two problems that I
cannot solve.

(1) Comparing multiple Bubble Charts that have dissimilar bubble size
data is not possible. The problem is that if you use the same size
multiplier, the bubbles in each chart have the same size, despite the
difference in bubble size values.

Of course there are some ways to overcome this problem, but they cause
a lot extra work or are not dynamic. I am looking for a Macro or VBA
procedure that generates bubble sizes in multiple charts that are sized
according to the same bubble size scale.

No need for a macro or VBA, in my opinion. If you put a dummy bubble
range in both your charts which consists of two bubbles whose sizes are
the minimum and maximum of *all* the bubbles in both charts, then the
bubble size will be stable across charts, and will dynamically update as
you change the data. Make the bubble line color and fill color "none",
so that they don't appear on the chart.

(2) I also noticed that Bubble Charts in Excel don’t have a proper
legend: they only inform about the third dimension (e.g. revenues). I
am looking for a legend that informs the user about the bubble sizes
and their values or ranges.

A while ago I wanted to make a legend that explained and labelled the
error bars on the data, not just the data point, and I tried making a
drawing, but it wasn't very elegant: the drawing didn't look exactly
like the data, making the result a bit amateurish in appearance.

Then I realised I didn't have to put up with that; I could have a
drawing that, by definition, looked exactly like an Excel data point,
just by making the "legend" out of a custom range with data labels. The
result was much better.

You can do the same with a bubble range whose content would be something
like this:

x-position y-position area explanatory text
---------- ---------- ---- ----------------
1.4 0.28 1 bubble area = 1
1.4 0.50 2 bubble area = 2
1.4 0.74 3 bubble area = 3

Example:

http://www.branta.demon.co.uk/excel/bubblelegend.xls

You also need one of the add-ins that gives you the ability to define a
range as the input to data labels (Excel has needed this capability
since the 1980s, and it's a scandal that it still han't got it in 2006)

 

[Jon Peltier]

No need for a macro or VBA, in my opinion. If you put a dummy bubble range
in both your charts which consists of two bubbles whose sizes are the
minimum and maximum of *all* the bubbles in both charts, then the bubble
size will be stable across charts, and will dynamically update as you
change the data. Make the bubble line color and fill color "none", so that
they don't appear on the chart.


A while ago I wanted to make a legend that explained and labelled the
error bars on the data, not just the data point, and I tried making a
drawing, but it wasn't very elegant: the drawing didn't look exactly like
the data, making the result a bit amateurish in appearance.

Then I realised I didn't have to put up with that; I could have a drawing
that, by definition, looked exactly like an Excel data point, just by
making the "legend" out of a custom range with data labels. The result was
much better.

You can do the same with a bubble range whose content would be something
like this:

x-position y-position area explanatory text
---------- ---------- ---- ----------------
1.4 0.28 1 bubble area = 1
1.4 0.50 2 bubble area = 2
1.4 0.74 3 bubble area = 3

Example:

http://www.branta.demon.co.uk/excel/bubblelegend.xls

I remembered using this approach after I sent my reply, then I decided it
would be too complex to describe. Good job.

You also need one of the add-ins that gives you the ability to define a
range as the input to data labels (Excel has needed this capability since
the 1980s, and it's a scandal that it still han't got it in 2006)

Or in 2007 for that matter.

- Jon