Undoubtedly excel provides numbers of function to analyze, audit and calculate data. Some of them are frequently used by excel users while some of them are specific that only small group of financial engineering or statistical specialist understand and need them.
Can anyone imagine using excel spreadsheet without using formula? I think no one and hardly anything could compare to the frustration caused when all of a sudden excel formula stop working and starts displaying “excel formula not working”.
Just as for instance suppose you have created the reports for your management meeting and just before print copies for the executives, you discover that the totals are all showing the last month values. How to you fix it? When such instances occurs the several questions starts running your mind that why is your excel formula not calculating? Why doesn’t this formula update its value by itself? Don’t get panic most likely your excel worksheet is perfectly all right. Then what’s the issue to find out the reason read this tutorial as it explains the most common mistakes that happen when making formula in excel and how to fix a formula that is not calculating or updating automatically.
Suddenly Excel formula not working correctly, it returns an error or some wrong result. Excel formulas not working will arise problems like, formula returns an error or wrong result.
Solution To Fix Excel Formulas Not Working Error
1. Enter numbers without any formatting
While using the excel formula, don’t add any decimal separator or currency sign like $ or €. Remember in excel formulas , a simple comma is typically used to separate a function’s arguments and the dollar sign makes an absolute cell reference.
So instead of entering like this, $50,000 in your formula simply input the numeric value like this, 50000 and use the Format Cells dialog (Ctrl + 1) to format the output to your liking.
2. Make sure numbers are not formatted as text values
Another very common reason of excel formula not working is that number formatted as text values. It may looks like as a normal number but ms excel perceives them as text strings and don’t include it in the calculations.
Visual indicators of text editor are as follows:
- Numbers formatted as text are by default gets left –aligned while formal numbers are right aligned in cells.
- The number format box on the home tab in the number group displays the Text
- When many cells with text numbers are selected on the sheet, the Status Bar only shows Count; while usually it shows average, count and sum for numbers.
- There may be green triangle shape in the top left corner of the cell or leading apostrophe visible in the formula bar.
To fix this , just select all the problem having cells and click the warning sign and then select the Convert to Number option.
But in some cases, neither green triangles nor the warning signs appear in cells then in that case look at the number format box on the Home tab in the Number group. If it displays text, try cleaning all formatting for the problematic cells and set the cells format to number or general. If it won’t work then create a new column and manually input the data means copy your text to the notepad and then paste it on your excel sheet and delete the broken column.
3. Match all opening and closing parentheses in a formula
Generally arguments of excel functions are entered within the parentheses and in complex formulas; you may need to enter more than one set of parentheses. When creating such complex formula make sure to pair the parenthesis properly.
Well excel display the parentheses pair in different colors as you enter them in formula. If your formula is missing some parentheses then excel will display the error and allow you to correct by balancing the pair.
4. Enter all required arguments in an Excel function
Each excel function is having one or more required arguments. Some function are enclosed in [square brackets] in the formula’s syntax. So the formula must contain the required arguments, otherwise excel displays “You’ve entered too few arguments for this function” alert.
If the entered arguments are more than the required one than you will get “You’ve entered too many arguments for this function” error message.
5. Include the full path to a closed workbook
If you are using a formula that reference a closed excel workbook then your external reference must include the workbook name and entire path to the workbook. For example:
6. Do not nest more than 64 functions in a formula
If you are nesting two or more excel functions into each other for example suppose created nested IF formula, remember about the following limitations:
- In excel 2016/2013/2010/2007 you can use max 64 nested functions.
- While in Excel 2003 and lower, you can only use up to 7 nested functions.
7. Don’t enclose numbers in double quotes
In excel formula; the value encoded within the double quotes is interpreted as a text string.
That means, if you enter formula like=IF(A1>0, “1”), Excel will consider 1 as text and therefore you won’t be able to use the returned 1’s in other calculations. So remove the double quotes and always write formula for numeric value don’t enclose number in double quotes unless you want them to be treated as text.
8. Enclose workbook and worksheet names in single quotes
When referring to other worksheets or workbooks that have spaces or non-alphabetical characters in their names, enclose the names in single quotation marks.
For example Reference to another sheet:
Reference to another workbook:
=SUM(‘[2015 Sales.xlsx]Jan sales’!B2:B10)
9. Separate function arguments with a proper character
Most of you must separate the function arguments with the comma, however this doesn’t work on all excel workbook. The character you use to separate arguments actually depends on the List Separator sets in your Regional Settings.
In European countries comma is used as decimal symbol and the list separator is usually set to semicolon; while in north America comma is default list separator.
For e.g: in North American you would write =IF(A1>0, “OK”, “Not OK”), while European Excel users should put the same formula like this =IF(A1>0; “OK”; “Not OK”).
So if your excel formula are not working because of “We found a problem with this formula…”error, then go the Regional Settings (Control Panel > Region and Language > Additional Settings) and check out what character is actually set here as List Separator there and then use exactly that character to separate arguments in your excel formulas.
Hopefully, all these above mentioned tips will surely help you in solving off your excel formula not working issue.