Formulas are one of my favorite parts of using Excel. You can use one, or nest many in one function to create a formula that gets calculations done in a snap of a finger. However, there are a sea of reasons that could go wrong while performing calculations using formulas in Excel.
There are multiple cases where your formulas seemingly won’t calculate. You could be met with an error message, or you could just see the formula and not the result when you hit enter. We have covered all cases where formulas don’t calculate in Excel. Keep reading this article to carry on with your calculations in no time!
Case 1: You Can Only See the Formula, Not the Result
If your formula does not return a value, there can be two reasons behind this issue. You could’ve either entered the formula in a cell that is formatted as text, or you’ve enabled the Show Formula option.
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 a formula. While you could change the text format to number, you can also change it to general to accommodate other values like dates.
Similarly, when you enable the Show Formula options, all formulas in the spreadsheet will be displayed. This may appear as the formula not returning any value.
Convert Cell from Text to General
- Select your cell from the grid.
- On the Home tab, select the fly-out menu from the Numbers section.
- Choose General.
Remove the Aphostrophe in front of Your Formula
If you have entered an apostrophe in front of your formula, it will command Excel to treat your formula as text. 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
- Head to the Formulas tab.
- In the Formula Auditing section, deselect Show Formulas.
Case 2: Excel is not Calculating After Editing Referenced Cells
In this case, your formula worked perfectly fine before you edited a cell your formula referenced. Now, the result isn’t updating after you’ve made changes to one of the cells you’ve referenced.
This is because you’ve set your calculation settings to manual. Your formula will refresh once you hit 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.
Case 3: Formula Results in an Error
There are nine errors you may encounter while trying to use formulas in Excel. All of these errors are 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. |
| Circular Reference | Your formula is referencing the active cell. | You cannot reference the cell you’re entering the formula on. If you’re looking to loop your formula several times, you can use the ROW function to create an array. |
Case 4: You’ve Nested More than 64 Functions in a Formula
You cannot nest more than 64 functions in a single formula in Excel. You can try to use a single formula instead of nesting multiple formulas.
For example, you can use the VLOOKUP function instead of nesting the MATCH and INDEX functions. Similarly, you can opt to use one COUNTIFS function instead of nesting multiple COUNTF functions.
Case 5: Formula is Calculating a Different Result
So usually if your formula results in something off, you would want to check the arguments first. However, sometimes it’s not the formula that is at fault; it’s the function.
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.