Using Excel Tables in Word Documents

Using Excel Tables in Word Documents

You can create tables directly in Word, of course, but if your tables consist of more than a few rows or columns, you'll probably find it simpler to build them in Excel and then transfer them to your Word documents. You can use either of the following methods to move a worksheet range from Excel into Word:

  • Copy the Excel data to the Clipboard, and then use Paste or Paste Special in Word to paste the table in the format of your choice, with or without a link to the source data.

  • On the Insert tab in Word, click Object. The Object command in Word works just like its counterpart in Excel. (See "Embedding and Linking with the Object Command" on page 886.)

Pasting an Excel Table from the Clipboard

If you copy an Excel worksheet range to the Clipboard and then paste that range into Word (using either the Paste command or its keyboard shortcut, Ctrl+V), the smart tag menu that appears below the lower-right corner of the pasted data, shown in Figure, provides quick access to the formatting options you're most likely to want. These options are as follows:

  • Keep Source Formatting Word receives the data as a block of HTML and creates a table, preserving the fonts, alignment properties, numeric formatting, text color, and shading of your original. In most but not all cases, this option (the default) is an adequate way to create a table in Word that matches the appearance of your Excel data. After you have performed the paste, if you click the pasted data, a Table Tools section appears on the Ribbon in Word, and you can use commands on the Design and Layout tabs to alter the appearance of the table in Word.

  • Match Destination Table Style The data becomes a table in Word, but Word formats it as if you had created the table directly in Word.

  • Paste As Picture Word turns the pasted table into a picture and makes picture-formatting functionality available. You might choose this format if you want to add some pizzazz to your numbers-for example, shadows, reflections, borders with rounded corners, and so on.

  • Keep Text Only If you select this option, Word does not create a table. Instead, it simply pastes each cell's contents in the current default font, separating cells with single tab characters. You might find this option useful if you're simply copying a single column from Excel. Where multiple columns are involved, the Keep Text Only choice sometimes produces a misaligned hash in Word.

Image from book
Figure: Immediately after you paste Excel cells into Word, a smart tag menu gives you access to the formatting options you're most likely to need.

In addition to these four options, the smart tag menu provides commands to link your Word table (in either Excel format or Word format) to its source in Excel. These choices, Keep Source Formatting And Link To Excel and Match Destination Table Style And Link To Excel, are equivalent to selecting Paste Special and selecting the Paste Link option. We discuss these options later in this chapter. (See "Paste-Linking an Excel Table into Word" on page 896.)


Don't close your Excel document before pasting. If you copy data from Excel to the Clipboard and then close the Excel document, your options for pasting into Word are considerably diminished. The default paste format changes from HTML to rich-text format (RTF), and the options to link your Word document to its source in Excel disappear.

Using Paste Special to Control the Format of Your Table

The smart tag menu that appears when you paste Excel data includes only the most commonly used formatting options. These will probably meet your needs in most cases. However, if you are pasting from an Excel document stored in Compatibility mode (that is, saved in the Excel 97–2003 Workbook format), you sometimes might find the HTML badly rendered in Word. (These problems appear to have been solved in the new Excel 2007 formats.)

If your Excel table does arrive with formatting distortions in Word, all is not lost. Erase the pasted data in Word, and then try again using the Paste Special command in Word. (On the Home tab in Word, click the small arrow below Paste, and then click Paste Special.) Figure shows this dialog box as it appears when your Excel document is still open. (If you close the Excel document, fewer options are available.)

Image from book
Figure: Word can paste an Excel range in any of these formats.

With almost any of the formats displayed in the Paste Special dialog box, you can either paste or paste-link. We'll look at the latter operation in a moment in "Paste-Linking an Excel Table into Word" on page 896. The next sections describe the available paste formats:

  • Microsoft Office Excel Worksheet Object

  • Formatted Text (RTF) and HTML Format

  • Unformatted Text and Unformatted Unicode Text

  • Picture (Windows Metafile), Bitmap, and Picture (Enhanced Metafile)

Using the Microsoft Office Excel Worksheet Object Format

Pasting the Worksheet Object format provides a completely faithful replication of the appearance of your Excel table-including any graphical elements that happened to be in your Excel selection. It also lets you edit the pasted table using Excel commands and features rather than Word ones. For example, if you want to apply a custom numeric format to your data after pasting it as an object into Word, you can do that by double-clicking the object. When you do this, the user interface of Word temporarily merges with Excel, as shown in Figure. After you edit the object and click any other part of the Word document, the Excel Ribbon is replaced by the Word Ribbon, and the worksheet column and row headings disappear.

Image from book
Figure: When you double-click an embedded Excel object, the Word Ribbon is replaced by the Excel Ribbon, letting you use Excel formatting and editing features to modify the object.

When you double-click an embedded Excel object that includes graphical elements, Word might truncate those elements temporarily. When you return to the regular Word interface, however, Word will fully restore your graphical elements.

An important peculiarity to note about Excel objects embedded in Word documents is that Excel embeds the entire workbook, not only the selection you copied to the Clipboard. The sheet tabs at the bottom of Figure illustrate this point. Although the editing window that appears in Word when you double-click the Excel object has the same dimensions as the original selection in Excel, you can scroll to any part of the current worksheet and even switch to another worksheet in the same workbook. When you return to Word (by clicking off the embedded object), Word treats any scrolling you do as an edit of the embedded object. If you switch from Sheet1 to Sheet2 while you're editing, you'll see Sheet2 when you return to your Word document.

Using Formatted Text (RTF) and HTML Format

