Embedding and Linking from the Clipboard

When you copy data to the Clipboard, the source application typically posts the data to the Clipboard in a variety of formats. When you use the Paste Special command in Excel (or another application, if you're not pasting into Excel), the Paste Special dialog box lists all the available formats that the receiving application knows how to use.

For example, suppose you copy a Microsoft Office PowerPoint 2007 slide. PowerPoint posts the slide to the Clipboard in more than a dozen formats. (If you're using Windows XP, you can see the available formats by opening the Clipbook Viewer application, Clipbrd. exe.) If you right-click a cell on your Excel worksheet and click the Paste Special command, the Paste Special dialog box lists the formats that Excel can accept. In this case, as Figure 30-2 shows, Excel offers a choice of six formats.

Figure 30-2: Excel can paste a PowerPoint slide in six of the many formats that PowerPoint posts to the Clipboard.

In many cases, the format that appears first in the Paste Special dialog box is the default format-the one that you would get by clicking Paste or pressing Ctrl+V. If the Clipboard data can be embedded, Excel is likely to make the embedding format the default. If you need to be certain, however, you shouldn't depend on these generalizations; use Paste Special instead of Paste.

If the format listed in the Paste Special dialog box includes the word Object, choosing that format embeds the Clipboard data. All the other formats produce a static paste. If you're not sure what a format is or does, select it in the Paste Special dialog box, and read the descriptive text below the format list.

As Figure 30-2 shows, the Paste Special dialog box includes two options, Paste and Paste Link. To link your source data, select Paste Link. Excel will render your source data in whatever format you have selected and also create a link to the source.

Inside Out-Don't Bother Pasting a Bitmap Object

If you copy a bitmap to the Clipboard, click the Paste Special command in Excel, and then select the Bitmap Image Object format, Excel pastes a picture instead of embedding a bitmap object. (This bug has been around for several versions of Excel.) To embed a bitmap, click Object in the Text group of the Insert tab.

Figure 30-3 shows a block of text paste-linked from Word into Excel. Notice that Excel identifies this as an object (as it does for embedded text). But instead of the EMBED formula you saw with embedded text (refer to Figure 30-1), Excel creates an external-reference formula, similar to the kind of formula it creates if you reference a cell in an external Excel workbook. The linked object also gets a green rotation handle in addition to the usual white handles. You can use the green handle to slant or invert the text block.

Figure 30-3: When you link data, Excel creates an external-reference formula similar to the formula it would use to reference a cell on another worksheet.

Note 

The Paste Link option in the Paste Special dialog box is not available for all the formats that Excel can paste. If it's not available for the data and format you want, try linking by means of the Object command on the Insert tab. For more information, see "Embedding and Linking with the Object Command" on the next page.

As mentioned, if you embed or link a format that Excel cannot render, the incoming data is represented by an icon. In some cases, you can also ask for iconic representation of data that Excel can render. Displaying an icon instead of the rendered data is an excellent choice if you want the user of your Excel document to have access to external information but not be distracted by it. The icon takes up little space on your worksheet, and you can add text beside it to explain its purpose. (An even better choice is a hyperlink; see Chapter 29, "Using Hyperlinks.")