Why Excel Formulas May Not Be Working, Updating, Or CalculatingPossible causes and how to resolve them
Formulas are an essential part of the Excel experience.
Without them, you’ll only be able to access a very small part of Excel.
I mean, you even have to use a formula just to perform the basic mathematical operation (addition, subtraction, multiplication, division) in Excel.
I just can’t imagine anyone using Excel without using formulas. If anyone is, what are they really using Excel for?
So I get why it could really be frustrating when a formula in Excel suddenly stops working properly.
It’s especially more frustrating if you don’t why the formula isn’t working as it should. “Did I enter the formula incorrectly?
Why is it not updating the result when I made changes to referenced cells?
Is something wrong with my Excel app?”.
These are just some of the questions one might ask when their Excel formulas suddenly stop working properly.
Well, let me say first that, most probably, there’s nothing wrong with your Excel application.
Some other stuff is causing your formulas to not work properly, but it’s not because of a corrupt Excel app.
In this article, I’ll be showing you the possible causes of why Excel formulas aren’t working, updating, or calculating.
We’ll also discuss how we can fix this issue. By the end of the article, you’ll be able to fix non-working formulas whenever you have to.
Let’s get started.
Excel Formulas Not Calculating
When a formula does not calculate, the cell that contains the formula will show not show the result.
Instead, it is the formula itself that will appear on the cell. It could be happening because of the following:
The “Show Formulas” Feature is Enabled
The “Show Formulas” feature prompts Excel to show all the formulas contained within the worksheet instead of the result.
The most probable reason why your Excel formulas aren’t calculating is that you accidentally enable this feature.
There are two ways to enable/disable this feature:
- Pressing the keyboard shortcut Ctrl + `
- Clicking the “Show Formulas” button via the Formula tab
To fix this issue, you just need to disable the “Show Formulas” feature.
The Formula Doesn’t Start with an Equal Sign (=)
For an Excel formula to properly calculate, it must start with an equal sign (=). If it doesn’t start with one, then Excel will treat it as text instead.
It’ll be easier to spot the error if a formula doesn’t have an equal sign or starts with a character that isn’t an equal sign, an apostrophe, or a space.
If the formula starts with an apostrophe or a space, the error will be less obvious.
To fix this issue, you need to make sure that the formula starts with an equal sign.
Delete any unnecessary character before the equal sign.
The Cell Where the Formula is Entered Is Formatted as Text
If a cell is formatted as text, anything that is entered into it will be treated as text (even numbers or formulas).
This could be the reason why the formula you just entered isn’t calculating – it’s technically not a formula but rather a text string.
Thankfully, this only happens if the cell is already formatted as text before entering the formula.
If the text cell format is applied only after the formula has been entered, the formula will still work as a formula.
To fix this issue, you’ll have to change the cell format to general or any other appropriate number format.
You may have to enter cell edit mode to make sure that the formula works properly.
Excel Formulas Not Updating
Suppose that you’ve made changes to the referenced cells in a formula. However, you’ve noticed that the formula did not update the result. It still shows the old result. What gives?
There’s only one possible cause for this: Excel’s “Calculation Options” setting is set to Manual. In such a case, any Excel formula within the current workbook will not update automatically. You’d have to manually update them.
If you want Excel to automatically update formulas, you’d have to set “Calculation Options” to Automatic. To do so, open the Formulas tab. On the right side of the ribbon, you should see a “Calculation Options” button.
Click it. Then, select Automatic.
Your formulas should update properly now.
You can also change the Calculation settings in Excel Options:
- In Excel 2003, click Tools, then select Options, then Calculation, another Calculation, and finally, select Automatic.
- In Excel 2007, click the Office button, select Excel Options, select Formulas, then Workbook Calculation, and finally, select Automatic.
- In Excel 2010 and up, open the File tab, then select Options, select Formulas, go to the Calculation Options section, and select Automatic under Workbook Calculation.
Force Excel Formulas To Recalculate
If you need to set the “Calculation Option” to Manual, you can force Excel to recalculate formulas. You can do this by workbook or by worksheet (the active sheet).
To force recalculate a workbook, you may:
- Press the F9 key, or
- Click the “Calculate Now” button via the Formula tab
To fore recalculate the active sheet, you may:
- Press the keyboard combination Shift + F9, or
- Click the “Calculate Sheet” button via the Formula tab
Excel Formulas Not Working
If a formula is written incorrectly, it will return an error or a wrong result.
There are many possible reasons why your Formula isn’t working.
In this section, I’ll be showing a list of the most common mistakes that one might make when entering formulas in Excel:
One or more of the required arguments in an excel function is not entered
Excel functions will always have at least one required argument. Some functions also have optional arguments that can be left blank.
For a formula that contains a function to work properly, you must enter all the required arguments of the function.
Otherwise, Excel will display the “You’ve entered too few arguments for this function” error message.
Likewise, if you’ve entered too many arguments (more than the required and options arguments), Excel will display the “You’ve entered too many arguments for this function” error message.
To fix this, make sure that you at least enter the required arguments (not less, not more). You can enter the optional arguments, but make sure that you don’t number of arguments allowed for the function.
Enclosing numbers in double quotes
In Excel formulas, you don’t have to enclose numbers or cell references in double-quotes.
If you enclose a number or cell reference in quotes (e.g. “A1 “), it will be treated as a text string instead. So the simple =1+1 formula will not work if it’s written as = “1 “+ “1”.
To ensure that your formula works properly, do not enclose numbers or cell references in quotes unless you intend to have them to be treated as text.
Not enclosing text in double quotes
In Excel formulas, you have to enclose text strings in quotes.
Otherwise, the cell where the formula is entered will show a #NAME? error value (unless the text string matches a function name).
To ensure that your formula works properly, enclose text strings in quotes.
Not matching all opening and closing parentheses in a formula
When you want to access a function in Excel via a formula, you’d have to enclose its arguments within open and closed parentheses.
This isn’t that much of an issue with simple formulas. However, with complex formulas that nest several functions, the opportunity for error grows.
Thankfully, Excel helps a bit by displaying each parentheses pair in different colors as you enter them in a formula.
Not only that, but if your formula is missing one or more parentheses, Excel will inform you via an error message. It will then suggest a correction to balance the pairs.
To avoid this from happening, make sure to properly pair your parentheses.
Nesting more than 64 functions in a single formula
In any version of Excel, there’s a limit to the number of Excel functions that you can nest into each other.
In the 2010 and later versions of Excel, this limit is 64 nested functions. Any more than that and the formula will not work properly.
The 2003 and lower versions are even stricter, only allowing up to 7 nested functions.
Be sure to not go above the limit to ensure that your formula works properly.
Entering numbers with number formatting
When you enter a number in a formula, you should only write the number and nothing else such as commas to separate thousands, or a currency symbol. Doing so will result in the formula not working properly.
Commas in formulas are used to separate the arguments of a function. The dollar sign, a currency symbol, is used to make an absolute reference to a cell or range of cells. They’re not meant to be used with numbers.
So, instead of writing $420,000 in a formula, you only need to write 420000. If you want the result to appear as currency, then you’d have to change the cell format to currency.
Not separating function arguments with the proper character
What separates a function argument from another will depend on the regional setting of Excel.
For most, it will be the comma (,). However, in European countries, the argument separator is a semi-colon (;). It may be different in other countries too.
To check what character is set as the separator, you check it via Regional Settings (which you can access via the Control Panel, then Region and Language, and finally, Additional Settings).
Not enclosing workbook and worksheet references in single quotes
When you refer to another worksheet or workbook that has spaces or non-alphabetical characters in its name in a formula, you’d have to enclose it in single quotation marks. Otherwise, it will result in a #NAME? error value.
Here’s an example of how to properly refer to a cell or range of cells in another sheet:
=SUM(‘Region I’!C2:C11)
Here’s an example of how to properly refer to a cell or range of cells in another sheet in another workbook:
=SUM(‘[2021 Sales per Region.xlsx]Region I’!C2:C11)
Not including the full path to a closed workbook
If a formula references a closed Excel workbook, the reference must include the workbook name and the full path to the workbook.
For example, the reference should look like this:
=SUM(‘D:\Sales Reports\[2021 Sales per Region.xlsx]Region I’!C2:C11)
Make sure to enclose the reference in single quotes.
Conclusion
And those are the possible reason why your Excel formulas aren’t working properly, as well as the solutions to address them.
So if a formula doesn’t work as intended, don’t panic and think that your Excel app might be corrupted.
Try the above fixes to see if any of them works.
Did I miss a possible cause of formulas not working properly?
Let me know in the comments so that we can think of a solution for it together.