Update PowerPoint chart data from Excel

We all know that in general Excel application is for calculations and PowerPoint is for visual presentation. So all the data is prepared in worksheet, based on which all the charts in PowerPoint are created. In this article I will show You how to update PowerPoint chart data from Excel using VBA.

This knowledge, how to update PowerPoint chart data automatically, is really useful for weekly, monthly or any other periodic reports, which uses same PowerPoint template. So let’s go straight to that and start with the example pie chart.

To edit the pie chart data just right click on chart area and select Edit Data in Excel.

After clicking it pops up the Excel workbook with “Chart” in the title bar.

So this is the way to change chart data manually. But if there is the way to change it like that, there is also the way to change it automatically!

But before we go to the VBA world You need the name of the chart, and for sure it is not “pie chart”.

Select the chart and go to Chart Tools. There choose Format tab and select Selection Pane.

And then appears the panel on the right side of the screen. In our case we can see only 1 shape name in the list.

If You want to change default name of the object just double click on it and type yours. For this article I will stay with this default one.

OK, got everything we need.

Time to automate the data update

First of all we need to open the template. To open choosen PowerPoint presentation and set its first slide to object variable use this.

pp_path = "...\Presentation1.pptx" 'your PowerPoint path


Set PowerPointApp = CreateObject("PowerPoint.Application")
Set draft = PowerPointApp.Presentations.Open(pp_path)
Set slide = draft.Slides(1)

If You Add Watch on slide variable You can find in its Shapes the graph shown below.

Now, knowing the path to the PowerPoint chart workbook, You can easily update the data, just like in case of chart data in Excel application. Use copy paste method, array method or simply type specified numbers in code to change the values on chart.

With slide.Shapes("Chart 5").Chart.ChartData.Workbook
    With .Sheets(1)
        .Cells(2, 2).Value = 7.9
        .Cells(3, 2).Value = 4.1
        .Cells(4, 2).Value = 1.5
        .Cells(5, 2).Value = 0.8
    End With
End With

Full code

Gathering the code lines together and adding some other like declarations, saving and closing PowerPoint presentation, it results in this.

Option Explicit

Sub update_pie_chart_data()

Dim PowerPointApp As Object, draft As Object, slide As Object
Dim pp_path As String

pp_path = "...\Presentation1.pptx" 'your PowerPoint path


Set PowerPointApp = CreateObject("PowerPoint.Application")
Set draft = PowerPointApp.Presentations.Open(pp_path)
Set slide = draft.Slides(1)

With slide.Shapes("Chart 5").Chart.ChartData.Workbook
    With .Sheets(1)
        .Cells(2, 2).Value = 7.9
        .Cells(3, 2).Value = 4.1
        .Cells(4, 2).Value = 1.5
        .Cells(5, 2).Value = 0.8
    End With
End With


draft.SaveAs ThisWorkbook.Path & "\Presentation " & VBA.Format(Now, "dd.mm.yyyy") & ".pptx"

draft.Close
Set draft = Nothing

PowerPointApp.Quit
Set PowerPointApp = Nothing

End Sub

And the pie chart after the coding part.

Few end words

Now You should know how to update chart data in your periodic PowerPoint reports. Expanding this example code can make your weekly tasks take only a moment.

The choice is yours.