How to Stop Formula to Convert into Value Automatically in Excel

We all know that Excel is a powerful tool, but in order to anticipate a user’s needs, Excel might make it tough to design sheets for importing data. In Excel, the calculation mode is set to Automatic by default. When you enter a formula into a range, Excel automatically calculates the formula’s value. However, if you do not want Excel to do this, you may disable this option. This post will teach you how to stop excel convert formula to value automatically.

📁  Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.

Stop Formula to Convert into Value Automatically in Excel Using These 2 Quick Methods

Today, we’ll learn about 2 methods to stop formulas to convert into value automatically in Excel. Let’s get started with the session.

There are three lists of numbers on the Excel sheet. One for physics, one for chemistry, and another for maths. Now, we’ll look at the sums in those columns.

Method 1

Utilizing Calculation Options to Stop Formula to Value Conversion

If you don’t want Excel to automatically turn your formula into a value, you need to change the settings for the Calculation Options on the Ribbon. We will use the SUM function here also. The SUM function adds numbers together. You can add single values, references to cells or ranges, or a combination of all three.

⬇️⬇️ STEPS ⬇️⬇️

  • First, we use the SUM function which will sum up the total marks of these 3 subjects. The formula is below:


After I use the formula and press Enter, it gives me the number 217.

Stop Formula to Convert into Value

  • Now, when I copy the formula to the whole column, the values are calculated automatically. This is because Automatic is chosen for the Calculation Options.

Stop Formula to Value Conversion

Now, let’s say you don’t want Excel to automatically turn your formula into a value. Then we will need to change the settings for the Calculation Options. In order to change,

⬇️⬇️ STEPS ⬇️⬇️

  • First, we click on the Formulas tab.
  • Then we click on the drop-down menu next to Calculation Options.
  • We click on Manual after that.

Stop Formula to Convert into Value Automatically


  • Let’s write down the formula once more. This time, all the cells show the same value. This means that the formula is not automatically turning into a value. This is because the Calculation Options are now set to Manual.

But you can switch Calculation Options back to Automatic.

⬇️⬇️ STEPS ⬇️⬇️

  • We go to Formulas, click Calculation Options, and then click Automatic.
  • When we set the Calculation Options to Automatic, we’ll see that the formula is automatically turned back into a value.

📕 Read More: How to Convert Formula to Value for Multiple Cells in Excel

Method 2

Using VBA to Stop Automatically Converting Formula to Value

Here, I’ll show you how to stop Excel from using VBA code to automatically turn a formula into a value. The Calculation Options were also changed from Automatic to Manual by the VBA code. So, the formulas do not automatically turn into values. To use the code for VBA,

⬇️⬇️ STEPS ⬇️⬇️

  • We go to the Developer tab first.
  • Then we click on Visual Basic next. The Visual Basic Editor will open. We can also use shortcut Alt+F11.

Stop Formula to Value Conversion using VBA

  • Then, we click Insert, then we click Module.

  • We added the following VBA code to the new module we just made.
Private Sub Utilizing_VBA()
Application.Calculation = XlCalculation.xlCalculationManual
End Sub

Stop Formula to Convert into Value Automatically using VBA

  • Then we press F5. Now, we put the following formula in the top cell of the Total Marks column. It gives back 217.
  • Now, if we copy the formula down the whole column, every value will be 217.

📄  Important Notes

🖊️  In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

🖊️  A practice workbook is given so that you can practice yourself.

🖊️  All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

🖊️ At the end of the Excel file, there is a sheet. This is where you can try out all of the methods discussed in this article.

📝  Takeaway from This Article

📌  Reader will be able to stop the formula from converting into value automatically in Excel with various methods.

📌  One will be able to stop the formula to convert it into value automatically using VBA in Excel.


That concludes today’s session. These are the methods for stopping any formula to convert into value automatically in Excel. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen, the best Excel solutions provider.

Related Articles

(Visited 100 times, 1 visits today)


My name's Tanzim. My articles are meant to help you learn more about how Microsoft Excel works and how to use its different features. I enjoy gaming and hope that some of my knowledge will help me provide you with some interesting articles despite some tiresome technical terms.

We will be happy to hear your thoughts

Leave a reply