MS Word

Keep an Excel Chart Updated in a Word Document

The Problem:

My department keeps its production data in an Excel spreadsheet with a chart that gives a quick idea of how things are going. I need to put that chart in our Monday-and-Thursday report for a visual update. So far, I've been copying the chart and pasting it into the Word document. This works fine, but it means that to make sure it's up to date, I need to paste in the chart about five seconds before I print the documentwhich feels more than a little tense.

The Solution:

It sounds as though you'd do better to link the chart to your report template. Each time you create a new report based on the template, it'll pull in the latest version of the chart. Follow these general steps:

  1. Create the chart as usual in Excel. Right-click the chart and choose Copy from the shortcut menu.

  2. Switch to Word (or start Word), open the report template, and position the insertion point where you want the chart. Choose Edit » Paste Special, click the item called Microsoft Office Excel Chart Object (Microsoft Excel Chart Object in Word XP or Word 2000), select the "Paste link" option rather than the "Paste" option, and click the OK button.

  3. In Word, format the chart as necessary: right-click it, choose Format Object from the shortcut menu, and use the options in the Format Object dialog box. For example, you might change the layout of the chart by wrapping the text of your report around it.

  4. Save the template and close it.

When you create a new document based on the template, Word warns you that the document contains links that may refer to other files (see Figure 9-14). Click the No button: you don't want to change the chart in the template itself, only in the document that you create based on the template.

Figure 9-14. Link a chart to a template to make the chart appear in each document you base on the template. When you start a new document based on the template, choose not to update the chart in the template.

You'll still need to update the chart in the report before you print it, but you can have Word do this for you. Choose Tools » Options, click the Print tab, and check the "Update links" box. Word then updates the document automatically before printing it.

If automatic updating will cause problems with other links that you don't want to have updated, update the chart manually: right-click it and choose Update Link from the shortcut menu.