Excel offers over 300 library functions. Although using these functions has made calculations easier, using them correctly can sometimes be tricky. Even the most experienced users can sometimes run into Excel formulas not working. You may be encountering one of these situations in your Excel program if your formula isn’t working:
- A pound (#) error code
- Excel displaying wrong results
- Excel displays the formula, but no results
- “There’s a problem with this formula..” error message
- “..Links to one or more external source..” error message
The fixes to Excel formulas not working depending on which of these issues you’re dealing with. Besides human errors, Excel may incorrectly calculate formulas if there is an issue with your Office app. In such cases, you must reinstall MS Office to fix the problem with its program files.
Solve Excel Error Message
Excel has a total of eight error messages. Excel displays each of these messages for different purposes.
#NAME?
If you encountered the #NAME? error, there is an issue with the function you’ve used in the formula. Check if you’ve correctly entered the function in your formula.
#VALUE!
Excel displays the #VALUE! error when there is an issue with the cell format. Recheck the cell format of the referred cells from your formula. If they’re under a different cell format, change it from the Home tab.
#REF!
You encounter the #REF! error when Excel has a problem locating the reference from your formula. Check if you’ve properly referenced your cell. If you’ve used absolute referencing, see if you’ve moved your data to a different location.
###
Excel displays this error when your cell content does not have enough room. Select the column with the error code and expand the column to display the full value.
#DIV/0!
If you run into this error, Excel alerts you that your data was divided by 0. Any value divided by 0 is infinite, and Excel cannot process it. To fix this error, change your data where it is not divided by 0.
#NULL
You probably have entered an incorrect syntax if you entered the #NULL error. Check if you’ve properly used the separators, including comma (,) and colon (:).
#N/A
Check if the cell you’ve referred to in the Excel formula is removed. Excel mostly displays the #N/A error when Excel cannot locate the referred cell.
#NUM
Excel uses the #NUM error to alert you that your number has non-numeric content. If you’ve entered a comma (,) or a special symbol such as the dollar ($) sign, remove these values to solve this error message.
Solve Excel Not Calculating Formula
If Excel only shows formulas but not results, there are three possibilities you could explore. You may either have the Show Formula or Manual calculation options enabled.
Excel will also fail to calculate if the cell you’ve entered in your formula is formatted as text. If you have entered ` before data, Excel will read your data as text. If you do not intend to use it as a text, remove the ` symbol from your data.
Disable Show Formula
- Launch MS Excel to open your document.
- Head to Formulas from the menubar.
- If the Show Formula option is selected, click it to deselect it.
Enable Automatic Calculation
- Open your spreadsheet.
- Select Formulas from the menubar.
- Click Calculation Options.
- From the fly-out menu, choose Automatic.
Format Cell to General
- In your workbook, select the cell or cell range with the formula.
- On the Home tab, select the fly-out menu in the Numbers section.
- Choose General.
Check Arguments
Except for PI and TODAY, Excel formulas need arguments to calculate the results. If you haven’t entered your arguments, Excel formulas will not work. Excel will present a problem with this formula error message in such cases. If you’ve entered your arguments, you should double-check them to ensure you have entered them correctly.
If your formula looks right, select a different list separator from the Excel options. For example, you may use a comma (,), but you’ve selected a semi-colon (;) as your list separator. You can change this configuration from advanced Excel settings.
You can configure list separators by thousands and decimals from Excel settings.
- Launch Excel.
- Head to Options from the sidebar.
- Select Advanced from the left panel.
- Scroll down to the Editing options section and deselect Use system separators.
- Enter the appropriate symbol for Thousands and Decimal separators.
Check References
Excel allows you to reference another worksheet or cell in its grid. If you have made such references, check if they’re correct. Additionally, Excel formulas with references will not work if you’ve moved or deleted the reference.
Make sure you haven’t created a Circular reference in your sheet. A circular reference is when you refer to the same cell you’re entering your formula in. This creates an infinite loop Excel cannot calculate.
Use Less Nested Functions
Excel only supports a total of 64 functions in a single formula. If you have nested more than 64 functions in a formula, break the formula down into smaller formulas. Not only will it bypass the restriction, but you will also leave less room for error.
Check for Syntax Error
Usually, there are four symbols in an Excel formula; the equals to, comma, parentheses, and the double quotation sign. If you do not use these symbols correctly in your formula, Excel will fail to calculate a result.
Formulas in Excel must start with an equal to (=) sign. Excel will only read your data as a formula once you start it with the equals sign.
Similarly, references are usually separated by a comma sign (,) in a formula. If you do not use a separator, Excel will read your references as a single entity and may not work.
Arguments in a formula are written inside Parentheses (). Especially in nested functions, ensure you have entered an equal amount of open and closed parenthesis.
Only use the double quotation mark (“ ”) to add text to your formula. Excel will not recognize the syntax if you’ve entered your value in single quotes (‘ ’). Additionally, ensure you do not enter a numeric value you intend to manipulate inside double quotes. Excel will read your numeric value as text in the formula. This will produce a different result than you expected.
If you’ve used operators such as Ampersand (&), Multiply (*), and Divide (/) make sure you’ve used the correct symbols. If you use a different symbol, you will get a different result.
