Excel Plays Well With Others Through Linking and Embedding

In this article

Excel is one member of a family of applications called Microsoft Office.  The more common MS Office applications with their focus are;

• Access – tracking and managing databases
• OneNote – organizing, find and share notes and information
• PowerPoint – presentations, slide shows, meetings and web pages
• Publisher – publications and marketing materials
• Word – create and edit documents, letters, papers, reports and booklets.

Being familiar with one member of the MS Office family makes it easier to work within another member.  The MS Office applications tend to have the same look and feel.  An added bonus is the ability of sharing data among the family members.

Copying and Pasting

Most of us are familiar with copying something from a source and pasting the information somewhere else.  The standard copy and paste process is not what this post is about.  Copying and pasting creates a static copy.  This post will go beyond the static copy when we take an object and place that object into another application.

What if we could take a chart from Excel and place it inside a Word document?  Or take a Word document and place it inside an Excel worksheet?  Instead of a static copy, could we modify the object we just placed into the destination application?

What are “Objects”

You might have heard, from a techie, that MS Office applications are “object-oriented”.  What did they mean by object-oriented?  And why, can’t they speak in plain English so everyone understands them?

The “object” that the techie is referring to is everything you work with in a MS Office application.  In Excel, an object could be a cell, a table or a chart.  MS Access tables or queries could be objects.  In MS Word, an object could be a letter, report or mailing label.  If you can create or edit something, that is an object.

Object-oriented means you can take one object and put it inside another object.  In relation to MS Office applications, you can take an Excel chart and put it inside of a Word document.  What would happen if all you could work with was MS PowerPoint?  You can probably create a one-page letter.  Try creating a 20-page report only using PowerPoint.  How about creating professional quality brochures in MS Access?

The software developers at Microsoft knew that no one application will address all your needs.  By creating a family of applications based on objects, MS Office applications will allow you to take an object from one application and place it in another.

 

Linking and Embedding

The process that allows you to link and embed objects is called Object Linking and Embedding or OLE.  An application that supports OLE will work with any other OLE compatible application.  As we have seen all the applications in the MS Office suite use OLE.  Plus there are other applications like the Adobe Collection suite and many other applications.

An example of linking would be placing an Excel chart into a Word report.  Excel and Word are now linked.  Any changes in the Excel cells that the chart is based on will be reflected in the chart within the Word report.  At this point, the Word report is referencing Excel.  When you save the Word report, the Word file stores the Excel workbook’s file name and file location.

Embedding works differently.  Using the same example above about the Excel chart within a Word report, now we will embed the chart.  The embedded chart is a copy of the Excel chart.  It is not attached to the original chart in Excel.  Any changes you make in the Excel workbook will not be reflected in the Word report.  Another point to consider is Word no longer references the Excel workbook data. The embedded chart along with all of the Excel workbook data is now contained in Word.  The Word file now is much larger.

Related: The Many Ways of Viewing an Excel Worksheet

Excel and Word Working Together

Enough with all this techie stuff, time for a practical example.  The example will include an Excel worksheet that will be linked to a Word document.  Remember, in this example, that linking causes Word to reference Excel.

Steps to Link an Excel Worksheet to a Word Document

1. Open the Word document
2. Move to the place, in the document, you will place in the Excel worksheet
   • Click just right of the selected place
   • Press the [ Enter ] key to move down to the next line
   • Click;  Insert –> Text –> Object –> Object
   • In the Object dialog, click the “Create from File” tab
   • Click the “Link to file” check box.
   • Note the “Result” message…it means any changes to the Excel worksheet will immediately appear in the linked Excel object.
   • Click the “Browse…” button
   • Go back to the Excel workbook
   • Click the “Insert” button and then click OK

The Excel worksheet is now displayed in the Word document.  To test the effects of linking, double-click one of the values in the linked Excel worksheet.  Notice that double-clicking causes the Excel application to open with the linked Excel worksheet.  At this point, you would have both Word and Excel open at the same time.

Go to the Excel application and make a change in one the cells in the worksheet.  Now click once inside the Word document to make it active.  Right-click the Excel linked object and click “Update Link” in the shortcut menu.  The change in the Word document will now match the change in the worksheet.

Limitations to Linking

• Links don’t automatically update in certain situations.
  • When you change the name of the linked file
  • When you change the name of the folder a linked file is stored in
  • When you move the linked file to a different folder
  • E-mail the Word document (host application) but not the linked document

All the above examples will break the link.

There is a sure fire way of automatically updating a linked object.  Using the above example, start by opening the Excel worksheet and make whatever changes to the values.  Next open the Word document.  You will see that the changes you had made in Excel are automatically reflected in the Excel object in Word.

Related: Excel Functions Standing upon Bones

Embedding an Excel worksheet in to a Word Document

In this example we will look at embedding.  Instead of referencing, embedding will, what I like to call “anchors”, the object into another application.

Steps to Embed

Open the Word document
Move to the place, in the document where you will place the Excel worksheet
Click just right of the selected place
Press the [ Enter ] key to move down to the next line
Click;  Insert –> Text –> Object –> Object
In the Object dialog, select “Microsoft Excel Worksheet” from the Object type list
Click the OK button

An Excel worksheet is now embedded in the Word document.  Notice that the Word Ribbon has been replaced by an Excel Ribbon.  At this point you can enter values into the embedded Excel worksheet.  To return to the Word document, you would just click anywhere on the document.  The Word Ribbon would reappear.

With embedding you really have the Excel worksheet in the Word document.  If you have a separate copy of the Excel worksheet and make changes, those changes will not be updated in Word.  There is not an “update” process with embedding.  Everything you do will be contained in the Word document.

Which is Best?

• Embedding involves one application, instead of having two applications open at the same time, as in linking.
• Embedding will create a larger file.
• Linking is best if the source object changes frequently and you need to always have the latest data.
• Linking can be hard to manage.  File names and file locations must be kept stable, otherwise the link will be broken.

To summarize, depending upon only one application may not be feasible.  There could be times in which you need an object from one application that must be placed into another application.  OLB compatible applications will allow you to address that problem.

Photo: t r e v y

Note By BiggerPockets: These are opinions written by the author and do not necessarily represent the opinions of BiggerPockets.