Bubble chart with multiple values per series

[Katherine]

Hi all,

I've got data that looks like this:

Business Unit Interest Influence/Authority Role
Cards & Payments 7 9 5
Internet 10 8 2
Cards & Payments10 5 2

I'm trying to create a Bubble chart (in Excel 2007) that has:
1. Interest as the X-axis
2. Influence/Authority as the Y-axis
3. Role as the bubble size
4. Business Unit as the Series name

If there is only once instance of a particular business unit, then
this doesn't appear to be a problem. It's a bit manual, but generally
works. But when I need to create another row for an existing Business
Unit, then I have to manually open that Series, and add in the cell
reference to the new X, Y, and Bubble size.

What I would LOVE Excel to do is to do a sort of dynamic array
creation/lookup type function. So that I specify the Series name and
it selects all X-values (Interest), Y-values (Influence/Authority) and
Bubble size where the Business Unit = the name that I specify.

I appreciate that this is nearly impossible for me to explain well,
but I hope that you can make sense of it. Basically what I'm doing is
a stakeholder analysis, and there are going to be multiple responses
per Business Unit, and I want to map all of these out, with each
Business Unit as a series with a different colour so at a glance I can
see the distribution, which Business Units I should focus on, which
outliers I need to target etc.

Because new people come and go all the time, I really don't want to
have to manually open each Series and manually point Excel to a new
row everytime. Nor do I want Cards & Payments to appear as two
separate series when they the two rows belong, logically in my head,
to the same series as defined by the series name.

I'm wondering if I need to layout my data differently - which is fine
as I'm just trying to get the shell together before I start populating
it. Any ideas would be gratefully received! I don't mind installing
add-ins and what not, I just can't seem to get Excel to do this
automatically for me!

Thanks so much for any assistance.

Cheers,
K

 

[Del Cotter]

I'm trying to create a Bubble chart (in Excel 2007) that has:
1. Interest as the X-axis
2. Influence/Authority as the Y-axis
3. Role as the bubble size
4. Business Unit as the Series name

If there is only once instance of a particular business unit, then
this doesn't appear to be a problem. It's a bit manual, but generally
works. But when I need to create another row for an existing Business
Unit, then I have to manually open that Series, and add in the cell
reference to the new X, Y, and Bubble size.

I recommend laying out your data like this:

Influence/Authority
-------------------

Interest Cards & Payments Role
7 9 5
10 5 2

Interest Internet Role
10 8 2

(You may prefer to put "Internet" off to the right to give "Cards &
Payments" room to grow)

Then create your Bubble chart using "Cards & Payments" only. Make sure
your created bubble chart is embedded on the data sheet, and not a Chart
Sheet of its own. This makes life a lot easier.

Now, add the "Internet" block to the chart by selecting the block using
the mouse, and dragging the block by one of its black edges on to the
nearby chart. A Paste Special dialogue box should appear. Make sure that
"New series" is selected, not "New points(s)". Make sure that "Series
names in First Row" is checked. Do this for each type of business unit
(you don't have to do it for each line).

This is all for Excel 97-2003; I have no idea if 2007 looks like this.

In future, all you have to do is select the series to which a new line
has been added, and a set of coloured handles will appear on the data
sheet. Unfortunately you have to drag all three boxes, they don't lock
to each other for some reason, but this is probably still quicker than
what you were doing before.

And now you probably will be able to design a dynamic range that
automatically detects new lines. You will have to add a new block for
each new type of business unit as it appears for the first time, and you
will also have to store the lines in different blocks instead of one big
data block, unless you use a pivot table to make the blocks from a data
series.

 

[Del Cotter]

I recommend laying out your data like this:

Influence/Authority
-------------------

Interest Cards & Payments Role
7 9 5
10 5 2

Interest Internet Role
10 8 2

Or, if you don't mind a few extra columns, try this. Start your Chart
wizard with the data arranged like you had it originally:

Business Unit Interest Influence/Authority Role
Cards & Payments 7 9 5
Internet 10 8 2
Cards & Payments 10 5 2

Create your bubble chart using only the last three columns, ignoring the
first column.

Now create as many new columns as there are business units, and populate
them with a formula like:

=IF($A2=E$1,C2,NA())

Leading to a block like:

Business Unit Interest I/A Role C & P Internet
C & P 7 9 5 9 #N/A
Internet 10 8 2 #N/A 2
C & P 10 5 2 5 #N/A

Now keep selecting the second to fourth columns and Paste Special them
into the chart to make new series, and drag the coloured boxes to their
new columns. The #N/A should ensure that "Internet" series has only
Internet row values, and so on.

I'm going to have to let you work out where all the series go, because
sorting it out is tricky to describe, and bubble charts are particularly
hard, but you say you've been doing this manually already, so you should
already be half way there. It's just the trick of NA() and extra columns
you needed, I think, to save extra work.

One other thing: select one or two extra blank rows at the bottom, and
always insert new blank rows when you add new data, and your chart
should always grow with your data without dynamic ranges. I do this
every day at work.