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.

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.

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

Read More: How to Copy and Paste Formulas Without Changing Cell References in Excel

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.

• 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!]: Microsoft Excel Cannot Paste the Data as Picture

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.

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

• 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: How to Copy a Cell in Excel Using Formula(7 Methods)

Similar Readings

• VBA Code to Compare Two Excel Sheets and Copy Differences
• Excel VBA: Copy Cell Value and Paste to Another Cell
• How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)
• Excel VBA to Copy Data from Another Workbook without Opening
• How to Copy Rows Automatically in Excel to Another Sheet (4 Methods)

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.

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

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.

• 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: Excel Formula to Copy Cell Value from Another Sheet (4 Examples)

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.

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

Read More: Excel Formula to Copy Cell Value to Another Cell

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

• How to Copy and Paste in Excel Using VBA (7 Methods)
• Macro to Copy and Paste from One Worksheet to Another (15 Methods)
• How to Use VBA PasteSpecial for Formulas and Formats in Excel (3 Ways)
• Excel VBA to Copy Rows to Another Worksheet Based on Criteria
• How to Use VBA to Paste Values Only with No Formatting in Excel
• Copy Rows from One Sheet to Another Based on Criteria in Excel