While working in Excel, we often copy values or formulas from other worksheets, from other programs or even from the web. For every copied value, we have specific preferences like copying without formatting, copying only the values or only the formulas. It becomes troublesome if the copied values distort the current format in our worksheets.
The Paste Special feature offers a variety of ways to copy and paste values according to our needs.
Figure 1. Final result: Copy without formatting
How to copy and paste without changing the format
In order to copy values or formula without changing the format, we launch the Paste Special tool in Excel.
To copy: Press Ctrl + C to copy cells with values, text or formula
To paste: Click Home tab > Paste > Paste Special
Figure 2. Paste Special feature
The Paste Special dialog box offers customized ways to paste the copied data. With this tool, we are able to copy only the values, formulas, format or any combination with number formats.
Figure 3. Excel Paste Special dialog box
Copy values without formatting
When we simply employ the Ctrl + C and Ctrl + V shortcuts to copy and paste values, we might end up with a table with varying formats such as this:
Figure 4. Copy values including formatting
In order to copy selected values while keeping the destination formatting, we follow these steps:
- Copy the selected value
Figure 5. Copying the source cell
- Select the cell where we want to paste the value
Figure 6. Selecting the destination cell
- Click Home tab > Paste > Paste Special > Paste Values button
Figure 7. Paste Values button in Paste options
The name “Ann Taylor” will be copied without formatting, all the while keeping the destination cell formatting.
Figure 8. Output: Copy values without formatting
Copy formula without formatting
When we copy a formula in one cell and paste it on another cell, we are at risk of also copying the format of the source cell. In below example, we want to copy the formula for age in cell D3.
Formula: =NOW()-C3/365.
Figure 9. Example: copy exact formula for age
When we simply paste the formula in cell D4 through the shortcut Ctrl + V or the menu options, we also copy the red text color format into cell D4.
Figure 10. Pasting the copied formula with format into destination cell
We want to copy the formula exactly as it is in the cell, without the formatting.
Work-around:
- Copy cell D3
- Select cell D4
- Click Home tab > Paste > Paste Special
- In the Paste Special dialog box, tick the Formulas radio button
Only the formula is copied, keeping the blue text color format of cell D4.
Figure 11. Output: Copy exact formula
Copy values not formula
This time let us learn how to copy numbers without the formula. Suppose we want to copy the age from a separate database in another worksheet.
- Copy cell D3
Figure 12. Copying the age from source cell
When we paste the copied cell into the destination cell, we have copied the formula as well.
Figure 13. Pasting the copied cell with formula
Work-around:
When we want to copy only the value and not the formula, we follow these steps.
- Select the source cell and press Ctrl + C
- Select the destination cell
- Click Home tab > Paste > Paste Special
- In the Paste Special dialog box, tick the Values radio button
Finally, we are able to copy the value and not the formula, as shown in cell E9.
Figure 14. Output: Copy values not formulas
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