Excel as a source of linked fields, tables and charts in a Word document

Word is generally a better program than Excel for creating reports that contain a mix of narrative, tables and charts. People often use an Excel spreadsheet to calculate the values which are subsequently reproduced in a Word report. If you regularly update a report, instead of manually re-typing or copying and pasting images to transfer the data between programs, it is worth investing some time and effort to directly link the values. This will enable to your Word report to automatically update itself and incorporate any changes made to the linked Excel spreadsheet(s).

This example shows how you can link not only Excel tables and charts, but also words, dates and numbers within the paragraphs of your Word document. This saves time and also prevents careless errors where old values are overlooked and not updated.

Create your source data in Excel

It is best to gather all of the required data onto a single worksheet. This makes it much easier to check that the values are all updated consistently. Create tables with the formatting you wish to see in the final Word document. The purpose of this exercise is to make regular updating a simple process. It is therefore helpful to ensure that any chart titles or labels which contain values such as period descriptions are linked to worksheet cells and can be updated automatically.

You can also use Excel formulas to create words such as 'better, worse, profit, loss, January' which will feed into sentences of the report. Many of these can be simply created using IF() functions e.g. =IF(A10>B10, "decreased","increased"). Number values such as the total expenditure can also be included. Use the ABS() function to remove any negative signs which are not required if the sentence includes words such as 'profit' or 'loss'. Don't bother formatting your words and numbers.

A Word document with 'live' links to values in an Excel worksheet

Open a new document or edit an existing one and write the outline of your report, leaving spaces where you wish to include words, numbers, tables or charts from Excel. The next step of the project is then a methodical copy and paste exercise.

Copying words and numbers - Copy a single Excel cell containing a word or number. Position the cursor in Word and select [Home] Clipboard | Paste Special | Unformatted text. Then click the Paste Link option and OK. By selecting unformatted text, the value will adopt the formatting of the words around it and will blend into your document. The Paste Link option means that it will automatically update itself to reflect any changes to the source spreadsheet. Note that the paste options on the (right click) short cut menu can produce unexpected results such as inserting paragraph breaks after the fields. The paste options on the ribbon should be used.

The following extract contains 5 values pasted from Excel. The formatting style is not taken from Excel.

Word treats each of these linked values as Fields and allows you to customise the formatting of numbers. Click on a number field to select it and then press <Shift> + <F9> to reveal the field code. To format a number add an extra code instruction: \#"picture_code" The whole code for the field above is: {LINK Excel.Sheet.8 "D:\\MyDocs\\Excel\\xl to word example.xls" "Sheet1!R29C2" \a \t \#£#,##0}

The codes can all be toggled and switched off by pressing <Alt> + <F9>. To force the document to refresh and update the field values, select [File] Info | Edit links to files |.

Copying a table or chart - In theory this is very simple but in reality we are faced with the unpredictable way in which pasted objects tend to jump around Word pages. It is therefore sensible to save your Word document before continuing.

Select the range of cells containing a formatted table (i.e. with borders and shading) and then Copy it. Position the cursor in Word and select [Home] Clipboard | Paste Special | Formatted text (RTF). Then click the Paste Link option and click OK. You may choose the Picture option instead of Formatted Text but this results in slightly larger file sizes and will result in all cell borders being printed (unless switched off in Excel).

Click once on an Excel chart and then Copy it. Position the cursor in Word and select [Home] Clipboard | Paste Special | Microsoft Excel chart object. Then click the Paste Link option and click OK.

When an Excel table is pasted as formatted text the rows will inherit the default Normal paragraph style. If this results in too much spacing above and below the text in each cell, try modifying the [Home] Paragraph | Paragraph dialog box | Indents and Spacing | and set the before and after values to 0 or 1.

If you delete a table or field which has been Paste Linked, briefly toggle on all of the field codes (<Alt> + <F9>) to ensure it was properly deleted. If necessary, delete the field code itself.

To print a series of Word documents, each one with individual variables (such as different names and addresses) use mail merge with Excel as a data source.

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file. It has been rewritten for Excel 2010.