Automatic Updating of Excel Tables in PowerPoint Slides

If you have PowerPoint slides that report regularly updated financial or numeric data that comes from an Excel spreadsheet, you can save a lot of time by linking the data instead of retyping it. This technique also allows you to secure the data from others changing it and prevents accidental transposition errors. This article shows you the step by step procedure for importing linked data from an Excel spreadsheet into a PowerPoint slide (works in PowerPoint 2003, 2007, 2010, 2013).

Step 1: Create the table in Excel

The first step is to create the table of data in Excel so that it looks exactly the way you will want it to look on the slide, including titles, row labels, column labels, gridlines, text and background colors and text formatting.

Step 2: Copy the table in Excel

Using your mouse or arrow keys, highlight the table of data in Excel. On the Home ribbon, click the Copy button to copy the data table to the Windows Clipboard.

Step 3: Prepare the slide in PowerPoint.

Create a new slide in PowerPoint. The slide layout with a title only is usually a good choice if the imported table will be the main focus of the slide.

Step 4: Paste the Table on Your PowerPoint slide

On the Home ribbon, click the arrow below the Paste icon to bring up a sub-menu and click on Paste Special. In the Paste Special dialog box, select the Paste Link radio button and the dialog box will look like the following:

image002

Click the OK button to insert the table.

Step 5: Resize and position the table object

The table comes on to the PowerPoint slide looking like a picture type object and can be sized by dragging the corner white circle resizing handles. Drag the corner handles to make the table as large as you want it to be. You can also drag the entire table to a new position by dragging the center of the table object.

Now, any time the data is updated in the Excel spreadsheet, when the PowerPoint file is opened, the new data will be retrieved and displayed (if the system asks you whether to Update the linked data, always select the option that allows the data to be updated with the latest information). If you move the Excel file, the link will not be able to update as it won’t be able to find the file in the original location, which is what the link on the slide is pointing towards.

If you want to edit the data in Excel, you can double click on the table object on the PowerPoint slide and the Excel spreadsheet will open. If you want to prevent others from changing the data in the Excel spreadsheet, make the Excel file a read-only file. This will allow them to see the data in Excel when double clicking on the inserted table, but they won’t be able to save any changes to the file.

If you want to send the file to someone else or take the file to another computer to present it, the program will ask if you want to update the links when you open the file, but in this case you will select the option to use the current data since the source spreadsheet file is not available. You will still be able to see the data table, it will contain the current data only and not any updates since you last opened it on your machine where the Excel file is available.

This procedure is very helpful if you have financial data, performance figures or other data that may be prepared and approved by a compliance area and changes monthly or quarterly. It allows the latest data to be shown in the linked PowerPoint slides, making sure that the presentations are compliant with regulations that require updated data to be presented to clients.

This technique is demonstrated in this video: Slide that automatically updates from Excel.

Get a free ebook on How to Present your Excel data effectively to Executives

If you create and deliver financial presentations, check out my Kindle ebook “20 Tips for Effective Financial Presentation with PowerPoint” and this slideshare presentation.