You're creating a Word document that will contain tables and charts from Excel... but the data for both tables and charts will continue to change as you work on the document. You can't wait until you get the final data to paste the content into Word from Excel, because you need to share drafts periodically with your team.
So, do you copy and paste it over and over again, every time the data updates or every time you need to distribute a new draft? Nah ... you have better things to do!
Paste the Excel content with a link ... so that the data automatically updates.
Okay, well you already knew you could do that ... but lots of table formatting doesn't hold well when links are updated -- and then there's the fact that anyone who doesn't have access to the links (like the client this report is being created for!) will get an error every time they open the document. Not so great!
BUT ... you can link the data while the document is a draft and then just break the link (with a simple keystroke). You'll end up with pictures for the chart and regular Word tables for the tables. No extra work, and no error messages for your clients. Just wait until the final version to format those Word tables to avoid extra work.
For those who want the short answer -- to break a link, just select the table or chart and press Ctrl+Shift+F9.
For more detailed help pasting content as a link, and an explanation of what this keystroke does ... read on ...
Note: If you're thinking that it's easier to just paste the Excel content as embedded Excel objects (so you can edit them without access to the originating Excel file) ... I'd suggest that you think again. Keep in mind that any embedded Excel object embeds the entire workbook from which the content originated, not just the individual object. So, all of your backup data ... and perhaps unrelated, confidential data ... that exists on every sheet of that workbook, is accessible to anyone who gets your document.
To Paste Excel worksheet data as a linked Word table:
1. Select and copy the worksheet content in Excel, as you normally would.
2. In Word, go to Edit, Paste Special. In that dialog box, click to select the Link option button, and then click to select Formatted Text as the paste type.
When you click OK, you'll have a Word table linked to the Excel data. Just wait until the data is finalized and you break the link before spending much time reformatting the table ... because some of the formatting you do may not hold when the data is updated.
To Paste an Excel chart as a linked chart object in Word:
1. Select and copy the chart in Excel, as you normally would. (Use the article on this blog called Sizing Excel Charts for Word for help correctly sizing that chart before you copy it).
2. In Word, go to Edit, Paste Special. In that dialog box, click to select the Link option button, and then click to select either Picture or Microsoft Office Excel Chart Object as the paste type.
When you click OK, you'll have a chart linked to its original data source.
To break the links:
Select the table or chart and press Ctrl+Shift+F9
That's all there is to it! That is the keystroke combination for unlinking any Word field from its result ... and both embedded and linked objects in Word are stored as fields. So, when you unlink the chart object, you'll end up with a picture. And, when you unlink the table you'll end up with a regular Word table that you can format at will!
Note: it is a good idea to make a note of the original Excel file name and location before breaking the link, in case of future editing needs.