The RTF and HTML formats preserve the font and numeric formatting of your Excel selection. They differ in the way they preserve that formatting. RTF is a method of encoding formatting information that has been available for a long time in Microsoft Office and other kinds of documents. HTML is the default format in Word (the format you get if you press Ctrl+V), a newer technology, and the programming language of the World Wide Web. Both formats generate tables in Word, maintaining the cell alignment you had in Excel and allowing for manipulation via the Table commands in Word. Neither format includes graphical elements that were part of your Excel selection.

HTML is more likely than RTF to render the formatting of your Excel selection accurately. But you might want to experiment to see which format suits your purposes more effectively. If you don't like the results you get, try again with a different format.

Using Unformatted Text and Unformatted Unicode Text

Use Unformatted Text and Unformatted Unicode Text when you do not want your Clipboard data to become a table in Word. Both formats transfer data from the Clipboard as though you had typed it directly into your Word document, using tab characters between the columns of your original Excel selection and return characters at the ends of lines. Ordinarily, the result is that data that was aligned neatly in Excel is no longer aligned in Word. Use the Unicode format if your data includes characters outside the regular ANSI range-for example, characters from non-Latin alphabets. Otherwise, it doesn't matter which of these two formats you use.

Using Picture (Windows Metafile), Bitmap, and Picture (Enhanced Metafile)

Picture (Windows Metafile), Bitmap, and Picture (Enhanced Metafile) formats produce more or less faithful graphical representations of your original Excel selection (including, of course, any graphical elements associated with it). Because the results are pictures, not tables, you can modify them with the picture-formatting features in Word.

Of the three available picture formats, Bitmap usually provides the most faithful replication of original appearances-at the cost of additional file size. For tables not accompanied by graphical elements, however, the Picture (Windows Metafile) format is usually more than adequate. Experiment to see what works best for you.

Paste-Linking an Excel Table into Word

You can paste-link any of the formats shown in Figure and described in the preceding sections by selecting Paste Link in the Paste Special dialog box. When you do this, Word creates a field that references the source of your Excel data. The field is a code (comparable to an external-reference formula in Excel) that tells the application how to update the data if you request a manual update. The code also tells the application how to locate the data for editing if you double-click the linked information in your Word document. You can see the code by clicking the Microsoft Office Button in Word, clicking Word Options, selecting the Advanced category, and then selecting the Show Field Codes Instead Of Their Values check box.

Links from Excel into Word are automatic by default, which means that anytime the Excel source is changed, the Word document is automatically adjusted. You can switch to manual linking by going to the Links dialog box, shown in Figure. To get there, right-click the Excel table in the Word document, click Linked Worksheet Object, and then click Links.

Image from book
Figure: In the Links dialog box in Word, you can switch between manual and automatic updating, modify the link specification, or break the connection.

If you're using manual updating, you can also force an update by clicking Update Now in the Links dialog box. A simpler way to update your table is to select it and press F9.

Inside Out-Link to Named Ranges
Image from book

It is extremely important to make sure Word identifies the source of an Excel link by means of a range name, not by an ordinary range reference. Otherwise, if the source table changes location for any reason (for example, if someone inserts or deletes a few rows), the link will no longer reference the original table. At best, you'll have a blank table in your Word document. At worst, you'll have the wrong table.

If the worksheet range has a name at the time you copy it to the Clipboard, Word will reference it by name when you perform your paste-link. If it is not named, Word will reference it by cell address using R1C1 notation. If you subsequently assign a name to the range in Excel and perform a manual update, Word will continue to reference it by address, not by name. If you have inadvertently linked to a range address instead of a range name, the simplest way to fix the problem is to remove the linked data, be sure the source range is named, and then re-create the link.

Image from book

Linking with Hyperlinks

An alternative way to create a link between a Word document and an Excel document is to use hyperlinks. With an Excel range on the Clipboard, you can click the Home tab in Word, click the arrow below the Paste command, and then click Paste As Hyperlink. As Figure shows, Word underlines every character in the pasted range, but the entire block becomes a single hyperlink; clicking any part of it takes you to the source data.

Image from book
Figure: Hyperlinks can connect a Word document to an Excel document, but they are not updated when the source changes.

The principal disadvantage of using hyperlinks instead of paste-linking is that hyperlinks don't get updated when the source changes. A hyperlink can make it easy for you or another user to find your way back to the data source, but it provides no assurance that your Word document is faithful to the source.

Using the Object Command

The Clipboard methods just described are fine for importing existing Excel tables into Word documents. If you're creating a table from scratch, you have the option of using an alternative method-by clicking the Object command on the Insert tab in Word. When you do this, the Object dialog box appears. Click the Create New tab, select Microsoft Office Excel Worksheet, and then click OK. Word displays a window into a blank Excel worksheet, as Figure shows.

Image from book
Figure: When you select Microsoft Office Excel Worksheet on the Create New tab in the Object dialog box in Word, a window into Excel appears in your Word document.

Here you can create your table, taking advantage of all the formatting and calculation tools in Excel. When your table is ready for inclusion in your Word document, click outside the Excel window. The result is an Excel object embedded in your Word file-exactly what you get if you create the table initially in Excel, copy it to the Clipboard, and then choose Microsoft Excel Worksheet Object from the Paste Special dialog box in Word.

The table that Word embeds will have the same row and column dimensions as the Excel window in which you created the table. That is, empty cells will be embedded along with populated ones. If you have to scroll to populate certain cells, some of your Excel table will not be embedded. In short, you need to adjust the size of the Excel window so it includes all the rows and columns you want to see in Word-and no more.

 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows