Automated presentations – linking of Excel data to PowerPoint

Creation of PowerPoint presentations, and especially updates of recurring PowerPoint presentations, can easily become tedious process prone to errors. This is especially true with presentations filled with (tabular) data and charts.

Linking of Excel data to a PowerPoint presentation can significantly speed up that process and completely eliminate errors. Tables, charts and other presentation items can be worked on in a more suitable tool than PowerPoint, Excel. Both values and also structure of the slides can be modified without modifying the presentation itself, again in Excel.

 

Consider the following example, we’ve created our slide in Excel, and it contains a pivot table with Top 10 suppliers in October, a chart, and some notes:

presentation slide created in Excel

Once we paste the new data export in the data sheet and Refresh All (of the sources in that Excel workbook), our pivot tables and charts will be refreshed. Notes are manually written in a merged cell, and will have to be manually edited in that merged cell.

First thing we should do in order to properly show our data is to remove the gridlines, i.e., we should uncheck the Gridlines under View:

remove gridlines in Excel

This is needed so that the Excel gridlines are not visible in PowerPoint.

By default, Excel background cells will be transparent, but if some Fill Color is applied, it can be removed with the No Fill option.

In this way, our data will have an easier time blending in with most of the PowerPoint Design Themes.

 

In PowerPoint, we can insert some content manually, for example, our slide title:

title in PowerPoint

We can select the data we want to link to PowerPoint, and then copy it:

select and copy desired slide from Excel

Next, we can paste it as link in PowerPoint:

paste special link in PowerPoint

As noted in the pop-up, Paste Link creates a picture linked to the source file so that changes to the source file will be reflected in our presentation.

 

This picture can be freely moved and/or aligned on the slide:

move align linked object in PowerPoint

It can also be freely resized:

resize linked object in PowerPoint

Right-clicking on the image will present you with few additional options:

edit, opern, convert linded worksheet in PowerPoint, update link

Convert will enable you to convert your linked image to a regular image, i.e., break that link.

Update Link can force data refresh if needed.

 

By adding Edit Links to Files command on your PowerPoint Quick Access Toolbar, you will have even more link manipulation options available. Most importantly, you’ll be able to set whether your links will be updated automatically or manually, and you can change your Excel source file. If for example, you want to create a new Excel source file every month, you can update your presentation with the help of the Change Source command:

Edit Links to Files in PowerPoint

A few additional notes:

Links in the PowerPoint presentations will update more easily if your target Excel file is open when you are changing links.

If your Excel source file is unavailable, for example, if you send your presentation by mail to someone, links will not be able to be updated by that person, but your linked images will remain as they are – for that individual, your presentation will (mostly) behave as if the links to files were broken.

Changing row and/or column width in Excel, hiding and grouping them can potentially distort your linked images, and they will potentially have to be reinserted into PowerPoint.

Anything added and/or modified out of the scope, in the upper example that would be Book1!slide!A1:P26, will not be visible in PowerPoint – in order to expand that scope, you will have to select a larger area in Excel and then reinsert your linked image into PowerPoint.

 

Additional options

 

You can insert your whole Excel sheet via Insert, Object and then Create from file:

insert object link PowerPoint from Excel

However, note that you will have very little control on what is actually being inserted here, as visible below:

insert object link PowerPoint from Excel, whole sheet inserted

 

You can also Embed your Excel file into PowerPoint:

embed Excel in PowerPoint

Once embedded, by placing and resizing the object available to you, double-clicking the object you are manipulating and zooming onto what you want to be visible, you can accomplish the same look as with linking:

embed Excel in PowerPoint after resizing

Implanting Excel file into PowerPoint in this way means that the whole Excel file is stored inside the PowerPoint file, or to be more precise, a copy of the Excel file you are embedding is stored.

That copy of your Excel file can be additionally edited, links to additional outside files can be added and existing links changed.

That copy of your Excel file will be shared together with PowerPoint.

Excel editor can be called by double-clicking the object:

edit embeded Excel in PowerPoint

This feature is useful if you really want to share your whole Excel files via PowerPoint.

However, there are security and file size issues to be concerned about (remember, your whole Excel file is stored inside your PowerPoint presentation).

Additionally, editing Excel files in this way can become extremely clunky very fast and application stability is often poor.

 

Leave a Reply

Your email address will not be published. Required fields are marked *