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?
Best Answer
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:
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:
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.