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 learning how to link Excel to Word can save a lot of time.
Whilst the basic process is the same as PowerPoint, the method of implementation is a little different.
Link Excel to Word
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.
Top Tips to link Excel to Word
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.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: