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
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
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.