Linking a graph in PowerPoint to the Excel data so the graph can automatically update when the Excel worksheet changes

If the graphs you want to use in your PowerPoint presentation will change regularly, it can be a hassle to create them from scratch every time there is an update to the data. It is likely that the data used to create the graph is coming from an Excel worksheet, so why not link the graph on the slide directly to the Excel data.

There are two methods of creating the link. I will describe each method, and then show a table that summarizes the differences. I’ll end with some recommendations of when each method should be used.

Each method starts with you creating the graph in Excel first, because we will be copying the graph created in Excel into the PowerPoint slide.

Method 1: Basic Paste

After you have created the graph in Excel, click on it and copy it (press Ctrl+C or click the copy button on the Home ribbon). Switch to your PowerPoint slide. Paste the graph on the slide by pressing Ctrl+V or clicking the Paste button on the Home ribbon. You can now edit the graph using the PowerPoint graph tools in the Graph ribbons (there are three ribbons on PowerPoint 2007 & 2010 and two ribbons in PowerPoint 2013 & 2016).

Method 2: Paste Link

When using this method, after you create the graph in Excel, you must format it exactly how you want it to appear on your slide since you cannot format it in PowerPoint. Set all legends, labels, axes, gridlines, and other settings for the graph. When it is formatted, copy the graph. Switch to your PowerPoint slide. Click on the arrow below the Paste button on the Home ribbon and click on Paste Special. In the Paste Special dialog box, click the Paste link radio button, select Excel Worksheet Object in the list and click the OK button. The graph is now a drawing object in PowerPoint that can be moved and proportionally size, but not edited.

Comparing the two methods

 Basic Paste methodPaste Link method
Graph must be fully formatted in Excel before copying to PowerPoint?NoYes
Graph is inserted on the PowerPoint slide asGraph objectDrawing object
Can format the graph in PowerPointYesNo
Can resize the graph in PowerPointYes, all directionsYes, only proportionally
Can animate the graph in PowerPointYesNo
Notified of the linked file when the PowerPoint file is openedNoYes, via a Security Warning dialog box
Method of data updateUser must click on Refresh Data button on Graph Design ribbonUser can click button in Security Warning dialog box displayed when PowerPoint file is opened
Access to full worksheet in Excel (all tabs and data)Via Edit Data button on the Graph Design ribbon in PowerPointDouble click on drawing object on PowerPoint slide
Access to data if source file not available (ie. if PowerPoint file emailed to others)NoNo

Recommendations

In most situations, the Basic Paste method will work well. It allows you to format and animate the graph in PowerPoint, giving you better control of how you present the graph. The disadvantage is that you must remember to manually click the Refresh Data button to get the latest updated data for the graph. If you need the updates to be more automatic and don’t need the formatting or animation in PowerPoint, the Paste Link method would be a good choice because it allows for updating the data when the PowerPoint file is opened. The disadvantage is that the PowerPoint file opens with a Security Warning dialog box, which can be unsettling for others who receive the file by email.

With both methods, you must be careful of who has access to the source Excel file from the PowerPoint slide. Anyone who can access the Excel file can see all the tabs and data, including any confidential information. If this could be a concern, I suggest you create an Excel file that contains only the data for the graph and the graph itself, leaving the bulk of the data in another Excel file that isn’t linked to the PowerPoint file. Alternatively, you can break the links to the source Excel file before you send the file to others. In PowerPoint 2010 and above, in the Info section of the File ribbon, there is a button to Edit Links to Files. You can select the links and click the button to break the links. This disables any future updating of the data, but protects your data from being seen by others.

 

Want an in-depth e-course on using Excel data in PowerPoint? Check out my After the Analysis: Communicating results from Excel in PowerPoint e-course

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

Submit a Comment

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

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

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

This 24 page ebook covers making your message clear, having backup data available if needed, advanced graphing techniques, and linking Excel to PowerPoint

Thanks for requesting your ebook! Check your Inbox for the confirmation email is automatically sent. Click on the link in that email to confirm and the ebook will be on its way to you!