# How to Stop Formula to Convert into Value Automatically in Excel

Get FREE Advanced Excel Exercises with Solutions!

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

## Related Articles Mrinmoy Roy

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 Advanced Excel Exercises with Solutions PDF  