If you are working on an Excel spreadsheet and finding difficulty in auto-calculating the formula, it must be because of the disabled auto-calculate option. So, if you are one of those users finding the same problem, try fixing the issue by enabling the auto-calculation option, checking the entered formula is correct or making sure the cell format must be set to General. Also, read this complete article for more workaround to fix the Excel formula is not auto calculating.
Today, in this article, we are going to discuss the different ways to fix Excel won’t automatically calculate formulas. So, check them out one by one and follow them to get rid of this problematic situation.
How To Fix Excel Formula is Not Auto Calculating?
Here are some of the ways in which you can escape the Excel formula fails to auto-calculate.
- Make Sure Auto-Calculate Option is Enabled
- Disable Show Formulas Option
- Check the Cell Format
- Ensure the Entered Formula is Correct
- Check for Leading Space or Apostrophe Before the Equal Sign
Solution 1# Make Sure Auto-Calculate Option is Enabled
The most possible and the primary reason behind this problem occurrence is the disabled auto-calculation mode. So, the first thing you need to ensure is that Automatic calculation mode must be enabled.
To do so, follow the below instructions:
- Go to the File tab appearing at the top-left corner.
- Then, head to the Options.
- Select the Formulas tab on the left panel.
- Now, here check if the calculation mode is set to Automatic or Manual. If selected manual set to Automatic.
- Tap Ok and exit the Excel spreadsheet.
Solution 2# Disable Show Formulas Option
Another reason occurring such a problem is the enabled Show Formulas option. If the spreadsheet displays formulas within cells instead of the result, it means that Show Formulas is turned on. This button is usually used while auditing the formulas. It shows the formula instead of the concerned formula result.
So, in such a case, you need to disable this option in order to get away with formulas in your spreadsheet are not automatically calculating problem.
Below are some of the ways to turn off Show Formulas:
- Click on the Show Formulas button on the Home menu tab.
- You can also press Ctrl + ‘ (Hotkey) to turn off the Show Formulas.
Solution 3# Check the Cell Format
If the cell format is set as Text, Excel might find difficulty in auto-calculating the formula. In Text format, Excel displays the formula instead of the formula result. Therefore, we recommend you check for the cell’s format. If set to Text, try changing the format to General to resolve the issue.
Here’s how you can do so:
- Check the Number group on the Home tab.
- If it shows Text. Click on the dropdown menu icon.
- Select General from the appeared list.
- Now, the cell will display the output result for the entered formula.
Solution 4# Ensure the Entered Formula is Correct
In case you haven’t entered the formula correctly, Excel won’t automatically calculate. If you have entered any non-printed characters, mentioned numbers in double quotes, etc, Excel will display the function itself instead of its result.
Also, check for any leading space or apostrophes at the beginning of the function. If you have entered any space or apostrophe before the equal sign in the formula, the spreadsheet will not display the output. Therefore, make sure the formula you have entered in the cell is correct.
Also Read: How To Fix #VALUE! Error in Excel
Solution 5# Check for Circular References in Spreadsheet
If the Excel spreadsheet is not auto calculating formula, check for Circular References. A circular reference indicates the calculation in a certain cell refers to its own result once or several times. This is mostly unintended.
So, when the user opens or creates a worksheet with circular references, it displays an error message:
“There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells.”
Therefore, in such a case, we suggest you check for circular reference.
You can check it by following the below steps:
- Go to the Formulas tab.
- Then click on the Formula Auditing option.
- Select the Error Checking option.
- Tap on Circular References which displays the circular references if any are present.
- Now, click on the cell that includes a circular reference.
- Review the cell formula and copy the formula in another cell.
How to Stop Auto Calculation in Excel?
In Excel, by default, every formula calculates automatically whenever a cell is changed. However, this can delay your work by slowing down the calculation process, especially in the case of complex files with several formulas.
So, in order to speed up the process, you need to stop the auto-calculation of formulas. You need to set the calculation mode to Manual. This won’t let the formulas calculate automatically on every change in a workbook.
You can set the calculation to Manual via 2 methods.
- In the Home tab, Click on the Formulas on the ribbon.
- Next, click on Calculation Options and select Manual.
- Open an Excel workbook.
- Click on the File tab in the top-left corner.
- Go to the Options.
- Select Formulas and click on Calculation Options.
- Click on Manual to stop automatic calculation.
- Now, click OK and exit the worksheet.
Frequently Asked Questions
- What is auto-calculate in Excel?
Auto-calculate option in Excel is the program’s ability to calculate new formula results automatically when a value or formula component changes. The spreadsheet auto-calculates the formula output by reviewing the inputs of the mentioned cells in the formula.
- When is it important to use auto-calculate in Excel?
Well, the auto-calculate mode is helpful while creating a formula. With auto-calculate, the output to the formula changes as you shift any of the cells in the formula’s range.
So, these are the solutions that work for you to fix Excel formula is not auto calculating. Here, we tried our best to help you with this problem by listing all the potential fixes.
Now, follow the fixes and solve the problem easily.