Paste options in Excel refer to a set of functionalities that dictate how copied data is inserted into a new location within a spreadsheet. This capability is especially significant for users dealing with large volumes of data, where maintaining the integrity of the original information is critical.

This example illustrates the various options of pasting in Excel. Cell B5 below contains the SUM function which calculates the sum of the range B2:B4. Furthermore, we changed the background color of this cell to yellow and added borders.

Paste

The Paste option pastes everything.

1. Select cell B5, right click, and then click Copy (or press CTRL + c).

2. Next, select cell F5, right click, and then click Paste under ‘Paste Options:’ (or press CTRL + v).

Result.

Values

The Values option pastes the result of the formula.

1. Select cell B5, right click, and then click Copy (or press CTRL + c).

2. Next, select cell D5, right click, and then click Values under ‘Paste Options:’

Result.

Note: to quickly replace the formula in cell B5 with its own result, select cell B5, press F2 (to edit the formula) and press F9.

Formulas

The Formulas option only pastes the formula.

1. Select cell B5, right click, and then click Copy (or press CTRL + c).

2. Next, select cell F5, right click, and then click Formulas under ‘Paste Options:’

Result.

Formatting

The Formatting option only pastes the formatting.

1. Select cell B5, right click, and then click Copy (or press CTRL + c).

2. Next, select cell D5, right click, and then click Formatting under ‘Paste Options:’

Result.

Note: the Format Painter copy/pastes formatting even quicker.

Paste Special

The Paste Special dialog box offers many more paste options. To launch the Paste Special dialog box, execute the following steps.

1. Select cell B5, right click, and then click Copy (or press CTRL + c).

2. Next, select cell D5, right click, and then click Paste Special.

The Paste Special dialog box appears.

Note: here you can also find the paste options described above. You can also paste comments only, validation criteria only, use the source theme, all except borders, column widths, formulas and number formats, values and number formats. You can also use the Paste Special dialog box to perform quick operations, skip blanks and transpose data.