Mrinmoy Roy
By default, the calculation mode in Excel is set to Automatic. Thus, when you enter a formula into a range, Excel automatically calculates the value of the formula. But if you don’t want Excel to do this, you can switch this option off. Here, in this article, you will learn to stop Excel convert a formula to a value automatically.
Stop Formula to Convert into Value Automatically in Excel: 2 Methods
I will use the following dataset to show you stop Excel converting a formula to a value automatically. The dataset has a total of 5 columns. The first column contains the student names. The next 3 columns contain obtained marks in 3 courses. The 5th column, Total Marks uses a formula to calculate the total marks each student obtains in the 3 courses. So without having any further discussion, let’s get started.
1. Using Calculation Options to Stop Converting Formula to Value
In the top cell of the column, Total Marks I’m using a formula. The formula consists of the SUM function. It sums up the total marks obtained in 3 courses by each student.
The formula is:
=SUM(C5:E5)After applying the formula, when I press ENTER, it returns 202.
Now when I copy down the formula to the entire column, it calculates the values automatically. This happens because the Calculation Options are set to Automatic.
Now suppose, you don’t want Excel to convert your formula to value automatically. Then you have to change the Calculation Options settings. To change,
- Go to the Formulas tab first.
- Then click on the Calculation Options drop-down.
- After that, click on Manual.
Now the Calculation Options are set to Manual.
Let’s copy down the formula again.
This time you can see that all the cells show the same value. This means the formula is not converting to value automatically. It’s because the Calculation Options are Manual now.
🔎 Note: After setting the Calculation Options to Manual, if you update any value that the formula uses, it doesn’t affect the formula result.
However, you can change the Calculation Options to Automatic again.
For that, go to Formulas ➤ Calculation Options ➤ Automatic.
After setting the Calculation Options to Automatic, you will notice that the formula is converted to value automatically again.
Similar readings
- How to Convert Formula Result to Text String in Excel
- Convert Formula to Value in Multiple Cells in Excel
- How to Convert Formula to Value Automatically in Excel
- Convert Formula to Value Without Paste Special in Excel
2. Stop Converting Formula to Value Automatically Using VBA
Here, I will show you to stop Excel convert a formula to a value automatically using VBA code. The VBA code also set the Calculation Options from Automatic to Manual. Thus, the formulas don’t convert to values automatically.
To use the VBA code,
❶ First go to the Developer tab.
❷ Then click on Visual Basic.
This will open the Visual Basic Editor.
You can also press ALT + F11 to open the Visual Basic Editor.
❸ After that, go to Insert ➤ Module.
❹ Now insert the following VBA code in the new module that you’ve created.
Private Sub Manual_Calculation()
Application.Calculation = XlCalculation.xlCalculationManual
End Sub
❺ To run the VBA code, press the F5 button.
Now insert the following formula in the top cell of the Total Marks column. It returns 202.
Now if you copy down the formula to the entire column, all the values will be exact 202.
This means the Calculation Options have been set to Manual. That’s why Excel has stopped converting formulas to values automatically.
Read More: Excel VBA: Convert Formula to Value Automatically
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the methods discussed in this article.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 2 ways to stop Excel convert a formula to a value automatically. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.
