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, as Figure 31-1 shows. For example, if you copy a Microsoft PowerPoint 2010 slide, PowerPoint posts the slide to the Clipboard in more than a dozen formats. If you right-click a cell in your Excel worksheet and click the Paste Special command, the Paste Special dialog box lists only the formats that Excel can accept.

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 31-1 shows, the Paste Special dialog box includes two options, Paste and Paste Link. To link your source data, select Paste Link. Excel renders your source data in whatever format you select and also creates a link to the source. Figure 31-3 shows a block of text pasted from Word into Excel with the Paste Link button selected. Notice that Excel identifies this as an object (as it does for embedded text). But instead of the EMBED formula used with embedded text (refer to Figure 31-2), Excel creates an external-reference formula that is similar to the kind of formula created 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 31-3. When you link data, Excel creates an external-reference formula similar to a formula it would use to reference a cell on another worksheet.

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 30.)