Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles copy and paste Copy without formatting in Excel

Copy without formatting in Excel

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc