Aung Shine
The Copy and Paste features are two of the most used features in MS Excel. Whenever we copy and paste any data, the formulas also get transferred along with the values. But, this may not be desired in some cases. Again, when we start typing any formula, Excel provides Function suggestions by default. We may also want to turn this off. In this article, we’ll show you the simple methods to remove the automatic formula in Excel.
How to Remove Automatic Formula in Excel: 5 Suitable Methods
To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset represents the Total marks obtained by using the SUM function.
1. Use Paste Values Feature to Remove Automatic Formula in Excel
In our first method, we’ll use the Paste Values feature in Excel. This feature returns the cell values only and ignores formattings, formulas, etc. attached to them. Therefore, follow the steps below to perform the task.
STEPS:
- First, select the range D5:D8 where we have applied the formulas.
- Then, press the ‘Ctrl’ and ‘C’ keys together to copy it.
- After that, right-click on the mouse and select Paste Values from the Paste Options.
- Lastly, it’ll return the values only.
Read More: How to Remove Formulas from Entire Excel Workbook
2. Delete Automatic Formula in Excel with Home Tab
We can apply the Paste Values feature from the Home tab also. So, learn the following steps to Remove the Automatic Formula in Excel.
STEPS:
- Firstly, copy the range D5:D8 where we have applied the formulas.
- Afterward, select Home ➤ Paste ➤ Paste values ➤ Values.
- Finally, you’ll only see the values without the formulas.
Read More: VBA to Remove Formulas in Excel Keeping Values and Formatting
3. Apply Keyboard Shortcuts to Get Rid of Automatic Formula
Moreover, we can apply keyboard shortcuts for deleting the automatic formula in Excel. Hence, follow the process to carry out the operation.
STEPS:
- First of all, select the range D5:D8 and copy it >> Press the ‘Alt’, ‘E’, and ‘S’ keys one after another.
- As a result, the Paste Special dialog box will pop out >> Select the Values and press OK.
- At last, it’ll return the values only by removing the formulas.
Read More: How to Remove Formula in Excel and Keep Values
4. Remove Automatic Formula Through Excel File Options
Now, if you want to turn off the automatic function suggestions in Excel, learn the below process.
STEPS:
- Go to File ➤ Options at first.
- Consequently, the Excel Options dialog box will appear.
- Next, from the Formulas tab, uncheck the box for Formula AutoComplete.
- In the end, press OK. Thus the automatic function suggestions are turned off.
5. Use Formulas Tab to Delete Automatic Formula in Excel
Again, Excel tends to calculate automatically in many cases. To turn on the manual calculating option, see the following steps.
STEPS:
- In the beginning, select the Formulas.
- Then, select Manual from the Calculation Options.
- Hence, it’ll turn off the automatic calculation.
Download Practice Workbook
Download the following workbook to practice by yourself.
Conclusion
Henceforth, you will be able to remove the automatic formula in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
