A table or chart in Excel can be put into a Word document in two different ways but there are traps that Microsoft doesn’t mention in their help. We’ll explain what’s happening and how to fix it.
This looks like a standard Word table and it can be formatted like a table … but it’s a special kind of table. It’s really an Excel worksheet linked into the Word document.
The cool thing is that Word actively links into the worksheet, meaning whenever the worksheet changes, so will the document.
This is possible in Microsoft Office; it’s been available for many years. There was a time when Microsoft could not stop demonstrating embedding Excel documents into Word. All manner of different options were available with plenty of acronyms thrown in to either impress an audience or confuse them. Sadly, computers found it hard to cope. Linking a worksheet into Word took up a lot of resources. It worked great in Microsoft’s carefully rehearsed demos but not so much in the real world. If embedding worked at all, it was often very slow or unstable.
These days the software and hardware is a lot better. If you gave up on embedding worksheets as a bad joke, here’s an opportunity to try again.
Copying vs Linking
There are two broad options for linking a worksheet into a document:
- a fixed, one-time copy or snapshot of the Excel worksheet.
- a dynamic, changing link. Any changes to the worksheet will appear in the linked Word document too.
In this article we’re going to focus on the primary method for dynamic or linked worksheets. There are others but this is the simplest and (mostly) easiest to work with.
Copy and Paste Special
Firstly, select the worksheet section that you want in the document and Copy it to the clipboard.
Then switch to Word and choose Paste from the Home menu. There are a few more options than you normally see.
Two of the Paste options are will link the worksheet to the document (look for the little chain icon – chain … link .. geddit?).
Link and Keep Source Formatting – the worksheet segment appears with the look of the original worksheet.
Link and Use Destination Styles – uses the default table formatting of the document.
Choose whichever closest suits your needs. As you’ll see, it’s easy to change the look of the table.
We’ll look at updating options for the worksheet a little later. But first lets see how linked worksheets appear in the real world.
In official lessons about this feature, that’s as far as you’ll be shown. In Microsoft’s world, worksheets are pasted in perfectly and work first time. Ha!
The Real World of linking Excel into Word
When you paste a worksheet into Word, there’s usually problems with the result. Here’s a typical Paste Link in Word:
See the pasted table and compare it with the Excel worksheet shown above? The formatting is all wrong. The top line is wrapped across two lines and each data row height is wrong. This happens almost inevitably when Paste Linking a worksheet.
You’ll need to fix those formatting problems but happily it’s pretty easy.
Under the hood
A little digression to discuss what’s happened in the Paste Link. If you understand what Office has done, you’ll be more confident in making changes.
In the pasted data above, Word has created a table. It’s a normal Word table with the Table Tools available on the ribbon. Each Excel cell is inserted into a corresponding table cell. That’s why they are in grey when editing the table; to indicate they are linked data.
The important point is that it’s a Word table – you can format it like any other Word table. Use the Table | Design tab to totally change the look, regardless of your initial Paste selection. You can also change formatting of individual rows, columns or cells. Just one example, we’ve changed the text color and enlarged the text in a single row.
If you’re interested, all this is done via a LINK field code (press Alt + F9 to reveal codes)
Fixing Formatting
How to fix the formatting errors after pasting? It depends on what mistakes Word has made (they vary) but here’s some tips and examples.
Turn on Show All from the Home tab. That will reveal the exact characters in each cell.
With Show All, it’s easy to see that the row height problem is caused by excess spaces in the Sales cells. Remove the spaces and the row heights are fixed.
In our tests, these excess spaces reappear whenever you update the link. We’re not sure why because the right-aligned % column doesn’t have the same problem. If you can figure it out, please let us know.
The heading problem is fixed by changing the column width slightly by dragging the table divider.
Here’s a ‘fixed’ linked table with various minor table formatting changes including right-aligning the table with text wrapping.
Updating the link
The main benefit of Paste Link is that changes in the Excel worksheet are reflected in the linked Word document. Do that by right-clicking on any data element in the table and choosing Update Link. Or press F9 (update fields).
Here’s the updated table from a few weeks later.
All it took was an ‘Update Link’ command and, annoyingly, a little reformatting.
Tip: include in the worksheet the date or version of the data. That will help ensure that you know which version of the worksheet is showing in a Word document.
Unlinking
Sometimes you’ll need to break the link with the original worksheet. For example, a document about result to the end of a financial year should not be updated with later figures.
The official way to do that is right-click on the data, choose Linked Worksheet object | Links (see screen image above) and choose Break Link.
The table will remain but now the cells have text, not linked data.
The faster method is the Ctrl + Shift + F9 shortcut which converts a field code into its results only.
PowerPoint slides in Word documents
A simple way to convert PowerPoint slides to Word document
Hiding stuff in Word, Excel and PowerPoint
Highlight tricks for Word, PowerPoint, Excel and Outlook
