MeadInKent
| Excel and Powerpoint | Conditional formatting | Mail Merge with Excel |
excel

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

Would you like to learn more?

book

Excel 2010 Bible by John Walkenbach (with CD)

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

An excel table using conditional formatting to create traffic light performance indicators

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.

An Excel list of words and values (created by functions) available to be used within a Word document

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.

A Word document containing 5 linked values

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

(PDF format) Example of a Word document which can be automatically updated by Excel

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
Document is in PDF format Click here for details about
obtaining this file

file: excel-to-word.htm meadinkent.co.uk 2014 Last updated Feb14 CMIDX S5 P15 Y