How to Use Paste Special in Excel 2016

By Greg Harvey

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 paste options in the Paste Special dialog box give you control over how a cell selection on the

The paste options in the Paste Special dialog box give you control over how a cell selection on the Clipboard is pasted into your worksheet.

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.

The Paste Special Dialog Box Options
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.

Transposing a copy of the production schedule table so that dates now form the row headings and the

Transposing a copy of the production schedule table so that dates now form the row headings and the part numbers now form the column headings.

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!