You can use the options on the Excel 2016's Paste button's drop-down menu or use the options in the Paste Special dialog box (by choosing Paste Special from this drop-down menu or pressing Alt+HVS) to control what information is pasted into the paste range.
Normally, when you paste worksheet data from the Clipboard, Excel 2016 pastes all the information (entries, formatting, and comments) from the cell selection into the designated paste area, thus replacing any existing entries in the cells that are overlaid.
If you open the Paste Special dialog box, you also have access to options that perform simple mathematical computations (Add, Subtract, Multiply, and Divide) between the number of cell entries that overlay each other. (See the table.)
The options in the Paste Special dialog box are divided into two areas: Paste and Operation. The Paste option buttons (some of which duplicate the options on the drop-down menu on the Ribbon and the Paste Option's palette in the worksheet) enable you to specify which components of the copied cell selection you want copied; see the table for a list of options.
The Operation option buttons in the Paste Special dialog box enable you to specify which mathematical operation, if any, should be performed between the overlaying values in copy and paste ranges. Select the Skip Blanks check box when you don't want Excel to replace existing entries in the paste range with overlaying blank cells in the copy range.
| Option | What It Does |
|---|---|
| All | Pastes all types of entries (numbers, formulas, and text), their formats, and comments from the selection in the paste area |
| Formulas | Pastes only the entries (numbers, formulas, and text) from the selection in the paste area |
| Values | Pastes only numbers and text from the selection in the paste area, converting all formulas to their current calculated values so they're pasted into the worksheet as numbers |
| Formats | Pastes only the formats from the selection into the paste area |
| Comments | Pastes only the comments from the selection into the paste area |
| Validation | Pastes only the Data Validation settings from the selection into the paste area |
| All Using Source Theme | Pastes all types of entries (numbers, formulas, and text), their formats, and comments from the selection in the paste area and uses the colors, fonts, and graphic effects in the theme assigned to their source worksheet |
| All Except Borders | Pastes everything but the borders assigned to the cell selection into the paste area |
| Column Widths | Pastes everything into the paste area and adjusts the column widths in this area to match those of the original cell selection |
| Formulas and Number Formats | Pastes only the formulas and number formatting (omitting all text and numeric entries) from the cell selection into the paste area |
| Values and Number Formats | Pastes only the numbers and number formatting (omitting all text and converting all formulas to their calculated values) from the cell selection into the paste area |
| All Merging Conditional Formats | Pastes only the numbers and number formatting that meets the conditions specified by conditional formatting in the cell selection |
| None | Performs no mathematical operation between the values in the cell selection placed on the Clipboard and those in the destination range in the worksheet (the default) |
| Add | Adds the values in the cell selection placed on the Clipboard to those in the destination range in the worksheet |
| Subtract | Subtracts the values in the cell selection placed on the Clipboard from those in the destination range in the worksheet |
| Multiply | Multiplies the values in the cell selection placed on the Clipboard with those in the destination range in the worksheet |
| Divide | Divides the values in the cell selection placed on the Clipboard by those in the destination range in the worksheet |
| Skip Blanks | Does not replace existing entries in the worksheet with any overlaying blank cells placed on the Clipboard as part of the cut or copied cell selection |
| Transpose | Switches the orientation of the entries in the cell selection placed on the Clipboard so that data that originally ran across the rows now runs down the columns in the new area of the worksheet and the data that ran down columns now runs across rows |
| Paste Link | Pastes links to the original cell selection placed on the Clipboard |
The Transpose option, which appears on the Paste button's drop-down menu and the Paste Options button (also duplicated by the Transpose check box in the Paste Special dialog box), is particularly helpful when you have a row of column headings that you want to convert into a column of row headings or when you have a column of row headings that you want to convert into a row of column headings. You can also use this option to pivot an entire table of data so that the data that runs across the rows now runs down the columns, and vice versa.
The following figure illustrates just such a situation. Here, the production schedule table (including the column headings) is selected in the cell range A2:J6, the Copy button on the Home tab of the Ribbon is clicked, and the cell cursor is moved to cell A8. After that, choose the Transpose option from the Paste button's drop-down menu. Excel's Live Preview feature then shows how this transposition would appear in the cell range A8:E17.
In the transposed table, the original row headings are now the column headings just as the original column headings are now the row headings. Note, too, that in transposing the table, Excel retained the formulas that total the units produced each month, although now they appear in the last column of the table instead of the last row.
To convert a cell range that contains formulas to its calculated values (as though you had input them as numbers), select the cell range, click the Copy button on the Home tab, and then choose the Paste Values option from the Paste button's drop-down menu without moving the cell cursor. This causes Excel to paste the calculated values on top of the formulas that created them, thus zapping the overlaid formulas and leaving you with only the computed values!
