Excel Formula Not Working can be a frustrating issue, but with the right troubleshooting steps, you can quickly identify and correct the problem. Whether it’s due to incorrect cell references, formatting errors, or disabled automatic calculations, understanding the common pitfalls can help you restore functionality and ensure your Excel work is accurate and efficient. By addressing these issues promptly, you can minimize disruptions and maintain your productivity. Always double-check your formulas and settings to prevent these problems and keep your Excel projects on track.”
- This Content Covers:
1. What are the Reasons for Excel Formula not Working and How to Fix Them?
-
- 1.1 Calculation Options set to Manual
- 1.2 Cells Formatted as Text
- 1.3 Show Formulas Button Enabled Accidentally
- 1.4 Insertion of a Space or apostrophe before Equal Sign (=) in a formula
- 1.5 Forcing Excel to Re-Calculate
- 1.6 Not Entering all the Required Arguments in an Excel Function
- 1.7 Enclosing Numbers with double quotations (“”)
- 1.8 Not Matching all Opening and Closing Parentheses in a Formula
- 1.9 Not Enclosing Workbook and Worksheet Names in Single Quotes (‘ ‘)
- 1.10 Include the Full path/Location to a Closed Workbook
- 1.11 Nesting too many Functions inside a Formula
1. What are the Reasons for Excel Formula not Working and How to Fix Them?
Here, we’ll look at several common errors we make while creating formulas that prevent them from working and learn how to fix them.
1.1 Calculation Options set to Manual
If the value you entered cannot be changed and the result is the same as what you input, Excel’s calculation option might be set to manual rather than automatic. Follow the steps below to fix this issue.
Step 1: Go to Formulas>>Calculation Options. From the drop-down list select Automatic. This will solve the issue you’re having
1.2 Cells Formatted as Text
Another reason for an Excel formula to not work is you might have unknowingly formatted the cells that contain the formulas as text. When set to text format, Excel neglects the applied formula and instead displays the plain result.
Step 1: See the picture below where I have entered a formula in C2 but it isn’t returning me a result and displaying the plain result. In this sort of cases first click on the cell and look at the Number group from Home tab.
Step 2: If it says Text then click on it and choose General.
Step 3: The problem is solved, now double click on the cell, containing the formula and click Enter to re-input.
1.3 Show Formulas Button Enabled Accidentally
If you knowingly or accidentally have enabled or turned the Show Formulas option on then the formula in your worksheet will not work. Follow the solution below to fix it.
Step 1: See the picture below, my formula is not working because the Show Formulas option is on here. To fix this go to Formulas and click on Show Formulas button if it’s grayed out like the picture below.
Step 2: Show Formulas disabled and the formula is working again.
1.4 Insertion of a Space or apostrophe before Equal Sign (=) in a formula
While working with a formula if you insert an additional space or an apostrophe before the equal sign (=), the formula will not work like the picture below.
Simply click on the cell and remove the space or apostrophe to solve this issue.
1.5 Forcing Excel to Re-Calculate
Look at the picture below, here I have used a formula in C2 and dragged the cell down so that the formula applies in C3 too. But the calculation here is incorrect and the C3 showing the result of C2 here also. This happened because I have turned Manual calculation option turned on from Formulas as you can see in the picture.
If you must have the Manual option turned on for some reason, you can force Excel to re-calculate a formula in a cell or in an entire worksheet with some simple shortcuts.
To re-calculate a single cell: Select the cell and press F2.
To re-calculate an active sheet: Press SHIRT+F9 or click on Calculate Sheet from Formulas tab.
To re-calculate a workbook: Press F9 or select Calculate Now from Formulas tab.
To re-calculate all sheets in all active workbooks: Press CTRL+ALT+F9.
1.6 Not Entering all the Required Arguments in an Excel Function
Every Excel function requires one or more arguments. Some functions may additionally take optional parameters, which are denoted in the syntax of the formula by [square brackets]. Not entering all the required arguments of a function is another reason for an Excel function to not work properly. If any of the needed parameters are missing from a formula, Excel returns an error text “You’ve entered too few arguments for this function” And if you input more arguments than allowed inside a function then you will get “You’ve entered too many arguments for this function” error message.
1.7 Enclosing Numbers with double quotations (“”)
Any value placed in double quotes in an Excel calculation is treated as a text string. Excel will consider number 1 as text if you use a formula like =IF(A3<0,”1″), thus you won’t be able to utilize the returned 1 in further calculations.
Following this simple rule will help you every time you write a formula using numerical values: Don’t double-quote numbers unless you want them to be seen as text.
1.8 Not Matching all Opening and Closing Parentheses in a Formula
This is one of the major reasons for the formulas to not work. A formula needs Parentheses wrapped around the values inside it specially if the formula is complicated and there are multiple arguments or conditions inside them. So, to specify the order of the calculations in complex formulas, you might need to use multiple sets of parentheses, one within the other. To solves this, make sure to pair the parentheses correctly when creating such a formula so that there is always a right parenthesis for every left parenthesis.
1.9 Not Enclosing Workbook and Worksheet Names in Single Quotes (‘ ‘)
Sometimes when entering the reference in a formula we accidentally or unknowingly don’t use the quote symbol properly to wrap the sheet or workbook name inside it. Either we enclose the names with double quotes or miss the left or the right quote which results in an error. Use single quotation marks (‘ ‘) to surround the names of worksheets or workbooks that include spaces or other non-alphabetic characters in their names when referencing them.
Reference to another sheet:
Reference to another workbook:
1.10 Include the Full path/Location to a Closed Workbook
The workbook name and full path to the workbook must be included in your external reference if you are developing a formula that refers to a closed Excel workbook.
1.11 Nesting too many Functions inside a Formula
Another reason an Excel formula is not working is nesting too many functions inside a single formula. There are many versions of Microsoft Excel, old and upgraded. All the versions don’t work completely the same as their capacity to work with multiple functions inside a formula varies. In Excel 2003 or lower versions, you can nest only 7 functions inside a formula while Excel 2007,2010,2013,2016 these versions can nest up to 64 functions inside a formula.
So, while nesting formulas keep in mind which version of Excel you are working on and how many functions can it nest.
Application of Excel Formula Not Working.
- Formula Displayed as Text: Verify that the cell is not formatted as text. Formulas entered into cells formatted as text will not execute but display as plain text. Change the format to ‘General’ or ‘Number’ and re-enter the formula.
- Incorrect Use of Quotes: Check for the incorrect use of quotation marks within formulas. Quotation marks are used to denote text strings. Incorrect placement can cause the formula to be treated as a text string or generate errors.
- Cell References Issues: Ensure that cell references are correct. Errors occur if the formula references incorrect cells due to row or column misalignments, especially after copying or moving formulas.
- Leading Space Before Equal Sign: A leading space before the equal sign (
=) will cause Excel to treat the formula as text. Make sure there is no space before the equal sign when you enter a formula. - Circular References: Identify and correct circular references where a formula refers back to its own cell, either directly or indirectly, which prevents it from calculating.
- Formula Calculation Set to Manual: Check if the workbook calculation option is set to ‘Manual’. If it is, Excel won’t automatically recalculate the formula when you change related data. Change this setting to ‘Automatic’ under Formula > Calculation Options.
For ready-to-use Dashboard Templates:
