How to use the Paste Options button in Excel

This step-by-step article describes the functionality of the Paste Options button in Microsoft Excel.

The Paste Options button becomes available when you paste data into cells in Excel. The button appears just below the pasted selection. When you click the Paste Options button, you are presented with a list of options that determine how the data will be pasted into the cell. These options are also available on the Edit menu when you click Paste Special.

Use Paste Options

The Paste Options button offers you the following options:

• Keep source formatting: Preserves all original formatting of the pasted selection. Preserves all formulas as well.
• Match destination formatting: Formats the pasted data to match the formatting already existing in the new location.
• Values Only: This option appears only when the pasted data contains formulas. By choosing it, you strip all formulas and paste only the resulting values.
• Values and Number Formatting: Formatting is preserved only for numeric values, including dates. In addition, only formula results (see "Values Only") are pasted. Text formatting is removed.
• Values and Source Formatting: Strips all formulas (see "Values Only") and preserves all formatting.
• Keep source column widths: Preserves the column widths as well as all formulas and formatting.
• Formatting only: Pastes only the formatting, leaving all values and formulas out of the pasted range.
• Link cells: Links the pasted cells to their original location. For example, if you copy A1 to B1, cell B1 will contain a link to the original location in the format "=A1". All formatting and formulas are removed.
• Use Text Import Wizard: This option becomes available when you paste plain text from another program, such as Notepad. The Text Import Wizard allows you to specify how to divide pasted text into columns by using delimiter characters or defined text lengths to create individual columns.

Note that the exact set of options available depends on the data that you paste.

Use Multiple Actions

In some situations, it may be necessary to use a combination of the preceding options. For example, you may need to copy only the formatting and preserve the column widths. To do this, use the Paste Special function on the Edit menu.

Turn Off Paste Options

To prevent the Paste Options button from appearing, follow these steps, as appropriate for the version of Excel that you are running.

Microsoft Excel 2002 and Microsoft Office Excel 2003

1. On the Tools menu, click Options.
2. Click the Edit tab.
3. Click to clear the Show Paste Options buttons check box.
4. Click OK.

Microsoft Office Excel 2007

1. Click the Microsoft Office Button, and then click Excel Options.
2. On the Advanced tab, click to clear the Show Paste Options buttons check box.
3. Click OK.

For more information about how to use the various types of buttons and smart tags, click the following article numbers to view the articles in the Microsoft Knowledge Base: