Copy-Paste is a very useful feature in Excel. Sometimes, you may face the problem that you need to copy the formulas along with the values or even only the formulas, but Excel does not support this. In that case, this article is going to be an important one for you. Today, I will describe along with the solutions why Excel not copying formulas but copying only values.
How to Fix When Excel Is Not Copying Formulas & Only Values: 4 Issues with Solutions
Here, I will describe 4 suitable solutions for why Excel not copying formulas but copying only values. In addition, for your better understanding, I’m going to use the following dataset. Which contains 3 columns. They are Customer Name, Sales, and Profit. Furthermore, in the Profit column, I used a simple formula. Now, I will copy that formula for the rest of the cells.
1. By Changing Manual Calculation to Automatic
Sometimes, you try the Fill Handle icon to copy the following cells, but you may see that all the values remain the same. Which means the formula is not responding to the rest of the cells.
Actually, this is happening because you may keep the Manual Calculations option in the Excel workbook. Furthermore, below, I have attached an image of it.
Now, let’s talk about the solution.
Solution:
- Firstly, copy the cell which has the target formula.
- Secondly, select the range where you want to paste the formula.
- Thirdly, from the Formulas tab >> go to Calculation Options.
- Lastly, tick mark on the Automatic option.
As a result, you will see that you have copied all the formulas, successfully.
Read More: How to Copy Formula in Excel Without Dragging
2. Reboot Device
Sometimes, you may follow the proper way to copy a formula still the formula may not work. In that case, try to restart your PC. After restarting, again open the Excel workbook and copy the formula. Now, the formula might work.
Read More: How to Copy Formula Down Without Incrementing in Excel
3. Use of Paste Special Option
Furthermore, if you want to Copy values along with the formula but you choose the Paste Values option from Paste Special then it will only copy values without formulas.
As a result, you will see the following output.
Solution:
To Copy only formulas, you may try the Paste Special feature in Excel. Follow the steps given below.
- Firstly, copy the cell from where you want to copy a formula.
- Secondly, select the range where you want to apply the formula.
- Thirdly, from the Home tab >> go to the Paste option.
- Lastly, choose the Paste Special option.
At this time, a new window named Paste Special will appear.
- After that, choose the Formulas option.
- Then, press OK.
Finally, you will paste only the formulas.
Read More: How to Copy Formula and Paste as Text in Excel
4. By Enabling Fill Handle Tools
The easiest way to copy formulas within the same column or same row is by dragging the Fill Handle icon. Sometimes, your Fill Handle icon may not work. Perhaps, the Fill Handle icon remains unchecked in the Excel options.
So, you should make sure that the Fill Handle icon is enabled or not.
- Firstly, to do so, go to the File ribbon.
At this time, a new window will appear.
- Secondly, go to the Options.
As a result, a new dialog box named Excel Options will appear.
- After that, from the Advanced option >> tick mark on Enable fill handle and cell drag-and-drop.
- Then, press OK.
So, by doing this now you can use the Fill Handle icon for copying formulas not only values in Excel.
How to Fix When Excel Is Not Copying Formulas from One Workbook to Another
Most of the time, in Excel, when you copy data from one workbook and paste it into another workbook, it is expected that data will be copied along with formulas. Unfortunately, this does not happen all the time.
To illustrate, when we copy-paste data between workbooks, if the source workbook is not open, formulas will not be copied with values to the destination workbook.
As an example, here I want to copy the formula of cell D12 from the workbook named Copying Formulas 2. So, I copied cell D12 and closed the workbook.
Here, when I paste it into another workbook named Copying Formulas 1 as you can see only the value is pasted. So, keep both workbooks open while copying formulas.
Another reason is, that if you use workbooks of different versions of Excel, then the formulas will not be copied only the values will be. Actually, you should use the same version of Excel workbooks for copying formulas from one to another.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 4 possible solutions for why Excel not copying formulas but copying only values. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Copy Formula to Another Sheet in Excel
- How to Copy and Paste Formulas from One Workbook to Another in Excel
- VBA to Copy Formula from Cell Above in Excel
- Excel VBA to Copy Formula with Relative Reference
<< Go Back to Copy Formula in Excel | Excel Formulas | Learn Excel
