Paste Options
Paste | Values | Formulas | Formatting | Paste Special
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 colour 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.
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.
- Select cell B5, right click, and then click Copy (or press CTRL + c).
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.
Status Bar
The quickest way to see the average, count, numerical count, minimum, maximum or sum of selected cells is by taking a look at the status bar.
- Select a range of cells.
2. Look at the status bar at the bottom of your window to see the sum of these cells.
Result:
Quick Operations
Use the 'Paste Special Operations' to quickly perform operations on a range of cells in Excel.
1. Select cell D3.
2. Right click, and then click Copy.
3. Select the range A1:B8.
4. Right click, and then click Paste Special.
5. Click Multiply.
Note: you can also Divide, Add or Subtract a value.
6. Click OK.
Note: all values are increased by 10 percent. Without this feature, you would have to create a temporary range (with formulas that multiply the values in the range A1:B8 by 1.1) and then replace the range A1:B8 by copy and pasting the temporary range as values.
