An exploding Pie or bubble chart

[PeterGammage]

Looking to graphically represent some data, which is an expansion of some data. A little like a pie of a pie, however I need a pie for all peices of the pie.

The example below is a complex version of this - however you should see that the bubble in the center represents a total whilst the bubbles linked to it are break downs of that total, and the bubbles linked to them are the breakdowns of them. I´m not looking to be as complex as this - just one level of explosion of the bubble or pie will do - but I do want to do it for more than one section (ie not just a pie of a pie as it can only expand one section)

http://www.guardian.co.uk/business/interactive/2009/sep/16/public-spending-larry-elliott?INTCMP=SRCH

All ideas, thoughts or considerations greatly appreciated!!

 

[pgc01]

Hi Peter
Welcome to the board

Check the section "Central Pie with Smaller Satellite Pies" in Jon Peltier's article about Pie of Pie charts:

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=471

HTH

 

[PeterGammage]

Thanks for the welcome and for the link.

The linked example is exactly what I have done, however I was looking for it in a function or chart format so that when things changed (like the makeup of the central pie) the sizes of the satellite pies also changed

 

[pgc01]

I'm afraid that, if what you want is an automatic solution, I don't think you can get it directly from what excel offers in terms of charts by default. I guess you have to know vba an implement a solution yourself.

You can write a procedure that, given a complete table with the detail and grouped results, builds the main Pie chart and all the satellite Pie charts around it with sizes according to the values.

You could then trigger the redraw/rebuild of the charts using worksheet or chart events.

You'll need to know vba, the chart object model, how to manipulate shapes and how to work with event procedures.

HTH

 

[PeterGammage]

Thanks - I'll stick to the Pie + Pie example as it gives me 80% of what I want for 20% of the effort.

Maybe Office 2020 will deliver this function

 

[Steve=True]

This can be done however, you would hve to overlay a line chart with a bubble chart since you cannot combine a buble chart with a line chart.

 

[PeterGammage]

Can you point me at an example of how to overlay the charts.

 

[Steve=True]

Not sure of where an example is, however the concept is easy to explain.

Create 2 charts.

One with bubbles,

One with Lines to the bubbles.

On the line chart, change the plot are and chart area Fills = No Fill (this makes it transparent.

Then move the line chart on top of the bubble chart to "Overlay" the charts.

 

[PeterGammage]

Thanks. I had thought you were talking about merging 2 charts - I had seen an example in the past where one chart type was dragged and dropped onto (a line chart onto a spider diagram I think) and and the result was a completely different chart type - a merge of the 2 (not an overlay - but a reall different chart type)

 

[Steve=True]

I have a working prototype for 1 main node with up to 12 additional nodes. I have yet to link it to other main nodes, but it works for a single bubble surrounded by up to 12 additional bubbles.

Do you have sample data that i can use for my beta's?