Please note javascript is required for full website functionality.


Charts and Dashboards: The Bubble Pie Chart - Part 3

8 September 2023

Welcome back to our Charts and Dashboards blog series.  This week, we’re going to finish constructing our Bubble Pie chart.


The Bubble Pie chart

In part 1 we started to look at how to construct a chart that might look like the following:

We began with this Excel file here.  From the data within this workbook, we created a Bubble chart:

Next, we created a Pie chart for Market A only, removing the title, legend and making the chart area transparent, removing the border as well. 

We then copied this as a picture, added data labels to our Bubble chart and pasted the Pie chart on to the Bubble for Market A

We are now at a stage where we can populate the other Bubbles with images by making a Pie chart for each Bubble and copying and pasting them to the correct Bubble.  Better yet, we may simply employ the assistance of VBA to automate this process.


In this instance, we will go to the Pie chart we made for Market A and name it ExamplePie in the Name box (ALT + F3).  Then, we go to the VBA Editor (ALT + F11) and create a new module and paste in the following code:

Sub PieMarkers()


    ' Declare variables

    Dim pieChart As Chart

    Dim mainChart As Chart

    Dim pointIndex As Long

    Dim rowRange As Range


    ' Disable screen updating to improve performance

    Application.ScreenUpdating = False


    ' Get reference to pie chart

    Set pieChart = ActiveSheet.ChartObjects("ExamplePie").Chart


    ' Get reference to chart that pie markers will be applied to

    Set mainChart = ActiveSheet.ChartObjects(1).Chart


    ' Process each row of pie chart data

    For Each rowRange In Range("Data[[Agriculture]:[Services]]").Rows


        ' Assign new values to pie chart

        pieChart.SeriesCollection(1).Values = rowRange


        ' Copy pie chart image

        pieChart.Parent.CopyPicture xlScreen, xlPicture


        ' Paste image to appropriate data point

        pointIndex = pointIndex + 1





    ' Release objects

    Set pieChart = Nothing

    Set mainChart = Nothing


    ' Re-enable screen updating

    Application.ScreenUpdating = True


End Sub

This macro creates a Pie chart for each market and then copies and pastes the appropriate picture into the relevant Bubble.  The next step is to then create a button which will update all the Pie images into the Bubbles for us by simply using ‘Assign Macro’ to a button or shape that you like:

Et voila!  We have the Bubble Pie chart:

Word to the Wise

If you want more segmentation in the Bubble images, simply add more columns to the Data table and update this part of the code to update the correct column headings:

    For Each rowRange In Range("Data[[Agriculture]:[Services]]").Rows

You can download the complete Bubble Pie chart here.


That’s it for this week.  Come back next week for more Charts and Dashboards tips.