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.

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.

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

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

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

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

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

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

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

• 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

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

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

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

Read More: Copy and Paste Formulas Without Changing Cell References

5. Applying 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, that it saves a lot of time.

Steps:

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

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

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

6. Doing Copy and Paste in Multiple Excel 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.

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

• 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: Copy and Paste Without Changing the Format in Excel

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

• 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

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

Download Practice Workbook

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

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

<< Go Back to Copy Paste in Excel | Learn Excel