Formulas are one of my favorite parts of Excel. You can use one, or nest many in a function. It’s quick and very versatile but not free from problems. If your formula is not calculating or even updating, there could be few cases to this.
Check Formula’s Syntax
Syntax are quite important in Excel to declare a data type. There are four basic syntax used in Excel including the equals to, comma, colon, and parentheses. If you miss out on any of these syntax, your formula may not calculate.
If you’re a beginner in Excel, here is a brief breakdown of these syntax and their functions:
| Syntax | Name | Description |
| = | Equals to | Declares that the following data is a formula. |
| , | Comma | Separates arguments inside the formula. |
| : | Colon | Specifies a range. |
| () | Parentheses | Used to enter arguments inside a function. |
Convert Text Cell to General
When your cell is formatted as text, Excel will treat the content inside your cell as text. This means it will not perform any type of calculations, including running the formula.
While you could change the text format to number, you can also change it to general to accommodate other values like dates.
- Select your cell from the grid.
- On the Home tab, select the fly-out menu from the Numbers section.
- Choose General.
Your cell will also be treated as a text if you’re entered an apostrophe before your formulas. The apostrophe will not appear with the grid so if you’ve added the symbol, you must remove it from the formula bar.
Select the cell with your formula and head to the formula bar. Remove the apostrophe in front of your formula and hit Enter. Your formula should now work.
Disable the Show Formulas Option
If you’ve enabled the Show Formulas option in Excel, instead of displaying the result of your formula, Excel will instead only display the formula. This may look as if the formula is not calculating at all.
You can go ahead and disable the Show Formula option, and it should do the trick. As Excel supports keyboard shortcuts, you can also use the shortcut to show or disable the show formula feature.
Shortcut: Ctrl + `- Head to the Formulas tab.
- In the Formula Auditing section, deselect Show Formulas.
Check Calculation Settings
If your calculation settings are set to manual, Excel will not update your formula automatically after you’ve edited it.
You can refresh your formula by hitting the F9 key on your keyboard. If you find it a hassle to refresh it every time you make changes, you can change your calculation settings to automatic.
- Open your spreadsheet.
- Head to the Formulas tab from the menu bar.
- In the Calculation section, select the Calculation Options.
- Choose Automatic.
Cell References and Error Message
There are nine errors you may encounter while trying to use formulas in Excel. All of these errors are the results of inconsistencies in your formula.
| Error | Possible Cause | Solution |
| #NAME? | The function or reference is typed incorrectly or does not exist. | Select the cell with your formula and check the function and reference name. If you have entered a text, make sure it is inside double quotes. |
| #VALUE! | The format of your reference is incompatible with the function. | Check the cells you’ve passed in the formula. If they’re stored in a different format, head to Home and change the cell format from the Numbers section. |
| #REF! | The reference you passed in the formula does not exist. | Verify if you’ve accidentally deleted the range you passed in the formula. |
| #DIV/0! | The formula divides any number with zero. | Use Ctrl + Shift + { and check the cells referenced in your formula. See if your formula is using any cells with the DIV/0! error or, if it’s dividing any number with zero. |
| #### | The cell content does not fit. | On your keyboard, use Alt,H,O,I to autofit the content to the cell. |
| #N/A | The lookup value is not found within the lookup range. | This is a very common instance while using lookup formulas. If you’re using VLOOKUP, only select the lookup value from the first column of the lookup range. |
| #NUM! | The argument passed in the function is invalid. | Check your argument. If you have any currency symbol like $ used, remove it and run the formula again. |
| #NULL! | An incorrect range operator or, intersection does not exist between specified ranges. | Use the correct range operator, which is the semicolon (:). If you intended to specify an intersection, check if it really does exist between the ranges you added the intersection operator (space) in between. |
Formula Contains Circular Reference
Your formula attempts to perform a circular reference if you run into an error message that looks like “There are one or more circular references where a formula refers to its own cell either directly or indirectly…”
Circular referencing occurs when you try to reference the cell you’re entering the formula inside the formula. For example, if I’m entering my formula inside cell A4, I can’t enter A4 as one of my references inside the formula. If you’re looking to loop your formula, you can use the ROW function to create an array.
Error Typing the Formula
If you received an unexpected result out of a formula, there could be something wrong with the function or the arguments.
For example, I created a named range, SUM. Now, as we know SUM is also a function. When I tried using the function to add two cells to the sheet, it gave me a completely different result. It is because I actually used the named range instead of the function.
This situation is also more likely when you create your own function. If you have created a named range with the same name as the function, verify if you’ve in fact used the function or the named range.
Add-ins or Extension
Add-ins are great utility and oftentimes help greatly in automating certain tasks. However, certain third-party add-ins can cause issues within the Excel program. If the solutions we listed above don’t help, you should try disabling your add-ins one by one until you find the faulty add-in.
- Head to File > Options.
- Go to Add-ins from the sidebar.
- Next to Manage, select COM Add-in > Go.
- Uncheck an add-in and click OK.
- Repeat this until you find the faulty add-in. Once you do, select the add-in and click Remove.
Insufficient Memory or System Resources
Excel by default uses all of your system’s processors to conduct a multi-threaded calculation. This generally decreases the time taken by Excel to perform such computing, however, if you have other heavy applications in use, your might get the “Excel ran out of resources while attempting to calculate..”
You can decrease the number of processors Excel uses to fix this issue.
- Go to File > Options.
- Head to Advanced from the sidebar.
- Scroll down to the Formulas section and select the option next to Manual.
- Use the down arrow to set the number of processors to 1.
Hidden or Filtered Cells
Excel will also take the hidden rows and columns into account if they exist in your specified range. Even if these cells are empty, Excel will go through each one of them while recalculating your worksheet. If there are more referenced cells in rows and/or columns, Excel will take a significant time to make this calculation, which could be translated as the formulas not calculating.
Similarly, if your hidden or filtered cells do contain a certain value, they will be used as a reference in your formula. This might give you an unexpected result from your formula.
Change the cell reference to avoid using hidden or filtered cells in your formula.
External References or Links
If one of the cells you referenced from any external worksheet or workbook contains an error, that error will be reflected in your destination cell. You can use Excel’s error-checking tool called Trace Error to check the precedent cells.
- Select the cell with the error and head to the Formulas tab.
- In the Formula Auditing section, select Error Check > Trace Error.
- Double-click on the black line.
- Click and open the references to check if they contain an error.
