Link Excel spreadsheet into PowerPoint presentation

I need to create a weekly PowerPoint presentation for various levels of management, to keep them apprised of the status for several categories of action items my department tracks. During normal operations, most of these action items are tracked via several Excel spreadsheets. There's a separate spreadsheet for each action item category.

(Yes, I know we should be using a different application, and storing this sort of stuff in a database, instead of spreadsheets. That's in work, but I still need an interim fix for this while we're stuck with Excel.)

The spreadsheets used to track the action items are very detailed, but the presentation needs to be kept brief, so we usually omit some of the specifics from the presentation. Below are some example fields which are commonly found in the tracking spreadsheets. Items in bold are the only fields we want to show in the presentation.

• ID
• Title
• Description
• Status
• Progress Notes
• Current Responsible Party
• Affected Customer
• Start Date
• Due Date
• Project Milestone(s) & Milestone Date(s)
• Document Reference(s)

Currently, the process to create the presentation entails manually going through the Excel spreadsheets and copying/pasting the data into tables created on the PowerPoint slides. However, it would be much more preferable if we could get this process automated to some degree. Also, in addition to keeping these slides up to date for each week's presentation, I need a method to save a static snapshot on a monthly basis for archiving purposes.

What I need is a way to link the spreadsheets into the presentation slides. Ideally, the solution should be able to:

• Pull only the columns needed from each sheet.
• Pull only non-completed action items from each sheet.
• Refresh data from the source sheets, either automatically or on-demand, with minimal user action required.
  • (i.e.: An on-demand refresh should only take a few clicks per sheet - or, more preferably, just a few clicks for the whole presentation - and not require a lot of repetitive copy/paste/etc.)
• Automatically add/remove slides when needed, to accommodate the size of the data sets.
• Permit saving a static copy of the presentation on-demand.
• Be compatible with both Office 2010 and Office 2013.

Is there a way to link my spreadsheets to the presentation, which can serve all these needs?

Solution 1:

If you are allowed to download and install the standard (and free) Microsoft product "Power Query", you can. More information about the use of Power Query can be found here. It is available from Office 2010 and up. It provides you with a lot of tools to select and merge data from different sources.

In your case, do the following:

• Create a new Excel workbook.
• Using Power Query, load the first action list to your Excel sheet .
• Use "Append" to append the other action lists to the resulting table. At the end, you should have a (dynamically linked) combined list of all your action lists.
• Using Power Query you can delete and rearrange the columns, sort the data and set filters on the columns. I recommend that you do the selection, sorting and filtering here in the Power Query, because if you do this in the final table in Excel, you need to re-apply all those filters every time you update the table.
• Save the workbook and close Excel.
• Start Powerpoint and on the Insert tab on the Ribbon choose Object and select "Create from file" and select the newly created Excel workbook with the combined action lists.

If you need to update the data, open the Powerpoint presentation and open the Excel object en click on the Refresh button and your data is updated. That's just a few clicks to update all of the action lists (cool, right?!)

This solution works for Office 2010 and 2013.

Finally there are your requirements about spanning multiple sheets and saving a static copy. There is no out-of-the-box answer to that, but I'd like you to consider to do this:

• Keep a "master" version of your Powerpoint with the reference to the Excel file in it (hint: make this file read-only).
• Every week, update the data with a few clicks as described above.
• Copy the Excel table (even if it is too big for one sheet) and paste it as a picture (Paste - Picture or Paste - Paste special... - Microsoft Office Graphic Object).
• Now you can duplicate the entire PowerPoint slide with the picture on it and use the cropping tools (select the picture and choose on the Ribbon tab Picture - Format - Crop) to select the right items on each sheet.
• You can reduce the size of the PowerPoint presentation by selecting a picture and go to the Ribbon: Picture - Compress Pictures and choosing the appropriate settings.
• Finally the sheet with the original Excel data can be deleted from this week's version of the presentation.

It is not really automated, but it keeps a non-editable version of your data in the presentation and it fulfills all of your requirements. The advantage of this approach is that all of the work can be done from the Powerpoint presentation itself.