Create a bubble pie chart or world map pie chart using vba and Excel charts

We all know what a pie chart looks like, and we all know what a bubble chart looks like:

What if you could combine the two to make a bubble pie chart. (Thanks to work done by Andy Pope):Or even using longitudes and latitudes, create a world map pie chart:

How can it be done?  If you have the new Excel 2013, then it’s easy: use the new extensions.  If you don’t, then to create a simple bubble pie chart there are 5 steps:

1. Create the data table for the different pie charts
2. Create 1 sample pie chart
3. Include the bubble graph data in the data table
4. Create the bubble graph
5. Add the vba code to add the pie charts to the bubble graph

 

Download the example excel sheet here

Step 1: Create the data table for the different pie charts.

Imagine you have a data table like the one shown below.  (Please note the three blank columns, we’ll fill those in in Step 3). Each row of data represents a pie chart you wish to create with 6 segments per pie chart.  You should be able to see this table on the basic tab of the spreadsheet above.  Create a named range by highlighting the numbers in your data table and then typing the new name “PieChartValues” in the name box.

Step 2: Create one sample pie chart.

Using the Data 1 row, create a pie chart.  Remove the fill of the chart and the outline, leaving only the six segments of the pie chart.  Open up the selection pane (Home > Find and select > Selection pane) and rename the graph “chtMarker”. Format the segments as you wish:

Step 3: Include the bubble graph data in the data table.

Add the data to the 3 columns of your data table as shown below (X, Y and Size):

The X and Y columns will be used by Excel to plot your bubbles on the graph, the Size column tells Excel the size of the bubble

Step 4: Create the bubble graph.

Highlight the columns B, C & D of your data table, and insert a bubble graph (Click on Insert > Other graphs > Bubble).

Excel will automatically format the bubbles for you.  Adjust the axes and the formatting of the graph as you wish.  Open up the selection pane (Home > Find and select > Selection pane) and rename the graph “chtMain”

Step 5: Add the vba code to add the pie charts to the bubble graph.

Add the vba code below in Module 1.

Sub PieMarkers()

Dim chtMarker As Chart
Dim chtMain As Chart
Dim intPoint As Integer
Dim rngRow As Range
Dim lngPointIndex As Long

Application.ScreenUpdating = False
Set chtMarker = ActiveSheet.ChartObjects(“chtMarker”).Chart
Set chtMain = ActiveSheet.ChartObjects(“chtMain”).Chart

Set chtMain = ActiveSheet.ChartObjects(“chtMain”).Chart
Set rngRow = Range(ThisWorkbook.Names(“PieChartValues”).RefersTo)

For Each rngRow In Range(“PieChartValues”).Rows

chtMarker.SeriesCollection(1).Values = rngRow
chtMarker.Parent.CopyPicture xlScreen, xlPicture
lngPointIndex = lngPointIndex + 1
chtMain.SeriesCollection(1).Points(lngPointIndex).Paste

Next

lngPointIndex = 0

Application.ScreenUpdating = True

End Sub

Please note, you must save the file with the extension *.xlsm if you want the macros to work.  You can also easily substitute the named range if you wish, by simply typing in the cell reference e.g. (“E4:J12”) instead of (“PieChartValues”).

Create a button on the page using the developer tab and assign the macro above to it.  Then click the button.  Adjust the data as you wish and click the button again.  The chart should update.

World Map

If you wish to create a world map like the one shown on the “WorldMap” tab of the spreadsheet attached here, all you need to do is change Columns B and C to longitude and latitude coordinates:

If you don’t know the longitudes and latitudes of the places where the charts need to be, use Google Maps and iTouchMap.com.  You can drag the marker to the position you wish on Google maps and it will give you the longitude and latitude

 

Then format the plot area of the graph and add a picture showing the map you wish.

Adjust the X and Y axis to match the maximum and minimum longitude and latitude shown on the graph. 

 

Enjoy!

Alesandra Blakeston