By default when you copy (or cut) and paste in Excel, everything in the source cell or range - data, formatting, formulas, validation, comments - is pasted to the destination cell(s). This is what happens when you press CTRL+V to paste. Since that might not be what you want, you have many other paste options, depending on what you copy.
This example illustrates the various paste options 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.