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

You probably arrived at this page because you are a business professional who has to present regularly to management or executives on analysis you have done in Excel. You don’t want to have to retype a table of numbers or recreate a graph in PowerPoint. Not only is that time you don’t have, but it could lead to errors, which would be embarrassing. So you want to link the table or graph in Excel to your PowerPoint slide. Then when the numbers change next week or month, the PowerPoint slide can automatically update.

It sounds like a great plan. Except every great idea does have potential downsides you need to know about. Here’s what you need to consider before deciding to link a table or graph from Excel to PowerPoint:

  • Might the boss ask for a last minute update because new numbers are available? If so, your link may cause you problems. If you don’t have access to the source Excel file, because you are off-site or don’t have a secure connection back to the server, you can’t update the numbers for the boss.
  • Will others need to make updates to the table or graph in PowerPoint? If so, they may get frustrated when they get an error message that the source file is not accessible. Linked data requires access to the source file to make changes and if they don’t have security permission to the file location, they are out of luck.
  • Does your organization care about branding? Most do, and they have a specific standard for the look of slides, including colors, fonts, etc. If you link an Excel graph, it won’t likely match that formatting, because it will use the default Microsoft Excel look. Now the presentation isn’t consistent with brand standards.
  • Do you want the update of the table or graph to happen automatically? That is not possible. Linked tables or graphs need manual intervention to be updated. Some methods will pop up a Security warning message when the PowerPoint file is opened. If someone isn’t scared that you have just given them a virus, they will read that clicking on the appropriate button in the dialog box will update the data. Other linking methods require a click on an update button for every table or graph. And if your file has a combination of these methods, you have to remember which is which for each slide.
  • Does the source Excel file change regularly? If many people can update the file, you may experience the data in your presentation changing unexpectedly when you update the links. If the file name changes each week or month, the data never gets updated since the links point to the original file name.

If you still want to link the graph from Excel to the slide in PowerPoint, scroll down to see how to do it.

If you don’t use linking, how can you easily update the graph in a presentation each time without a lot of extra work? I suggest you consider creating the graph in PowerPoint with data copied from Excel. Set up the data required for the graph in a set of Excel cells. When you create the graph in PowerPoint, use Paste Special – Values to copy the data from Excel. You can format your graph in PowerPoint and it will be compliant with your organization’s branding standards. Because the data is in the PowerPoint file, you (or others) can update it at the last minute if needed.

When using this method, how can you make sure the latest data is used for the graph? By using a consistent procedure that includes documentation. Follow the same procedure each month for copying the cells from the current version of the Excel file to the data table for the graph on the appropriate slides. Then, update either a text box on the slide or the Slide Notes with the date and time of the update and the file name the data came from. That way, you, or anyone else, will know where the data came from and when it was last updated.

If you are presenting financial information, this article explains the steps you should use to make an effective visual presentation. If you are part of a team who presents financial or operational information to management and executives, my customized training delivers immediate ROI. If you have to communicate the results of analysis often, you can learn about many more ways to use Excel tables and graphs in PowerPoint with my ecourse After the Analysis: Communicating results from Excel in PowerPoint.

If you want to link a graph from Excel to the PowerPoint slide, here are two methods to do it. 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

Submit a Comment

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