Any Microsoft (MS) Word documents with financial references will almost always contain some type of table explaining expenses, time periods, numbers of employees and more. These tables are frequently part of a larger MS Excel spreadsheet.
A potential problem arises when a change takes place to the spreadsheet between the time the document is prepared and the time it is distributed. This problem can be eliminated by using the Paste Link feature in Paste Special. (For more information about the Paste Special command, see Copy Text from Web to Word with Paste Special and Using Paste Special Within Excel.)
Why Create a Word/Excel Link?
For the purpose of clarity two files will be used in this example.
RevPredict: A Word document explaining what the company can realistically expect in the way of revenues for the coming fiscal year.
RevProject: A spreadsheet that was used to assemble the raw data and make calculations to be used in support of the RevPredict document. Portions of the spreadsheet table, as well as charts and graphs, will be pasted into the RevPredict Word document.
Today is Thursday and the report will be presented to the board of directors on Tuesday next week. By end-of-business on Friday the report is completed. Monday afternoon, notice is received that due to economic factors, like manufacturing costs and salary negotiations, have changed enough that data must be changed in the spreadsheet. While there are only two or three manual changes to the spreadsheet the resultant recalculations create significant changes that affect individual departments, branch locations, and ultimately the corporate bottom line. This data has already been pasted in the RevPredict document. It would take hours to manually edit all the changes required.
If Paste Link was used in pasting spreadsheet segments, the only manual change required would be the two or three changes to the raw data in the spreadsheet. Because the spreadsheet is set to automatically recalculate, all related cells and formulas are readjusted in a couple seconds. When the Word document is loaded, all related references have been updated.
Creating the Word/Excel Link
Before creating the Paste Link it will be important that the user pay strict attention to the name and location of both the Word document and the Excel spreadsheet. For this example it is assumed that the spreadsheet and document were prepared on Thursday before the presenting date.
Open the Word document
Open the Excel spreadsheet
Select the cells or graph to be copied to the document and press Control + C.
Position the cursor in the Word document where the spreadsheet data is to appear and activate the Paste Special function. (In Word 2007, click on Home/Paste down arrow. In pre-2007 Word click on Edit/Paste Special.)
A list of how data should be pasted appears and the first choice, “Microsoft Office Excel 200X Worksheet Object,” should be selected.
At the left of this dialog box are two choices: Paste and Paste Link. Click on Paste Link.
Then click on OK. The selected portion of the spreadsheet appears in the Word document.
Continue this process wherever necessary for the report. Be aware that when choosing a Chart or Graph, the choice in Paste Special will read “Chart Object” instead of “Worksheet Object.”
Updating the Word/Excel Linked Data
When the last minute, modified numbers are received on Monday follow these steps.
Open the spreadsheet file.
Make the changes necessary to the raw data in the spreadsheet. The spreadsheet will automatically recalculate all other cells dependent upon the new raw data.
Save the spreadsheet with the same file name to the same storage device and same folder.
Retrieve the Word document. A notice will appear stating that the Word document is linked to other files and asks if it should use the new data from the other files.
Click Yes.
All linked objects in the Word document are updated automatically.
Caveat Concerning Word/Excel Links
The Word document and Excel spreadsheet link is static and absolute. If either file is renamed or moved to another storage device or even another folder on the same storage device, the link will be broken. To repair this problem read the article: “Repair a Broken Link Between Word and Excel.”
Paste Links Between Other Microsoft Applications
The linking capabilities described in this article are available in other Microsoft applications. The only difference is in the reference made in the Paste Special dialog box. Instead of “Worksheet Object” the choice may say “Paint Object” or “Picture Manager Object” instead.
With a little practice Paste Link becomes an easy and efficient method for keeping interdependent files updated. Overall it is an excellent method for assuring the ability to make last minute changes with ease.
