Share via

Excel charts not updating in PowerPoint

Anonymous
2019-10-25T14:16:55+00:00

Hi,

I have a PowerPoint presentation that contains 76 charts that are all linked to Excel. When first opening PP it prompts me to update the links which i do but non of the charts seem to update with the real time data that is in Excel. I can manually update each chart but this takes too much time and i would prefer a way that allows me to automatically update the charts. 

Anyone have any idea's on what i could do to stop this issue. Have i hit a hard limit for charts in a PP file? Also both files are quite large withe the PP at 265,303KB and the Excel file at 64,562KB. 

Thanks,

Sean

<The thread has been moved to the correct category by forum moderator>

Microsoft 365 and Office | PowerPoint | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Steve Rindsberg 99,161 Reputation points MVP Volunteer Moderator
2019-10-29T15:18:32+00:00

>> Surely there must be a link for it to open the excel file at the correct table?

There are a couple ways of creating charts in PPT:

  1. Directly, as in via Insert | Chart. That produces an embedded, not linked, chart. The chart and all of the data required to produce it are part of the PPT file.
  2. There's also a way of creating a chart in PowerPoint but paste/linking data from elsewhere into the PPT chart's data sheet.  PPT doesn't update these with changed source file data until you doubleclick the chart to edit it (and may update when you manually force an update from the links dialog).
  3. You can create a chart in Excel, copy it, then go to PPT and choose Paste Special, As Excel Chart Object and put a check next to Link.  As long as the link points to a file that hasn't moved, these will update when you open the PPT, force an update from the links dialog or run VBA similar to what I posted above.

BUT:  PPT has a nasty little habit.  It will let you create a link as above even though you haven't saved the Excel file. That produces a chart in PPT that's linked to nowhere (even though you might have saved the file after creating the link.)  ALWAYS save the XL file first before starting to link between it and PPT.

You can also copy/paste from XL to PPT in ways that create embedded rather than linked charts, but from your description, I don't think that's what's happening here.

Was this answer helpful?

10+ people found this answer helpful.
0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-10-29T15:50:52+00:00

    I have just deleted some of the charts and done the special paste and it is now automatically updating!! (Just have to do this for all 77 slides now 😂)

    Thanks for the help.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2019-10-25T14:53:34+00:00

    Hi Steve,

    Sorry, i should have been a little clearer. On opening after clicking update on the popup, the charts don't update but I can then go through and press refresh data on each individual chart and the data pulls through from Excel. 

    *** Edit ***

    I have tired running the VBA but i still get the same issue.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Steve Rindsberg 99,161 Reputation points MVP Volunteer Moderator
    2019-10-26T17:16:46+00:00

    OK, thanks for the update, Sean.

    As a diagnostic, try this:

    Select one of the charts in question, then run this bit of VBA:

    Sub IsItLinkedOrIsItMemorex()

    Dim oSh as Shape

    Set oSh = ActiveWindow.Selection.ShapeRange(1)

    If oSh.Type = msoLinkedOleObject Then

       MsgBox("I am indeed a linked object")

       MsgBox"I'm linked to:" & vbcrlf _

             & oSh..LinkFormat.SourceFullName

    Else

      MsgBox("Sorry, no links here. Maybe that's why I'm not updating?")

    End If

    End Sub

    That'll tell us more about exactly what type of object it is and where the link points, though if PPT can't find the linked files at all, it'd likely bark at you about that whenever you opened the PPTX, and wouldn't be able to manually update the links.  But hey, it's just another line of code ...

    Let us know the results.

    Was this answer helpful?

    0 comments
  4. Steve Rindsberg 99,161 Reputation points MVP Volunteer Moderator
    2019-10-25T14:39:54+00:00

    Hi Sean,

    When you say "but none of the charts seem to update with the real time data that is in Excel" do you mean that they update on opening the PPT/X but don't continue to update as data is updated real time in the Excel workbook?

    If so, that's normal behavior; PPT only updates links when the file opens (if they're set to autoupdate) or when you manually force the update per link as you've mentioned you can do.

    Try running this VBA on a copy of your PowerPoint file:

    Sub UpdateLinks()

    Dim oSl As Slide

    Dim oSh As Shape

    For Each oSl In ActivePresentation.Slides

        For Each oSh In oSl.Shapes

            If oSh.Type = msoLinkedOLEObject Then

                oSh.LinkFormat.Update

            End If

        Next

    Next

    End Sub

    Was this answer helpful?

    0 comments