This blog entry was originally published as an article on March 28, 2006..

You already know that you can use mini-spreadsheets in your Microsoft Word documents through the use of Word’s table feature. Further, in a previous article, you learned how to use an internal Word database to create form letters, labels and other types of mail merge items. While both of these are useful tools, Word’s table feature is not even close to a replacement for the raw power of Excel and Word’s internal address list leaves so much to be desired that I could write an entire series of articles on the topic.

Even with an office suite as tightly coupled as Microsoft Office, you may be surprised at how much integration you can accomplish. For example, instead of using Word’s restrictive table feature, how would you like to directly integrate all of the power of an Excel workbook into your Word document? Or, what about that list of names and addresses you have in Excel? Would you rather be able to use that than being forced to use Word’s internal address book?

In this How do I… blog entry, Scott Lowe shows you how to make full use of Excel right inside Word.

Excel workbook information in Word

If you’ve tried to use Word’s table feature to perform Excel-like work, you’ve probably had a frustrating experience. While Word’s table function is good for very minor tasks and can perform some calculations, it doesn’t even begin to touch what you can do in Excel. So, use Excel instead!

There are a number of different ways you can use Excel data in Word. Consider the Excel workbook shown in Figure A.

Figure A

There are many ways to get this information into Word.

For this example, assume that the data will change every so often and that you will need to have the worksheet recalculate as information is modified. Now, let’s assume that you want to include this information in some kind of report.

Copy & paste

You’ve used copy and paste before to move information from one place to another, and may have even used this method at some point to include Excel data in your Word documents.

Figure B

A copy and paste basically turns your Excel data into a Word table.

When you do a copy and paste from Excel into Word, as Figure B demonstrates, your Excel information is converted into a Word table with all of the limitations inherent in Word tables. However, this is sometimes a great approach if you have some data in Excel you want to present for a one-time report and the information will never change.

That’s all I’ll say about this method.

Paste special (Paste: Excel Worksheet Object)

The second option accomplishes a similar goal, but allows you to manipulate the contents of the newly pasted information using the program in which the information originated – in this case, Excel.

Figure C

“Paste Object” allows you to manipulate the pasted contents with the native program.

The main difference between the copy and paste method is that you can now use all of Excel’s capability to maintain the information and you don’t even need to open Excel. Just double-click the table to allow Word to load Excel’s functionality while you edit the information. When you’re done making changes using the Excel tools, click anywhere else in your Word document. Figure D shows you what happens when you double-click an Excel worksheet embedded in a Word document.

Figure D

Notice in this figure that the heading clearly states that you are in Word, but the toolbars are clearly those you would see in Excel.

In Figure D, you’ll notice that, although you’re in the Word application, everything from the toolbars to the + cursor to the row and column headings screams Excel. You can always tell you’re in “edit mode” when you can see these Excel features. From here, anything you can do in Excel, you can now do in this Word table.
So how do you accomplish this seemingly magical feat? First, open Excel and select the data range that you want to include in your Word document. Once selected, press the Copy button (or press Ctrl + V, or choose Edit | Copy). Now, back in Word, place your insertion point at the location at which you want to place the Excel information. From Words Edit menu, choose Paste Special, which opens the window shown below in Figure E.

Figure E

Paste Special lets you paste your Excel data as Excel data rather than as a Word table.

From the Paste Special window, choose the Paste radio button at the left and, from the “As” box, choose “Microsoft Office Excel Worksheet Object”. Next, click the OK button.

Once you copy information from an Excel worksheet into Word using this method, the information is no longer tied to the original Excel workbook. That means if you make changes to the information using Excel directly, those changes will not be reflected in your Word document. However, the third method to include Excel information in your Word document can assist with the problems that may be created by this limitation.

Paste special (Paste LINK: Excel Worksheet Object)

Suppose you’ve copied a portion of a larger Excel document into your Word table for various reports, but the Excel information is under constant modification. If you use just the Paste Special method above, you’ll spend a lot of time synchronizing the information in Word with you Excel data. Fortunately, Microsoft considered this predicament and provides a very simple solution.

The solution is almost identical to the Paste Special option I talked about in the previous section except for one key factor. In the Paste Special window, rather than choosing the Paste radio button at the left side of the screen, choose the “Paste link” option instead.

Figure F

Note that the “Paste Link” option is chosen this time.

The Paste Link option is very similar to the Paste option except that the information you place into your Word document maintains a relationship to the original Excel worksheet. That is, in a day, a week, a month, etc, if you make changes to the source Excel worksheet, the next time you open the Word document into which you pasted a link to that Excel workbook, you will be asked if you’d like to update the linked information in your Word document from original Excel files.

Figure G

