Automate the Update Of Big PPTs - Say NO to Copy/Paste

Periodic update of huge PowerPoint presentations is still part of deliveries of an analytical professional. Despite the advances in web based dashboards and reporting systems some customers still prefer to have their outputs nicely presented in a deck. The transfer of data from Excel to PowerPoint slides happens manually more often than we would like to admit. This is OK of course for small sized decks. MS Office offers lots of flexible options to link a slide to Excel data source. You could link or paste data or a chart with various options for formatting. They work pretty well and I welcome you to try them. However, the option to use with decks with large number of slides has to be chosen carefully. I consider huge a deck with more than 50 linked charts or data sources in it.


The problems come from maintenance and update of all the links as the PowerPoint tools are not that flexible. There are also issues with the update speed (tip: use set to manual the link update option for better results). Using links is messy when the deck is sent out to a customer and she makes changes on some slides and then it comes back to you for updates. Another painful situation is updating the  data on a slide when the source file is not available and then relinking back to original source. These and other nightmare scenarios for large decks require really to think twice and spend bit more efforts in organizing them. I found that the solution that works the best for me in this case is to avoid links between ppt and Excel and automate the transfer of the data.

Before going further with that I would like to make the point that I step into automation of a ppt only it is big, the structure is relatively stable and it has to be updated regularly. If it is just one-off deck, then I just link the data while working on it and break links for the delivery.

The idea is simple -  the charts are created in PowerPoint and the data behind them is transferred from Excel by a macro. Very much as if it is done manually. The key elements are:
- Name or full path to the ppt to be updated.
- Description of each slide

Slide Description Sample

Please see an example from a deck I have recently prepared. The table describing the slides includes the slide number, chart name on the slide and the source range name for it. There are also details for aggregation required by the data, e.g. slide 15 has the value data by technology for all value spaces and technologies.
 
- Data sources. These are various tables that hold the data to update the charts. Usually the data is some aggregation of few data sources and I make set of data block dynamically dependent to a parameter. The most convenient way is to use named ranges. Please see below a sample of a data source - it is dynamically updated depending on the parameters on the slide description (sample numbers used). A headline with the name of the range helps quickly focusing on a range.

Data Source

- A "governor" - few formulas that pull out the information for a slide and referred by the data sources. Please see below an example.

This is a helpful tool for extracting the data for each slide.I prefer not to keep absolutely everything automated to be able to see the data pulled for a specific slide and other reasons.

- A macro. Last but not least. The macro opens the ppt and slide by slide updates the chart on it. Macro is not complicated and you can find sample code on many websites with a simple search. Of course, some experience with VBA is required.


For the example purposes I took the simple case of one chart per slide but  going to two or more per slide requires simple tweak of the formulas in the "governor".



It usually took an intern at least 4h to update 140 charts in a deck. This setup and the macro do the job for 10 min. The extra effort in this approach is in describing the slides- the macro can be used for any deck and the data sources have to be created anyway. This extra effort would take an hour for 100 charts but the savings in time and boredom are immense.