How to Stop Formula to Convert into Value Automatically in Excel

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.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


2 Methods to Stop Formula to Convert into Value Automatically in Excel

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.

Dataset to Stop Formula to Convert into Value Automatically in Excel


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.

Using Calculation Options to Stop Excel Convert Formula to Value Automatically

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.

Using Calculation Options to Stop Excel Convert Formula to Value Automatically

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

Using Calculation Options to Stop Excel Convert Formula to Value Automatically

Read More: Putting Result of a Formula in Another Cell in Excel (4 Common Cases)


Similar readings


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.

Stop Excel Convert Formula to Value Automatically Using VBA

❸ After that, go to Insert Module.

Stop Excel Convert Formula to Value Automatically Using VBA

❹ Now insert the following VBA code in the new module that you’ve created.

Private Sub Manual_Calculation()

Application.Calculation = XlCalculation.xlCalculationManual

End Sub

Stop Excel Convert Formula to Value Automatically Using VBA

❺ 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 (2 Easy Methods)


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.


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. And please visit our website ExcelDemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo