Paste Special

  • Post author:
  • Post category:Uncategorized

While working in Excel, we often copy values, formulas or even formats from other cells.  Merely copying can sometimes break the format. Thus, for each copied value, we have specific preferences like copying only the formulas, the values and copying with or without formatting.  

The Paste Special feature offers several ways to copy and paste values according to our preferences.  

Figure 1.  Paste Special dialog box

Paste All

The first option in Paste Special is the Paste All.  This has the same effect as when we copy a cell and directly paste it on another cell.  Let us follow these steps:

  • Select C14

Figure 2.  Copying the source cell

  • Press Ctrl + C or click the Copy command button under Home tab > Clipboard group

Figure 3.  Copy button in Home tab

  • Select the cell where we want to paste, F14
  • Right-click and select Paste Special

Figure 4.  Paste Special in menu options

  • In the Paste Special dialog box, select All under Paste and None under Operation. Click OK.  

Figure 5.  Paste All option

Note that all characteristics of C14 is copied in F14, including format and formula.  

Figure 6.  Output: Paste All

Paste Formula

When we want to copy only the formula from the source cell while keeping the format in the destination cell, we use the Paste Special Formula option.

  • In the Paste Special dialog box, instead of All, select Formulas under Paste.  

Figure 7.  Paste Formulas option

As a result, only the formula =SUM(F3:F12) is copied from C14 to F14.  The format remains the same, where the cell has no color fill and the value is in percentage format.  

Figure 8.  Output: Paste Formula

Paste Values

In the same way, when we want to copy only the values from the source cell while keeping the format in the destination cell, we use the Paste Values only option.  

  • In the Paste Special dialog box, select Values under Paste.  

Figure 9.  Paste Values option

As a result, only the value “151” is copied from C14.  Since the format in F14 remains the same, 151 is displayed as a percentage, “15100%.  

Figure 10.  Output: Paste Values

Paste Formats

In order to copy only the format of a cell, we use the Formats option in Paste Special.  There are three methods in doing this:

  • Copy the source cell, right click the destination cell and select Paste Special.  In the dialog box, select Formats.  

Figure 11.  Paste Formats option

  • Copy the source cell, right click the destination cell and look for the button.  This is the shortcut buttonfor Paste Formats

Figure 12.  Paste Formats command button

  • Select the source cell (C14) then click the Format Painter button in Home tab > Clipboard group.  

Figure 13.  Format Painter in Home tab

Then click the destination cell (F14).  

Figure 14.  Output: Paste Formats

All three methods paste only the format into the selected cell.  

Paste Special Multiply

There is a quick option to multiply a range of cells with a certain value. We follow these steps:

  • Copy the value in E2 by using the shortcut Ctrl + C
  • Select the range of cells we want to multiply, C3:C12

Figure 15.  Selecting the range of cells to multiply

  • Right click and select Paste Special.  
  • In the dialog box, select Multiply under Operation

Figure 16.  Paste Multiply option

The values in C3:C12 will be instantly multiplied by 1.05.  

Figure 17.  Output: Paste Special Multiply