Linking Excel Files Into A Word Document On Mac

This article was created for Second Son Consulting - easily the best mixed environment I.T. consulting company in Los Angeles. You can find the original article in the blog section of their website.


Mac users of Microsoft Office are often left out in the cold - some of the best features of the Office suite don’t work well (or at all) and documentation can be very hard to come by. So, when I figured out how to…

Embed Microsoft Excel Files Into Word Documents Using Office 2011 On The Mac


I figured that I better share with the world. If you have any suggestions for improving the workflow, or better references for me to check out please send me an email.
Of course, this is Office on the Mac so nothing is easy and none of it works as well as you want it to - still, this is a functionality that can’t be replicated in Pages/Numbers. Mainly because you can’t get multiple cross-linked spreadsheets to interact (unless you know better!). Oh, did I forget to mention that the Excel files can refer to each other? Sorry, that’s an important detail that I should have mentioned earlier :-)

This article was created for Second Son Consulting - easily the best mixed environment I.T. consulting company in Los Angeles. You can find the original article in the blog section of their website.


Mac users of Microsoft Office are often left out in the cold - some of the best features of the Office suite don’t work well (or at all) and documentation can be very hard to come by. So, when I figured out how to…

Embed Microsoft Excel Files Into Word Documents Using Office 2011 On The Mac


I figured that I better share with the world. If you have any suggestions for improving the workflow, or better references for me to check out please send me an email.
Of course, this is Office on the Mac so nothing is easy and none of it works as well as you want it to - still, this is a functionality that can’t be replicated in Pages/Numbers. Mainly because you can’t get multiple cross-linked spreadsheets to interact (unless you know better!). Oh, did I forget to mention that the Excel files can refer to each other? Sorry, that’s an important detail that I should have mentioned earlier :-)

This is a step-by-step on how to embed data from Excel Files into Word documents in a way that lets that the Excel files be updated independently of the Word document. (That is, the Excel files are “linked" into the Word doc.) Care must be used when using this technique to ensure that the values in the Excel sections of the Word document are current before distributing it

Prepare Your File Structure

You will be linking files together and Office relies on absolute paths to files. So, it is important that you have your files in a FINAL location that will not change while you are working. It's possible to relink the files if you move them but it is easiest if all files you will work on reside in a single folder that you do no intend to move later. (Note - this is mostly so you can relink the files later if the links get broken). I did test the links after moving the entire folder to a new location and the links connected correctly.

Excel Files Can Link to Each Other

The Excel files that you want to link/place into the Word document should be ready before you begin. Any linking between Excel files can already be established (though it is not necessary). You can see from the formula bar that the cells in this table reference cells in a different Workbook.

Formatting of Excel Tables Can Be Controlled By Excel

The formatting of the cells that you place into the Word document is taken from the Excel formatting. You can change it later but it will help you to visually lay out the Word document if the final formatting is applied before you begin. Formatting controls are easier to understand in Excel than in Word.

It is easier insert the Excel data into existing text areas

Data from Excel is copied and pasted into the Word document. It is easiest to have text already in place and then insert the cells into it. Formatting, centering, etc. can be done immediately after you instert

Select Cells In Excel, Copy then Paste into Word

Simply select the range of cells you want to put into the Word document and then use command-C to copy. (In the screen shot I have used the context menu - note the dashed lines around the table I am copying that indicate it is selected)

Paste into desired position in Word document - NOTE: immediate follow up action required!

Set the cursor at the position where you want to insert the Excel cells and use the paste command. Do not do anything else until you read the next step. It requires you to interact with the clipboard menu - which disappears and is very difficult to get back again.

Set Formatting and Linking As Shown Below

In order to make the data update when the original Excel sheet is updated you need to LINK the cells to Excel. The issue of formatting is not as important but it is much easier to get the formatting correct in Excel than in Work.

NOTE ABOUT FORMATTING/LAYOUT

To change the position of the cells on the page it is easiest to use the formatting bar in the ribbon - click anywhere inside the cells, then click on the Tables ribbon. Use the Properties button to make changes (see below)

Setting Table Layout Properties

Most common will probably be Centered with No Text Wrap but that is a style decision...

Repeat As Necessary

Follow the same steps to insert all necessary sections into your Work Document

Changing Your Data In Excel

In my testing excel seems to require that all the linked spreadsheets be open in order for the data to update correctly. I'm sure there is a way to link them more robustly but have not found it yet. So, open all the spreadsheets that are cross-linked in the document and then update them as necessary. Note: do not attempt to OPEN all the files independently at the same time. If you need to work on the Word doc and the Excel docs simultaneously then open Word first and us it to open the Excel files as shown below.

Close All Excel Docs, Then Open The Word Document To Update The Figures

Failing to close the documents can result in unexpected changes to your data!

When the Word Document Opens Update The Links

Choose Yes to get the new information from Excel! Note that Word often loses the formatting of the tables when you update it this way - make sure the double check the format/layout of each table when you update.

Updating Excel Directly From Word

Note that you CAN NOT simply type new numbers into the tables of the Word document and expect them to be udpated in Excel. You CAN type new numbers but they become "dead".

To update Excel from within the Work document right click on the table and choose: Linked ... > Open Link (as shown above). The Excel file will open in Excel and you can edit directly. Changes that you make will show up immediately in Word FOR THE CELLS YOU ARE EDITING ONLY! If there is cross linking between spreadsheets then the linked data will not be automatically updated.

Save and close the Excel sheet when you are done editing.

To Make Sure That All Links Are Correct - Open Each As Shown In The Step Above

This is the only way that I have reliable ensured that all the current data in the Excel files is mirrored in the work file. Open the Work file 1st, then Open all the Excel files from within Word. This ensures that all data is current and correct.

To Freeze The Data And Break Links To Excel

Convert the Word document to a fixed version by breaking all the links. First select Edit > Links... from the Edit menu and then choose the Break Link button after selecting all linked Excel sheets. This will break the link to the Excel docs so that new changes will never get included in the Work document - but the numbers can still be edited manually. As far as I have been able to determine the only way to relink is to delete the existing cells and paste new ones in from Excel - a repeat of the origianl process.