Types of Paste Special in Excel

You may not always want to copy everything from the source range to the destination range.

For example, you may want to copy only the formula results rather than the formulas themselves. Or you may want to copy the number formats from one range to another without overwriting any existing data or formulas.

To control what is copied into the destination range, choose Home-> Clipboard-> Paste and use the dropdown menu shown in Figure below. Options are:

In addition, the Paste Special dialog box enables you to perform other operations, described in the following sections.

Performing mathematical operations without formulas

The option buttons in the Operation section of the Paste Special dialog box let you perform an arithmetic operations. For example, you can copy a range to another range and select the Multiply operations. Excel multiplies the corresponding values in the source range and the destination range and replaces the destination range with the new values. In figure below we have a table containing daily work sheet of a worker. In total earning column should display Total Earning [Hours Worked * Rate/H]. We can do this by multiply operations of paste special.

1. First copy the range B3:B8 and paste on cell D3 [ you can simply use ctrl+c to copy and ctrl+v to paste]

2. Copy the cell range C3:C8

3. Right click on D3 to invoke paste special dialog box. Click on Multiply option and click OK [ref. to figure below].

Skipping Blanks when pasting

The Skip Blanks option in the Paste Special Dialog Box prevents Excel from overwriting cell contents in your paste area with blank cells from the copied range. This option is useful if you’re copying a range to another area but don’t want the blank cells in the copied range to overwrite existing data.

In the following example we have two different data sets containing some blank cells. Our objective is to merge those data sets not affecting the non-blanks cells.

• Copy the range F1:I8

Transposing a range

The Transposing option in the Paste special dialog box changes the orientation of the copied range. Rows become columns, and columns becomes rows. Any formulas in the copied range are adjusted so that they work properly when transposed. Note that you can use this check box with the other options in the Paste Special dialog box. Figure below shows an example of a horizontal range (A:E1) that was transposed to a vertical range (A3:A7).

Paste Link

Click on the Paste Link button in the Paste Special Dialog box  means you create formulas that link to the source range. As a result, the destination range automatically reflects changes in the source range.

Copy the cell(s) you want to paste. Here it is I3.

• Go to Cell I7 and press CTRL+ALT+V for the Paste Special.
• Click Paste Link button from Paste Special dialog box. (See figure below).
• If you make any changes in cell I3 the same will reflected on cell I7.
• Type Rs 3500/- in Cell I3 the same amount will reflected in Cell I7.

Copying Formats by Format Painter

Perhaps the quickest way to copy the formulas from one cell to another cell or range is to use the Format Painter button (the button with the paintbrush image)