Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Copy and Paste in Excel without Formulas (7 Easy Tricks)

In excel, most of the time we do calculations using formulas. However, when we copy data from one location to another, formulas are copied as well. Besides, when you use Ctrl + C and Ctrl + V to execute the copy-paste operation all the formatting is copied. Again, you might want to send spreadsheets to other people, where you do not want to show formulas. This article will guide you on how to copy data from source to destination without formulas.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


7 Easy Tricks to Copy and Paste without Formulas in Excel

Let’s assume we have a dataset (B5:E10) containing sales data along with total sales. I have used excel functions to get the Total Sales. Now I will show you how to copy and paste the Total Sales data without formulas.

7 Easy Tricks to Copy and Paste without Formulas in Excel

1. Use Excel Home Tab to Copy and Paste without Formulas

You can copy and paste using the Home tab of Excel Ribbon.

Steps:

  • Select the data that you want to copy and then copy it by pressing Ctrl + C from the keyboard.

Use Excel Home Tab to Copy and Paste without Formulas

  • Next, choose the destination cell (Cell G5) where you want to paste values.

Use Excel Home Tab to Copy and Paste without Formulas

  • Then go to Home > Paste > Values (see screenshot).

Use Excel Home Tab to Copy and Paste without Formulas

  • As a result, the copied values are pasted to the destination without formulas.

Read More: VBA Paste Special to Copy Values and Formats in Excel (9 Examples)


2. Copy Data without Formulas Using ‘Paste Special’ Command

You can use the Paste Special command in excel to execute the copy-paste task. Using this operation will paste your data without formulas.

Steps:

  • First, copy the data and select the paste location.
  • Then put the cursor on the cell where you want to paste the data and right-click on it.

Copy Data without Formulas Using ‘Paste Special’ Command

  • Consequently, the Paste Special dialog appears. Now choose Values from the Paste section and click OK.

  • Finally, you will get all the values without formulas as expected.

Read More: [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)


3. Use Keyboard Shortcuts to Copy and Paste Values

If you are a keyboard lover, then you can use a combination of hotkeys to perform the copy-paste operation.

Steps:

  • Copy the source cells (using Ctrl + C) and put the cursor on the paste location.

Use Keyboard Shortcuts to Copy and Paste Values

  • Next, press either of the following combinations of hotkeys to paste the values in the destination location. Pressing these hotkeys will bring the Paste Special window.

Alt + E + S + V + Enter (one by one)

Or

Ctrl + Alt + V, V, Enter

Note:

  • You can bring the paste values by using the below hotkeys too. This shortcut follows the path Home > Paste > Values.

Alt + H + V + V

Read More: All Paste Options in Excel with Time Saving Shortcuts


4. Copy and Paste without Formulas Using Mouse Trick

Using the mouse trick is a very interesting way to copy and paste data without formulas. Let’s discover the process of doing it.

Steps:

  • First, select the range of data you want to copy and hover the mouse along the border of the range until a four-headed arrow appears.

Copy and Paste without Formulas Using Mouse Trick

  • Then using the right button of the mouse drag the data to the paste location. Now release the mouse right button and the below menu appears. Click on Copy Here as Values Only.

Copy and Paste without Formulas Using Mouse Trick

  • As a consequence, you will see values are pasted without formulas.

Read More: Macro to Copy Specific Columns from One Worksheet to Another in Excel


Similar Readings


5. Apply Quick Access Toolbar to Paste Values without Formulas

I will add the Paste Special command to the Quick Access Toolbar in excel to copy-paste values. The main advantage of this process is, it saves a lot of time.

Steps:

  • Firstly, go to the Customize Quick Access Toolbar icon and click on More Commands.

Apply Quick Access Toolbar to Paste Values without Formulas

  • Then, the Excel Options dialog appears. Choose Paste Special from Popular Commands, click on Add and press OK.

Apply Quick Access Toolbar to Paste Values without Formulas

  • As a result, the Paste Special command is added to the Quick Access Toolbar. Now copy the data, select the paste location and click on the newly added Paste Special icon.

  • Subsequently, the Paste Special dialog will show up. Click Values from the Paste section and press OK.
  • Upon clicking OK, you will get the data pasted without formulas.

Read More: Formula to Copy and Paste Values in Excel (5 Examples)


6. Copy and Paste in Multiple Sheets without Formulas

Sometimes multiple sheets contain similar types of data. In such a case, if you copy and paste the data in these sheets at once, it will save a lot of time. Let’s learn how to perform the task.

Steps:

  • First, select all the sheets where you want to perform the operation (using the Shift key). I have selected Sheet1, Sheet2, and Sheet3.

Copy and Paste in Multiple Sheets without Formulas

  • Now go to Sheet1, copy the data range and select the paste location.

Copy and Paste in Multiple Sheets without Formulas

  • Then paste the data using any of the copy-paste methods mentioned in this article. You will see that in all of the three sheets data are copied without formulas.

Read More: Excel VBA: Copy Range to Another Workbook


7. VBA to Copy and Paste in Excel without Formulas

Suppose, you have an excel sheet containing formulas and you want to copy the data from this sheet to another workbook. While copying data to the other book you need only the values, not the formulas. This seems quite tricky right?. But you can do this by using a VBA code.

Steps:

  • Firstly, go to the worksheet where you have the source data. Then right-click on the sheet name and click View Code to bring the VBA window.

VBA to Copy and Paste in Excel without Formulas

  • Now type the below code in the Module. Run the code using the F5 key or click the Run Sub/UserForm icon (see screenshot).
Sub PasteInAnnotherBook()

ActiveSheet.Copy
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

VBA to Copy and Paste in Excel without Formulas

  • Upon running the code, the dataset is copied to a new workbook.

Read More: How to Apply VBA PasteSpecial and Keep Source Formatting in Excel


Conclusion

In the above article, I have tried to discuss several methods to copy and paste without formulas in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

2 Comments
  1. Reply
    Alun Vaughan-Evans Nov 30, 2022 at 3:50 PM

    Hello,

    is there any way to keep the formatting and also the values?

    • Hello Alun Vaughan-Evans,
      Thanks for your comment. I am replying to you on behalf of ExcelDemy. There is a way to keep the formatting and also values. You can follow these steps to do that.
      Step-01: Select the range you want to copy and then copy the range by pressing Ctrl + C on your keyboard.
      Step-01
      Step-02: Select the cell where you want to paste the range. And then go to Home > Paste > Values & Source Formatting (like the following image).
      Step-02
      Finally, you will see that you have copied the values with formatting.
      Final Output
      I hope this will help you to solve your problem. Please let me know if you have other queries.
      Regards
      Mashhura
      ExcelDemy

Leave a reply

ExcelDemy
Logo