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

by Jim Gordon
Microsoft Macintosh MVP
Revised May, 2008

These instructions were designed for users of Macintosh versions of Microsoft Office, excluding Office 2008. 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:



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 that 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).

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.

Excel range

Next, switch back to the 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, hold the SHIFT key down on the keyboard while you choose EDIT > PASTE LINK on the Graph menu. Click OK to replace the sample data with the linked data from Excel. Your data should now look like this:

After paste

and the default graph (VIEW > DATASHEET from the menu if you can’t find the graph) will look like this:

Graphed data

You can customize the graph to your liking at this point. Microsoft graph has the same set of formatting tools as Microsoft Excel.

From the 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 (an option for PowerPoint).

PowerPoint MVP Shyam Pillai made an 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 the Applications Folder.
You may delete GraphUpdate.zip

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, any 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