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 button
for 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
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment