Excel 2016 All-in-One For Dummies
Book image
Explore Book Buy On Amazon

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!

About This Article

This article is from the book:

About the book author:

Greg Harvey, PhD, is the president of Mind Over Media, LLC., and a bestselling author of books on Excel, including all editions of Excel For Dummies and Excel Workbook For Dummies. He began teaching business users about computers back in the 1980s, and has been a dedicated educator ever since.

This article can be found in the category: