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
tab >*Home*>*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
tab >*Home*>*Paste**Paste Special* - In the Paste Special dialog box, tick the
radio button*Formulas*

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
tab >*Home*>*Paste**Paste Special* - In the Paste Special dialog box, tick the
radio button*Values*

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