This post may contain affiliate links. Please read my disclosure for more info:

Linking Excel files to Word

Edit links in Word using Excel VBA

Linking Excel files to Word

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.

Excel Linked Word - Default Paste Options

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.

Excel Linked Word - Paste Special

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.

Excel linked to Word - grey highlight

 

Field Codes

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.

Excel linked to Word - field codes

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:

\aupdate link automatically (manual updating if switch not used)
\tshow the linked item as text
\pshow 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.

Excel Linked Word - Update Link on open

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.

Excel Linked Word - Right Click link

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

Excel Linked Word - File Edit Links

The Links window will open, which provides various options to update, edit and break links.

Excel Linked Word - Update Links Window

 

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:

  1. 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.
  2. 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.
  3. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *