Cannot Automatically Refresh Data in an Excel Table that was built in PowerPoint

Question

I have a PowerPoint Presentation in which I placed several blank Excel Tables (Insert Tab > Table > Excel Spreadsheet).  Within the cells of the tables I have many formulas (VLookUps, Sumproducts, etc.) that reference several other excel workbooks and collect/summarize the data I need to present.  Everything works fine and all tables are calculating correctly.

However, when I close powerpoint and reopen, the tables do not update unless I manually double click on each table.  Once I double click and activate the table for editing, then the formulas in the table work and recalculate the new data from the workbooks they are pointed to.  

Is there a way that I can have these formulas recalculate upon opening PowerPoint?

I am using Excel and PowerPoint 2010.

Thanks!

Answer 1

By doing that, you've created an EMBEDDED OLE object.  These only update when doubleclicked.

Instead, you need to create the information you want in an external Excel file, save it, then select the information you want, rightclick, choose Copy.

Switch to PPT, choose Home tab | Clipboard group | click the triangle under Paste and choose Paste Special.  In the Paste Special dialog box, put a check next to Link, make sure MS Excel Worksheet Object is selected then OK.

Answer 2

>> Then using the Paste Special (thereby creating a link) feature I can cut and paste the tables into the presentation.  

COPY and Paste Special, not CUT, but otherwise, yes.

>> The drawback there is that my presentation will not update with the new information unless I have first opened and refreshed the new workbook (the one I will create) which in essence is linked to the various workbooks containing the raw data I need to summarize.

This is something you'd have to test, but I suspect you're right ... PPT would likely look at the file's time/date and only attempt to refresh the link if the file's timestamp is later than it was at the time the link was created or last updated.

Answer 3

Thanks Steve!!!

That was my fear.

If I am understanding things correctly, my only choice then is to create yet another workbook containing the tables I need for my presentation.  Then using the Paste Special (thereby creating a link) feature I can cut and paste the tables into the presentation.  The drawback there is that my presentation will not update with the new information unless I have first opened and refreshed the new workbook (the one I will create) which in essence is linked to the various workbooks containing the raw data I need to summarize.

Is that accurate or is there a better way?