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.
Excel file without formula is impossible. Excel spreadsheet is all about formulas. Formulas make the work easy for maintaining and carrying out complex calculation as well. But what if you encounter Excel formulas not working error message, this is very frustrating as you are not able to do anything on your Excel file.
To define it best take an example:
Suppose you have created the reports for your management meeting and just before printing copies for the executives, you discover that totals are showing the last month values. This is very frustrating as you don’t know how to fix it?
Not happy with your MS Excel performance?Don't Miss The BEST TOOL To FIX .xls/.xlsx Errors & Issues
When such instances occur several questions start running in mind that why the formulas not calculating? Why doesn’t this formula update its value by itself? This is very miserable and the user gets Panic what to do right now, to overcome the situation. Then what’s the issue to find out the reason read this tutorial as it explains the most common mistakes that happen when making
Here in this article, we are describing the complete information that explains the most common mistakes happen while making a formula in Excel and how to fix a formula that is not calculating or updating automatically or Microsoft Excel Formula not Working.
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 look like as a normal number but ms excel perceives them as text strings and doesn’t include it in the calculations.
Visual indicators of a text editor are as follows:
- Numbers formatted as the text is 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 of 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 than 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.
But if it still 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 displays the parentheses pair in different colors as you enter them in the formula. If your formula is missing some parentheses then Excel will display the error and allow you to correct by balancing the pair. and due to this users start getting Excel Formulas Not Working error message.
4. Enter all Required Arguments in an Excel Function
Each Excel function is having one or more required arguments. Some function is 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 the 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 us 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 a decimal symbol and the list separator is usually set to a semicolon; while in North America, a comma is default list separator.
For e.g: in North American, 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 unexpectedly start getting Excel Formulas Not Working error due to “We found a problem with this formula…” error, then simply 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.
Try the given tricks to solve the Microsoft Excel Formula not working and fix the error easily.
Automatic Solution: MS Excel Repair Tool
Apart from the manual solution sometimes the Excel file gets corrupted and start showing various errors, then, in this case, make use of the MS Excel Repair Tool. This is the best repair utility to fix all sorts of issues, corruption and even errors in the Excel file. with the help of this users can repair the Excel file easily and also restore the entire corrupted data including cell comments, charts, other data and worksheet properties.This is a professionally designed program that can easily repair.xls and .xlsx files and easy to use.
Steps to Utilize MS Excel Repair Tool:
In this article, I tried by best to provide ample information to solve the Excel Formulas Not Working error message. Make use of the given solution to fix the error and utilize the Excel file completely for maintaining crucial data.