WORD AND POWERPOINT

WORD AND EXCEL

Keep an Excel Chart Updated in a Word Document

The Annoyance:

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 Fix:

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.

Transfer Data from Word to Excel

The Annoyance:

Every now and then, I receive data in a Word table that I need to paste into an Excel spreadsheet. Each time I do this, I seem to get different results: sometimes the text just snaps into the spreadsheet cells perfectly ; other times, I get Word's table borders and font formatting in my spreadsheet, andworsesome table cells are spread across multiple rows. If I undo this unsuccessful paste and repeat it, I get different results again. If it makes any difference, this is Word 2003 I'm using.

The Fix:

Word is trying to be helpful, but it's not quite managing what you want. Word 2003 tends to behave as you describe; Word XP and Word 2000 behave a little differently (and Word 2000 doesn't have the Paste Options Smart Tag).

Here's what's probably happening:

• If the table you copy has only one paragraph to each cell, when you paste, the data snaps into place with one Word table cell to one Excel cell . This is probably the result you want.

• If the table contains cells that have two or more paragraphs in them, Word pastes in the table as a Word object by default. That means the that table keeps its Word formatting, so you get the text formatting and borders, and multiparagraph cells end up occupying multiple rows (one row per paragraph). You can fix this quickly: click the Smart Tag button and choose Match Destination Formatting to redo the paste operation as plain text. The multiparagraph cells will still occupy one row per paragraph, so you'll need to fix them manually, but Word's formatting will be gone.

• If you undo the paste operation and then repeat it, Word may say to itself (in binary) "Oh, she didn't like what I did; I'll do it the other way this time" and paste in plain text instead of the Word object. This will give you the effect described in the previous paragraph, but it doesn't happen consistently (and you're not allowed to ask me why).

If this degree of uncertainty annoys you, use Paste Special instead: right-click the upper-left destination cell, choose Paste Special from the shortcut menu, choose the Unicode Text option in the As list, and click the OK button.