Copy without formatting in Excel

  • Post author:
  • Post category:Uncategorized

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 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.  


  • 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


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