You are seeing the formula itself instead of its calculated result, or seeing it without an equals sign, your formula is not responding to some changes, or is responding with an unexpected result. If we’ve hit the nail on the head, you are welcome to browse this tutorial on how to fix Excel formulas that are not calculating.
The potential issues we will discuss today are Automatic Calculation being off, Show Formulas being on, formula as text, formula with a leading apostrophe or without the equals sign, and circular references.
Find out about these Excel formula troubles in detail and how to swipe them clean from your workbooks because what good is Excel if it’s misbehaving in formula performance?
Out with the Excel toolbox and let’s get fixing!
Automatic Calculation is Turned Off
The first culprit that may be causing a formula to not work is the Automatic Calculation Option, particularly when it is turned off. The implication of that is the Manual option being on instead. Consequently, formulas will fail to update when a value of a referenced cell in the formula has been changed.
Turning off Automatic Calculation may be helpful to quicken a slow file. Excel’s calculation/recalculation of formulas may lag a workbook especially if it contains many formulas, volatile functions that recalculate with every recalculation in the workbook, off-worksheet or workbook links, or data tables.
In such cases it may be deemed efficient to turn off Automatic calculation. Read ahead to find out how Automatic and Manual Calculation options behave.
What are Automatic & Manual Calculation Modes
On a regular Excel day, when you change a value of a cell that is referred in a formula, the formula recalculates to adjust as per the new value entered. A volatile function recalculates with every recalculation in the workbook and when the workbook is opened. This is the default Calculation Option i.e. the Automatic Calculation Option.
With the Manual Calculation Option selected instead, see the static behavior of formulas. In the example shown below, have a look at cell F18. The pre-tax total is calculated as $87.50 by multiplying the unit price in D18 by the number of units in E18:
Let’s say we have just received a notification that the price of this product had gone up from $8.75 to $9.00 but hadn’t been accounted for in the system. When we punched in the new unit price, the pre-tax total did not update itself.
When we checked the Calculation Options, we found that it has been set to Manual.
In the Manual mode, the formula will not recalculate unless you recalculate the formula cell itself. Volatile functions will also not update by opening the file or with recalculation in the workbook.
As a result, there will be three ways of getting the formula cell to recalculate.
- Editing the formula cell will make it recalculate.
- Using other calculation options in the Formula tab:
Calculate Sheet (Shift + F9) will recalculate the active worksheet while Calculate Now is for the whole workbook. To recalculate all open workbooks, press the Ctrl + Alt + F9 keys.
- Either these manual methods, or you can switch the Calculation Option back to Automatic. We may have given the way to do that away but here it is.
How to Turn Automatic Calculations On
This is the fix on having the Manual Calculation Option on. Select the Formulas tab and then the Calculation Options icon from the Calculation group. Click on the Automatic option.
The tick mark shifts from Manual to Automatic in Calculation Options and all stuck formulas will instantly recalculate when the Automatic option is selected. Closing or reopening the workbook will not change this setting and it will also apply to all other open workbooks and workbooks subsequently opened after changing the Calculation Option.
If need be, you can turn the Automatic recalculation off again after finishing your work in the workbook by selecting the Manual option.
Show Formulas Option is Turned On
The columns are blown wide open, the Number Formats are flayed, and the formulas are baring themselves, having eaten up the formula output. What is going on? If the correct guess is Show Formulas being the pest, the expansion in column widths allows the exposed formulas to be displayed.
This was the ideal life of our worksheet:
Now there’s this:
And that’s just half of the sheet.
In this state, no new or old formulas on the spreadsheet will calculate; the formula will only be displayed like text. That is quite the whole point of the Show Formulas feature; it makes every format like text when enabled; the Number Formats will be gone; dates will turn into serial numbers, numbers will be aligned from right to left and formulas will show as text instead of the calculated value.
A selected formula cell will also highlight the referred cells as in cell-edit mode (note the difference in the before and after sample shots above).
In some situations, use of this feature is warranted as it is much easier to view all the formulas instead of going to every cell of interest and reading the formula off the Formula Bar. But anyway, you might be done with the formula exposure or want nothing to do with this Expocalypse.
Either way, below you’ll see how to turn the Show Formulas option off.
Go to the Formulas tab > Formula Auditing group to check if Show Formulas is highlighted in a darker gray, indicating that the feature is on:
Click on the Show Formulas button to turn it off.
The sheet will be restored to the original form without any value or format actually changed:
You can work the Ctrl + ` (grave accent) keys as a keyboard shortcut to toggle Show Formulas on and off.
Notes:
Show Formulas only applies to the active worksheet.
Show Formulas applies to all the formulas on the sheet. Therefore, if it’s not all the formulas giving you trouble collectively, your trouble might be explained in the next sections.
Recommended Reading: Excel Shows Formula Instead of Result
Formula is Entered as Text
If the other formula cells are looking and doing alright, and one (or a few) aren’t, good chances are that the formula is entered as a text. There could be a handful of reasons for this.
The format of a certain range on a sheet could have been preset as Text so that when a formula was entered, it behaved as simple text instead of returning the formula’s result. Or the format could have been set by the person who passed the file down to you. If it was you, you’d know why you did it.
How this problem will appear on a worksheet:
H20 is not in cell-edit mode, yet the formula is showing just by selecting the cell. In the highlighted Number Format Bar, it is evident that the format is set to Text instead of General or any other relevant Number Format.
Now for the fix. Select the cell with the Text format. Click on the right-side arrow of the Number Format Bar and select the General or any other format you wish to apply to the cell.
For more Number Formats, select the More Number Formats option at the bottom of the menu. You will be led to the Format Cells dialog box for many more formats to apply and edit.
After selecting the format (we have chosen the Currency format from the Number Format Bar), you need to recalculate the formula cell to get it to accept the change in format and return the result of the formula.
Now we have gotten the formula output in the applied Number Format.
Circular References in the Formula
A formula will not deliver the expected result if it contains a circular reference. In Excel, a circular reference occurs when a formula contains a direct or indirect reference of the cell it is in.
Excel won’t leave too much to the imagination the first time around; when you hit the Enter key on the formula containing a circular reference, Excel will produce a pop-up warning and that would be a good time to change the location of the formula cell or to edit the active cell out of the formula.
A small example of what a direct circular reference and its warning message looks like:
The alert will only show the first time a circular reference is entered in a workbook.
While calculating the sum of the post-tax figures in column H, we have accidentally extended the cell reference in the formula to H3:H20 instead of H3:H18. This way, the formula contains the formula cell itself and triggers the circular reference warning.
An indirect circular reference arises when the target cell contains a formula referring to a cell that refers back to the target cell. Very much the circle of Excel life.
When you close the error message, the formula with a circular reference ends up in 0 (or the last calculated value in some cases):
While this is the sum of the column and you know that the final value can’t be 0, for another formula you may assume 0 to be the answer and that throws off the representation of your dataset. You can check for circular references in the Formula tab > Formula Auditing group > Error Checking menu > Circular References option. The feature confirms that indeed there is a circular reference in cell H20. Click on the mentioned cell reference and the feature will select the problem cell on the worksheet.
It only shows one circular reference at a time in the submenu; the same one that is displayed in the Status Bar but the Status Bar won’t select the shown circular reference cell. Use the feature to track all the circular reference cells and eliminate them editing the formula, changing the cell location, or deletion.
Missing Equals or Unnecessary Apostrophe in the Formula
If the formula is missing the initial equals sign or has a leading apostrophe, the formula will not calculate. Both things happen because of one reason; while the rest of the formula appears alright, it is actually no longer a formula.
The operator that makes a formula what it is, is the equals sign. You can have the rest of the syntax A-OK but without the equals sign, Excel doesn’t get the signal that a formula is being used and returns the value as a string.
No equals sign, no calculation. Add the equals sign to the formula to get it to calculate. Copy the formula down if need be.
Secondly, you may include the equals sign but the calculation is going nowhere if the formula is preceded by an apostrophe:
Now that we got the equals sign in the game, we’re still not getting the result of the formula as it is led by an apostrophe. This renders the value of the cell as text while the apostrophe doesn’t appear in the cell itself. It can be seen in the cell value in the Formula Bar, as can be deduced from above. The effect will be similar with other leading characters as no character should precede the equals sign if you want the formula to calculate.
This may be deliberate for showing the formula but in the case that it isn’t or when you want to calculate the formula, remove the apostrophe.
General Tip: Make sure the formula itself is right in the usage of the syntax, arguments, and operators. Although in most of these cases, an error or erroneous formula message is likely to be triggered, pointing you towards the correction you need to make.
We hope we put the troubleshooting guns to good use and your formula predicament has been finally resolved. By now you know that a formula problem can be internal, in the formula, or external with other features at play. Securing our troubleshooters back in their holsters, we’ll ready them for the next round of firing!
About Mehwish Javaid