Bubble Charts w/ Scatter Plot overlay

[Guest]

Is there a way to overlay a scatter plot chart on the same graph as a bubble
chart? I'm currently using Excel 2002 and I can't get it to combine ANY
other chart type with a bubble chart. Will Excel 2007 allow me to do this?

 

[Ed Ferrero]

Hi Tim,

You can emulate a bubble chart with a scatter chart. Just need to paste some
circle shapes as markers for one scatter chart series. Then add a new series
and leave the markers as default.

A sample that you can change is here http://www.edferrero.com/charting.aspx
look for 'BCG Matrix'

Don't know how comfortable you are with VBA. If it gets too confusing, post
back and I will try to explain further.

Ed Ferrero

 

[Del Cotter]

Is there a way to overlay a scatter plot chart on the same graph as a bubble
chart? I'm currently using Excel 2002 and I can't get it to combine ANY
other chart type with a bubble chart.

I don't know anything about 2007, but Bubble charts won't work in
combination with any other type in earlier versions.

But if a scatter chart is all you want, then can't you just use small
bubbles of fixed size as your markers? If you want different shapes as
markers, you can cut and paste Autoshapes to make the "bubbles" any
shape you like. But be careful, I find they sometimes drift if you
display at different zoom sizes than 100%.

 

[Tushar Mehta]

Is there a way to overlay a scatter plot chart on the same graph as a bubble
chart? I'm currently using Excel 2002 and I can't get it to combine ANY
other chart type with a bubble chart. Will Excel 2007 allow me to do this?

No, unfortunately, 2007 doesn't add that capability.

However, it is almost trivial to combine XY Scatter and Bubble charts (with
one constraint). After all, a Bubble chart is nothing but a XY Scatter
chart with an added dimension.

So, plot your Bubble chart. Then, add an additional column of data for the
XY chart (assuming that the data are in columns) with all values equal to
some small number. This number will depend on the values in the column for
the true bubbles but start with 1.

Add this new data set (with 3 columns) to the chart. Change the values in
the dummy column added above to get the desired effect of size.

The constraint on the above technique is that the only kind of 'marker' you
can use is a circle. You can change the size, the color, and the fill, the
border, etc., but you are restricted to a circular marker.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

Thank you, Ed. VERY helpful. Couple questions.

How do you make the pie charts stand alone instead of appearing in a box?

How do you name the pie charts Pie_x?

Thanks,

Tim

 

[Guest]

Thank you, Tushar. Not what I was looking for on this project, but it
definitely gave me some ideas for future charts.

 

[Ed Ferrero]

Hi Tim,

You can shift-click on an embedded chart to select the chart object rather
than the chart.

Once this is done,
you can format to get rid of the box (line + fill)
you can rename the chart object by typing in the name box.

Ed Ferrero

 

[Guest]

Ed -

We're ALMOST there. I usually have my data source transposed from the way
you have it and your macro doesn't seem to like it.

How do I change the VBA code for my X:Y data arranged in columns instead of
rows?

Thanks.

 

[Ed Ferrero]

Hi Tim,

The macro does not reference a sheet range. Do you have data plotted as one
series, or multiple series in an x-y chart? To find out, select the chart,
then use Chart -> Source Data -> Series Tab. There should only be one
series. If you have multiple series, delete all but one and change the x
values, y Values ranges to suit.

Ed Ferrero

 

[Jon Peltier]

Ah, but you can copy an autoshape and paste it onto the bubble series as a
custom marker, and the custom marker will size according to the bubble size
data:

http://peltiertech.com/Excel/ChartsHowTo/CustomMarkers.html (bubble chart
near the end)

- Jon