When your Excel formula is not calculating, or not updating, it can be very frustrating. Your formulas are the driving force for your spreadsheet.
There are 5 reasons for your Excel formula not calculating. In this tutorial we explain these 5 scenarios.
Watch the Video – Excel Formula Not Calculating
1. Calculation Options is Set to Manual
The first thing that you should check is that the calculation options are not set to manual. This is the most likely problem.
Click the Formulas tab and then the Calculation Options button.
If this is set to manual, the formulas will not update unless you press the Calculate Now or Calculate Sheet buttons.
Change it to Automatic and the formulas will start working.
This setting can be changed by macros, or by other workbooks that you may have opened first. So if you are not aware of this setting, it could still be a reason for the formula not calculating.
2. Formula Not Calculating as Cell is Formatted as Text
Another common reason is accidentally formatting the cells containing formulas as text. These will not calculate whilst in this format.
To check this; click on the cell and check the Number group of the Home tab.
If it displays Text. Change the format to General using the list provided.
Then re-calculate the formula in the cell by double clicking on the cell and pressing Enter.
3. A Space is Entered Before the Equals
When typing the formula be sure not to enter a space before the equal sign. This is difficult to notice so can go unrecognised, however it will prevent the formula from calculating.
Double click the cell, or edit it in the Formula Bar. Check if there is a space and if so delete it. The formula will update.
4. An Apostrophe is Entered Storing the Formula as Text
When an apostrophe (‘) is entered before typing in Excel, that tells Excel to store the content as text. This is a common approach to store numbers such as phone numbers as text to retain the leading zeros.
This however could be the reason why your formula is not calculating.
The apostrophe will not be visible in the cell on the spreadsheet, but you can see it in the Formula Bar.
Double click the cell, or edit it in the Formula Bar and delete the apostrophe.
5. The Show Formulas button is Turned On
The final reason could be that the Show Formulas button on the Formulas tab is turned on. This can easily be done accidentally, or possibly by someone else using this workbook previously.
This button is used when auditing formulas. It shows the formula instead of the formula result, stopping them from calculating. This can be helpful when troubleshooting formula problems.
In this example, the Show Formulas feature is stopping Excel auto calculate formulas in column C and a SUM function in cell E2. Excel is displaying the formula, not the result.
So, if a Microsoft Excel cell is displaying formula and not result, click the Show Formulas button again to turn it off and the formula will be working. If you are a keyboard shortcut fan, you can also press Ctrl + ` to toggle Show Formulas on and off.
Hi Alan,
A 6th reason for formulas not calculating is when there are circular references somewhere in the workbook.
Damn those pesky circular references.
If anyone is not familiar with circular references, check out this tutorial explaining how to handle them.
THAT solved my issue, circular references. A THOUSAND thank you’s!!
You’re very welcome. Great to hear.
I have a column of times (13:30 format). Row 77 is to be a calculation of those times. It works above but not this row?? Snippet below. Any suggestions? I’ve made sure cell D77 is formatted as 13:30 as above. Cleared all formulas & saved; deleted row 77 and added, and checked the formatting of this cell against others that work above and below. Confused.
I think I would need to see what is going on to solve this, Terri.
Anyone know where you can find advanced answers for Excel issues? These are all very basic, and are the same things listed on 100 other sites. My problem showed up in a file that has been used for months, and then just randomly decided to stop calculating, even though none of the issues above were applicable.
Hi Paul, The issue is not always advanced. There will be reasons a formula is not calculating. You can targeted help from Excel Rescue – http://bit.ly/2Ms1d2h
there is another reason, is Excel just not functioning correctly. after Excel changed to 365 version. this type of error happens all the time. I am so sick of it. I never experienced same amount of error before this version.
What error? There are reasons for Excel not calculating which are detailed in the article. Excel doesn’t function incorrectly though except software crashes. If there is an error to a calculation, there is a reason for it.
I have double checked all of these, and despite all being correct, my simple formula still won’t calculate. I’m simply trying to use SUM to add single digits across columns. It works for the first two rows, but not for any of the following. I’ve tried both copy/paste of the formula and re-entering it manually in a cell. No go, still returns 0, when it should be returning 9 or 10, etc. None of these reasons listed here covers it!
Difficult to answer without seeing the spreadsheet Tracy. Check the number are definitely numbers. Are they right aligned?
thanks saved me
Fantastic!!!