Using Paste Special to Paste Formatting and Perform Actions

If one part of your worksheet contains the type of formatting you need to apply to another part, you can often save time by using the Paste Special command and choosing options in the Paste Special dialog box. For example, you can paste formats or column widths only instead of pasting all the data and formatting from the cells you've copied. The Paste Special command also enables you to transpose data and perform mathematical operations on the cells you're pasting to.

You can access most of the Paste Special options from either the Paste pop-up menu in the Edit groupof the Home tab or the Paste Options pop-up menu, but in most cases it's clearest to use the Paste Special dialog box.

To display the Paste Special dialog box (see Figure 4–14), use one of these commands:

• Ribbon. Choose Home Edit Paste Paste Special.
• Menu bar. Choose Edit Paste Special.
• Context menu. Ctrl+click or right-click the destination cell, and then click Paste Special.
• Keyboard. Press Cmd+Shift+V.

Figure 4–14. Use the Paste Special dialog box when you need to paste only some of the data, when you need to perform an operation on the data, or when you need to transpose its rows and columns.

You then choose the option button you want in the Paste area:

• All. Select this option button to paste all the data and all its formatting. Normally, you'll want to do this only if you're using the Skip blanks check box, the Transpose check box, or the Paste Link button—otherwise, it's easier to use the Paste command.
• Formulas. Select this option button to paste in all the formulas and constants without formatting.
• Values. Select this option button to paste in formula values instead of pasting in the formulas themselves. Excel removes the formatting from the values.
• Formats. Select this option button to paste in the formatting without the data. This option is great for making one worksheet's formatting similar to another's.
• Comments. Select this option button to paste in only comments. This option is handy when you're integrating different colleagues' takes on the same worksheet.
• Validation. Select this option button to paste in data-validation criteria.
• All using Source theme. Select this option button to paste in all the data using the theme from the workbook the data came from.
• All except borders. Select this option button to paste in all the data and formatting but to strip out the cell borders.
• Column widths. Select this option button to paste in only the column widths—no data and no other formatting. This option is useful when you need to lay one worksheet out like another existing worksheet but put all different data in it.
• Formulas and number formats. Select this option button to paste in formulas and number formatting but no other formatting.
• Values and number formats. Select this option button to paste in values (rather than formulas) and number formatting.
• Merge conditional formatting. Select this option button to copy all data and formatting and to merge in any conditional formatting. See the next section for details on conditional formatting.

If you need to perform a mathematical operation using the data you're pasting, go to the Operation area of the Paste Special dialog box and select the Add option button, the Subtract option button, the Multiply option button, or the Divide option button, as needed. For example, if you want to multiply the current values in the cells by the values you're pasting, select the Multiply option button. Otherwise, leave the None option button selected to paste the data without performing math with it.

In the bottom section of the Paste Special dialog box, you can select or clear the two check boxes as needed:

• Skip blanks. Select this check box to prevent Excel from pasting blank cells.
• Transpose. Select this check box to transpose columns to rows and rows to columns. This option is much quicker than retyping data that's laid out the wrong way.

When you've chosen the options you want, click the OK button. Excel closes the Paste Special dialog box and pastes the data or formatting you chose.