A few weeks ago, I wrote a post about linking Excel files into PowerPoint. Whilst PowerPoint is probably the application I use most (after Excel of course), in third place would be Word. If, like me, you are regularly creating Word documents, or using standard templates, then linking Excel files directly to your Word document can save a lot of time.
Whilst the basic process is the same as PowerPoint, the method of implementation is a little different.
Linking an Excel workbook to a Word document
The process of linking an Excel workbook to a Word document is straightforward. Simply copy a range from Excel. Then, in Word click Home -> Paste (small arrow). There are various options, as default we can click the icons for Paste Link and keep formatting or Paste Link and merge formatting.
But, if we click Paste Special… it provides even more options. Click Paste link and then select the paste type. Finally, click OK to paste the Excel content into the Word document with your selected options.
Notice the number of paste options available to you. I especially like Unformatted Text, as it can be used to add variable information to a standard sentence.
Within Word, when selected, any linked text will highlight grey (unless the settings in Word have been changed). See the screenshot below.
One of the key differences to PowerPoint is the existence of Field Codes. This is the method by which Word inserts linked documents and other dynamic options.
You can view the code behind the Field Code by pressing Ctrl+F9 to view all Field Codes, or Shift+F9 to display just the selected Field Code. The screenshot below shows an example of a Field Code.
It is possible to manually edit the text in the Field Code. Along with changing the file path and cell address, it is also possible to change the “switches”. The switches are the pieces of text at the end which all have “\” followed by a letter or letters. The main ones are:
|\a||update link automatically (manual updating if switch not used)|
|\t||show the linked item as text|
|\p||show the linked item as a picture|
A lot more can be achieved through Field Codes, but that is really outside the scope of this post.
Update and edit links
The benefit of linking documents is that once created, you can update the linked values quickly.
Update link on opening
When opening the Word document you will have the option to update the document with data from the linked files.
Updating on demand
If you just want to update a single link you can right-click the link then select Update Link from the menu.
Through the Linked Worksheet Object submenu (as shown above) there are further options to Edit, Open and Convert Links.
Update with the “edit links” option
This option is available through the Linked Worksheet Object menu as shown above, or through the ribbon File -> Info -> Edit Links to Files (it’s in the bottom right corner, but only once the Word document has been saved).
The Links window will open, which provides various options to update, edit and break links.
My top tips
If you link Excel to Word often, you will soon find it doesn’t always behave how you might expect. Here are my top tips to get the best results:
- Use named ranges – adding rows and columns in a worksheet will not change the link reference in Word. For example, if Word is linked to Cell A1, then you insert a new column at the start, Cell A1 will become Cell B1. But, Word still references Cell A1. This would now be the wrong cell. A named range helps to ensure the correct range is displayed in the document.
- Word will not know if you change the name or file path of a linked file (see point 3 below for the exception). You need to ensure the most up-to-date version of the Excel file always has the same file name and file path, so that Word can find it.
- The fastest method of changing the source of an Excel file is to have both the Excel and linked Word files open. Then save the Excel file with a new path or file name. In this circumstance, Word will update the reference to the file automatically.