Pivot table and buble chart

[mbobro]

Hi,

Is there any solution how to combine buble chart as pivot table chart?
The point is pivot tables are flexible to use, change series etc whil
buble chart allows me to illustrate the value by the size positioned o
(X,Y).

Thanks,


Micha

 

[Jon Peltier]

Michal -

You can't make a pivot chart with a bubble chart type. But you can make
a regular chart from a pivot table. I do it all the time, to get around
other shortcomings of pivot charts.

The trick is to select a blank cell away from the pivot table before
starting the chart wizard. In step 1, select Bubble, in step 2, click on
the Series tab, Add a series, and select the ranges that contain the X,
Y and bubble size data. Be careful not to select one of the pivot table
buttons, or Excel will break your chart by turning it into a pivot chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[mbobro]

John,

Thanks for this trick - that's smart. Anyway I think I should explain
bit more about what I'm planning to do as your solution does not solv
this completely.

What I'm planning to do is to create a kind of a custom map, let say o
Europe, that will show the potential of countries.

The potential is calculated by a pivot table from a raw data. Pivo
table is very flexible enabling me to select both countries and year
of comparison (to be precise years are more important than countries a
countries do not change much in Europe so we may always display jus
all of them). This is also user friendly as it alllows the user t
change the year without understanding all VBA and raw data processin
that is in background. Moreover the pivot chart data is still linked t
the raw data, so once I add, correct, change the raw data I hav
automatically chart updated.

I have thought about having pivot bubble chart as I can set am Europea
map in the backgound of the chart and then use constant X, Y value
that will correspond with the coutries placement on the map. And th
potential will be displayed by the bubble size.

What a surprise when Excel told me that I may not use the bubble chart


So now coming back to your solution.

My pivot has years in rows, countries in columns and potentia
parameters on pages.

Yes, I may take the bubbles size from the table and make regular char
out of it. And below I set two rows with fixed XY positioning.

The pivot tables button allows me to change years and the char
updates. Unfortunately at the same time, if the user change th
countries, the chart will corrupt - the pivot will change, the XY wil
remain constant.

I may not block or hide country button only. If I hide all the button
I won't be able to specify the years

At the end of the day I may instruct the user: "DON'T CHANGE TH
COUNTRIES", but in my experience it does not work :-(

Eh, why Microsoft has blocked bubble chart with pivot.....

So now with much more knowledge, how would you fix it?


Thanks,


Micha

 

[Jon Peltier]

Michal -

If I understand what you're doing, here's how I'd try it. This is the
simple way, where you make them see all the countries.

My raw data table would have columns for country name, latitude and
longitude (Y and X), year, and the parameter you're using as bubble size
(potential).

My pivot table would have country in the rows area, longitude, latitude,
and potential in the data area (all as average, not sum), and year in
the page area. Then you drag the data button to the column area. So the
columns are Country, Long, Lat, Potential.

The chart is made using Longitude for X, Latitude for Y, Potential for
bubble size. Want the country names? Use Rob Bovey's Chart Labeler
(http://appspro.com) or John Walkenbach's Chart Tools
(http://j-walk.com) to apply the labels in the country column for the
labels. When the year is changed in the page area of the pivot table,
the chart changes to show the values for the selected year.

For a more complicated approach, where the countries can also be
selected in the pivot table, you can set up dynamic ranges that grow and
shrink as more or fewer rows (countries) are included in the pivot
table. Then use these dynamic ranges as the series source data for the
chart. Some examples of dynamic charts, plus lots of links, are shown on
my web site:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[mbobro]

John -

A part of this work would be to specify the (X,Y) for the bubbles.
thought I'd be able to drag and drop them whereever I want, but M
Excel again is not cooperative too much on this field... :-(

Of course I may find a position and enter it manually, but maybe ther
is a smarter solution?

Regards,

Micha

 

[mbobro]

*John -

A part of this work would be to specify the (X,Y) for the bubbles.
thought I'd be able to drag and drop them whereever I want, but M
Excel again is not cooperative too much on this field... :-(

Of course I may find a position and enter it manually, but mayb
there is a smarter solution?

Regards,

Michal *

OK, I have found the bug - I had a formula to calculate the X,Y values
Once I tried to drag and drop it was not in conflict with that formula
Now I have copied values of the formula and I'm free to drag&drop.


Micha