When you open up a Word document into which you’ve pasted links to Excel workbooks, you’re asked if you want to automatically update the information in the Word document.

If, between the time you pasted the link into a Word document and the next time you open up said Word document, you’ve deleted one of the Excel source files, Word will tell you that it can’t find one of the files.

Figure H

Word can’t find one of the Excel source files.

If this happens to you, it’s not a huge problem. Word will still update the rest of the information in the document.

In Figure I, I’ve pasted the exact same table twice into a Word document. The upper table was pasted with just the “Paste” option while the lower table was pasted with the “Paste Link” option. Afterwards, I updated the Excel information. Note that the upper table did not change when compared with earlier examples in this article. However, the lower table shows updated information.

Figure I

Note the differences between the two tables.

Note, when you double-click a linked Excel object in Word to edit the contents, Word gives you a full-screen version of Excel to work with and actually loads your Excel file for you. This means that changes made in Word are also transferred back to your original Excel workbook.

Start fresh with Excel in Word

You don’t even need to start with an existing Excel worksheet to be able to use Excel’s features in Word. You can put a blank table in Word and create the data you need right in your Word document using all of Excel’s features.

To do this, go to Insert | Object to open the Object window from which you can insert a new Excel object. From the “Object type” window, choose “Microsoft Excel Worksheet” and click the OK button. Word will give you a mini Excel spreadsheet with which you can work.

Figure J

Note again that you’re in Word with full Excel toolbars and menus.

On this screen, also note that there is a tab called “Create from File”. In reality, you can use information from just about any Windows program in your Word document. You could use the “Create from File” option to select an Excel workbook and place it into your Word document, but this method can be a little harder to control. I recommend that you use the one of the methods previously described instead.

Resizing embedded Excel information

There is one fact you should know about before you keep going: No matter what you embed into your Word document, the entire Excel workbook is available to you in Word. So, if you copy a single row of Excel data and then “Paste Special” it into your Word document, Word will size the Excel window such that you see only the row you pasted. However, all of the other information is still available. In Figure K, I’ve pasted (using the Paste Special option) a header and a single row of data from Excel into Word.

Figure K

Note that only the header row and a single row of data are displayed here in Word.

Now, in Figure L, I’ve double-clicked this tidbit of information. Notice that all of the tabs along the bottom are still accessible from the original workbook.

Figure L

The entire Excel spreadsheet was pasted into your Word document.

What does this mean? In essence, even if you thought you were getting just a portion of your Excel workbook in Word, this result means that your entire Excel workbook was actually saved inside your Word document. This isn’t necessarily a bad thing!

Resizing an embedded Excel workbook

Why did I mention that an entire Excel workbook is really copied into Word? At some point, you’ll probably want to resize the Excel information you pasted into Word. How you do that depends on what you want to accomplish and your results might be unexpected if you didn’t know that all of your Excel data exists in your Word document.

In Figure K earlier, you saw a single row of data. Now, when you’re in “Word mode”, meaning that you’re editing your Word document and not the Excel contents, single-click (as opposed to double-click) your Excel table.

Figure M

Note the black boxes.

The eight black boxes around your selection are resizing handles. You’ve probably resized pictures before by holding onto one of the handles and dragging it to make the picture larger or smaller. When you’re in “Word mode” (i.e. you’ve only single-clicked the Excel information), Word treats this information as a picture. Watch what happens (Figure N) when I use the middle bottom resizing handle to adjust the size of Excel table.

Figure N

The information is stretched, just as a picture would be.

Now, double-click the embedded Excel object to go into “Excel mode” in Word.

Figure O

The resizing handles are hard to see in this screenshot, so I’ve highlighted them in green.

Now, in Figure P, I’ve dragged the bottom middle resizing handle down the screen a ways and then went back into “Word mode”.

Figure P

Note the difference between this figure and Figure N.

This time, note that the information, rather than being stretched, was expanded. Instead of a single row in the workbook, Word now fits as many rows of information as there is space in the box.

If this is unclear, give it a shot in your own!

Fonts and formatting

I’m not going to spend a lot of time on this topic, but for a good reason that I’ll explain in a minute.

If you want to change the fonts and formatting in an embedded Excel worksheet object, you have to make the changes from within the Excel object by double-clicking the object and using Excel’s formatting tools. Nothing will happen if you, for example, try to change the font in an embedded Excel sheet using Word’s formatting tools.

Now, as to why I won’t be spending a lot of time explaining how to make font and formatting changes. I wrote an entire series of articles on the topic of formatting Excel worksheets.

Better Formatting in Excel

More than the sum

On their own, Word and Excel are formidable programs with significant capability. Together, they offer you the best of both programs and become more than the sum of their parts. There are a lot of different ways you can integrate the two products and each has its pros and cons.