Combination Line & Pie Chart

Way back in February of 2013, I published a how to guide on how to create a world map pie chart using vba and Excel.

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

Although I wrote the how to, the original work was of course done by Andy Pope.  The post also shows you how to create a basic bubble pie chart:

Bubble Pie Chart

Since then I’ve had a few requests for modifications.  One I posted as an example in this post: 20 excel charts for your dashboards:


Another was to create a line chart in combination with a bubble chart.  Kind of like this:

combination line pie chart

Well in essence the principle is the same.  You can download my sample sheet here.

Step 1: Create the data table

Imagine you have a data table like the one shown below.  Columns B and C represent the line chart data values.  Columns D and E represent the pie chart values.  Each row of data represents a pie chart you wish to create with 2 segments per pie chart.

line pie chart data table

Create a named range by highlighting cells D3:E14 (the pie chart values) in your data table and then typing the new name “PieChartValues” in the name box.

named range

Step 2: Create a sample pie chart

Using the Data 1 row, create a pie chart.  Remove the fill of the chart and the outline, leaving only the two 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:

sample pie chart

Step 3: Create the line graph

Highlight the columns B & C of your data table, and insert a line chart with markers (Click on Insert > Charts > Line > Line with Markers).

line chart with markersOpen up the selection pane (Home > Find and select > Selection pane) and rename the graph “chtMain”.

line chart

Step 4: Add the vba

If you are using my sample worksheet, then at this point, all you need to do is press the Update Charts button on the worksheet.  Otherwise, 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


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.

combination line pie chart


+Alesandra Blakeston


One thought on “Combination Line & Pie Chart

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s