How to Link Excel Data with a Graph in Word or PowerPoint

by Jim Gordon
Microsoft Macintosh MVP
Revised January, 2014

These instructions were designed for users of Macintosh versions of Microsoft Office 2011. The instructions apply to Excel 2008 and Excel 2011 for Mac. Users of Windows versions of Microsoft office may wish to reference an article titled Graphs Don't Update Automatically on the PowerPoint FAQ web site.

Follow these instructions to create a graph that is linked to a range of data cells in an Excel workbook.
When the workbook’s data is changed, the new information in the range of cells will update the graph that is located in Word or PowerPoint.

Start with a range of data in an Excel workbook. Type the following into an Excel workbook to follow along with this example:

Sample data

Next, save the Excel workbook.  Leave the workbook open, as we will return to it.

Now switch to Microsoft Word or PowerPoint. Click in the document or on the slide where you want the graph to appear. In Word, the graph will appear at the blinking cursor position.

From the menu choose INSERT then OBJECT…
Then choose Microsoft Graph Chart.

Object type 

This will open a default graph in the Microsoft Graph program. You should see a spreadsheet and a small picture of your graph, and the program name in the upper left corner of the screen should be Graph, It should look like this:

MS Graph screen
      shot

Next, switch to Excel (click the Excel icon in the Dock, use ALT+TAB, or click on any visible part of the Excel workbook if it is visible in the background, or use Spaces, or whatever way you like to switch applications).

In Excel, select (highlight) the data range. Then use Edit > Copy from the menu (or Apple+C). If you have done this correctly, you should see the range selected and “crawling ants” around the data range in Excel. Don't click anywhere.

Excel range

Next, switch back to the Microsoft Graph program (it has its own badge on the Dock, or use ALT+TAB). Select (highlight) the sample data range.

Graph default data
      selected

Next, in Microsoft Graph, on the Graph menu choose EDIT > PASTE LINK. In the resulting dialog, click OK to replace the sample data with the linked data from Excel. Your data sheet should now look like this (View > Datasheet from the Microsoft Graph menu):

After paste

and the graph will look like this after you pasted the linked data:

Graphed data after
      pasting

You can customize the graph to your liking at this point. Use the Toolbar and Menus in Microsoft Graph to change the chart type and customize the graph as desired.

When you are done customizing your graph, From the Microsoft Graph menu choose QUIT AND RETURN (or Apple+Q) to return to Word or PowerPoint.

Save the Word or PowerPoint document. The link has now been made and saved.

~

To refresh the graph with the current information from Excel.

Method #1

Double-click the graph in Word or PowerPoint.
As soon as Microsoft Graph opens it will update the graph with the current information from the Excel workbook from which the graph was created and you can close Graph right away (Apple+Q) to return to your updated document.


Method #2 (This option is for PowerPoint 2011 only).

PowerPoint MVP Shyam Pillai made a free add-in that updates links.
Download the add-in by PowerPoint MVP Shyam Pillai
Double click the downloaded file named GraphUpdate.zip to extract the actual add-in file called GraphUpdate.ppa
Move GraphUpdate.ppa to a location that you can remember, such as your Documents folder. Consider making an Add-Ins folder in your Documents folder so you can store all your add-ins in one place.
You may delete GraphUpdate.zip file that was downloaded.

From PowerPoint's Tools menu choose Add-Ins
Click the Add button
Navigate to the Applications folder
Select the file GraphUpdate.ppa
Click the Open button
Click the Enable Macros button
Click the OK button
Now, at the bottom of PowerPoint's Tools menu is a new command: Update Charts

Use the Update Charts command whenever you want to update charts with fresh data from data source workbooks. The process takes some time and it will appear that nothing is happening or that PowerPoint is not responding for a while. Be patient while the add-in does its work to update all the linked data within the presentation.
Update confirmation

Caveats about using links

   1. If the name of the source Excel workbook is changed, or if the source is moved to a new location, the links to that source will break and will have to be re-created in order to work again.
   2. When you double-click or update a linked graph, Microsoft Graph will start up Excel if it is not already running. This is not fast and it will appear that nothing is happening for a while. Be patient.
   3. Be sure to make a notation in the data source Excel workbook that you have made a link to the workbook. If you delete the workbook or make it inaccessible then the linked graph will no longer work.

Learn More

Add-ins are applications that extend the capabilities of Microsoft Office. They can be robust Macintosh applications or very simple programs that you create yourself, often to automate repetitive tasks or to create a feature that you wish Office had but does not. You create desired functionality yourself using Visual Basic for Applications (VBA), which comes with Microsoft Office.

I encourage everyone who has Microsoft Office to take a stab at learning vba.

Microsoft Excel in Macintosh Office can use data sources such as web queries and Microsoft Access databases. It is possible to use almost any existing data source for PowerPoint and Word graphs via Microsoft Excel.

-Jim Gordon
Mac MVP
Co-Author Office 2011 for Mac All-in-One For Dummies