Unable to make use of Excel formula because all of a sudden it stopped working, updating or calculating your data figures? Desperately you are looking for the fixes so that your Excel formula starts working again?
If your answer is yes then DON’T WORRY…! because you are at the right place.
This post will give you the best fixes to troubleshoot Excel Formulas Not Working, Excel formulas not updating or Excel formulas not calculating like issues.
I ensure you all that after reading the complete post you can proficiently fix Excel Formulas Not working issue on your own.
So, let’s dive into it….!
To recover Excel sheet data, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
About Excel Formulas Error
Undoubtedly Excel provides a number of functions to analyze, audit, and calculate data. Some of these functions and formulas are frequently used by Excel users while some of them are specifically used by a small group of financial engineering or statistical specialist.
Excel file without a formula is impossible. Excel spreadsheet is all about formulas. Formulas make the work easy for maintaining and carrying out complex calculations as well. But what if you encounter Excel formulas not working issue? Well, it’s very frustrating as you are not able to do anything on your Excel file.
Let’s take an example for clear understanding:
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?
When such instances occur several questions start running in mind that why my Excel formulas not calculating automatically? OR why don’t Excel formulas not updating automatically?
As it’s a very annoying situation so it’s obvious for any user to get Panic. But you need to be calm so that you can find out the actual reason for getting Excel formulas not working issue. To help you in this, my tutorial will explain the most common mistakes that you are doing while making formulas for Excel.
Types Of Excel Formula Errors:
Mostly it is seen that Excel users get stuck in three of these situations when their Excel formula starts troubling them.
- Excel formulas not working–
meanwhile this your Excel formula starts showing the wrong result or error.
- Excel formulas not updating–
It will display the old value even after you have already updated the dependent cells.
- Excel formulas not calculating–
In this case, the cell will only display the formula not give any result of it.
Here in this article, we are describing the complete information that explains the most common mistakes that happen while using a formula in Excel. So, that any Excel user can overcome Excel formula not calculating, Excel formula not updating automatically or Excel Formula not Working like issues easily and effortlessly.
How To Fix Excel Formulas Not Working Error?
The occurrence of Excel formula not working issue returns the wrong results or sometimes errors too. So, in this section, you will get an idea of how to fix silly mistakes usually done at the time of creating Excel formulas.
1. Enter Numbers Without Any Formatting
While using the Excel formula, don’t add a currency sign eg: $ or € or a decimal separator.
Note: in the Excel formulas, a sign of comma is generally used for separating the function of arguments. Whereas, the sign of dollar is for making absolute cell reference.
So, you can put a numeric value like this, 50000 instead of putting data like this $50,000. All in all, I just want to say in your Excel formula simply use the numeric value.
2. Numbers shouldn’t be formatted as text values
Another very common reason for the Excel formula not working is that numbers formatted as text values. It may look like as a normal number but MS Excel considers them as a text string and doesn’t include it in the calculations.
Visual indicators of a text editor are like this:
- Numbers that are formatted as “text” will by default get left-aligned while formal numbers are aligned right in the cells.
- You will see that on the Home tab> number group, the number format box is selected with Text option.
- When many cells having text numbers have been selected from the worksheet you will see that the Status Bar will only show the Count. whereas generally, it displays the count, sum, and average of numbers.
- You will see a green triangle shape in the cell’s top-left corner or a leading apostrophe visible in the formula bar.
To fix this, just select all the problems having cells and then tap on the yellow warning sign. After that select, the option Convert to Number.
But in some cases, nor the green triangles neither the warning signs seem in the cells. Then in that case go to the Home tab > Number group >number format box. if this shows the text, then clear all the formatting of the cells having the issue. after then set the format of the cell either general or number.
But if it still won’t work then make a new column and enter the data manually. Means just copy down your text to the notepad after then paste it on your Excel sheet. At the end delete off all broken columns.
3. Match All Opening And Closing Parentheses In A Formula
Generally, arguments of the Excel functions are kept under parentheses and in complex formulas; you may require to put more than a single set of the parentheses. When making such complex formula make sure that you have opened and closed the parenthesis properly.
Well, Excel shows parentheses pair in some different colors when you use it in the formula. Suppose your formula is lacking with some parentheses then Excel will display the error and allow you to correct it by balancing the pair. and due to this users start getting Excel Formulas Not Working error message.
4. Put All The Required Arguments Within The Excel Function
Each Excel function is having single or multiple required arguments. If you are entering optional arguments then enclosed it in the [square brackets] in your formula’s syntax. So, the formula must have all the necessary arguments. Or else your Excel will display the following “You’ve entered too few arguments for this function” warning message.
If the entered arguments are more than the required one then you will encounter with following “You’ve entered too many arguments for this function” warning message.
5. Include The Full Path To A Closed Workbook
If you are using a formula that references a closed Excel workbook then your external reference must include the workbook name and entire path to the workbook. For example:
6. Don’t Nest More Than 64 Functions Within A Formula
If you are nesting more than two Excel functions with each other for example suppose to create nested IF formula. Then follow these limitations:
- In Excel 2016/2013/2010/2007 you can make use of max 64 nested functions.
- While in Excel 2003 and lower, you can only make use of a maximum of 7 nested functions.
7. Don’t Enclose Numbers In Double Quotes
In the Excel formula; the value encoded within the double quotes counted as a text string.
That means, if you enter Excel formula like=IF(A1>0, “1”), then Excel will consider 1 as a text, and thus you won’t be able to make use of the returned 1’s in any other calculations. So, don’t put the double quotes and always write the formula for numeric value don’t enclose numbers in double-quotes until and unless you want it to count 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. Use Right Charcter For Separating The Function Argument
Most of you separate the Excel function arguments with the comma, however, this doesn’t work on all Excel workbook. The character which you generally use to divide arguments moreover depends on the List Separator which is sets with the Regional Settings.
European countries use for assigning the decimal symbol. Whereas, for the list separator semicolon; is used.
For e.g: North American excel user, would write =IF(A1>0, “OK”, “Not OK”), whereas European Excel users will put the exact same formula like this =IF(A1>0; “OK”; “Not OK”).
If you suddenly start getting Excel Formulas Not Working issue because of the “We found a problem with this formula…” error. in that case just go to your Regional Settings which is present in:
(Control Panel > Region and Language > Additional Settings)
After then make a check the character which is set for List Separator over there. make use of the same character for dividing the arguments in the formula of Excel.
Try the given tricks to solve the Microsoft Excel Formula not working and fix the error easily.
How To Fix Excel Formulas Not Updating Automatically?
When you are noticing that the Excel formula is not updating automatically the reason can be your Excel calculation setting is somehow got changed. Generally, it is found that when the calculation setting gets changed from automatic to manual. At that time Excel formula is not updating automatically like issues is been faced.
Here is how to change the calculation setting to fix Excel Formula Is Not Updating issue.
- Go to the Excel ribbon, and then on the Formulas.
- From this formula tab choose the Calculation group.
- tap to arrow key present next to Calculation Options. From the drop-down choose the Automatic option.
Or else, you can modify the calculation setting through these steps:
- For Excel 2019, Excel 2016, Excel 2013, and Excel 2010 user:
Open Excel worksheet < Formulas < Calculation options group. After then choose the Automatic option present within Workbook Calculation.
- For Excel 2007 user:
Tap to the Office button < Excel options < Formulas < Workbook Calculation < Automatic.
- For Excel 2003 user:
From your Excel ribbon choose the Tools < Options< Calculation< Calculation< Automatic.
How To Fix Excel Formulas Not Calculating Automatically?
If you are struggling with Excel Formulas Not Calculating issue then it’s because your cell is showing the Excel function in place of the evaluated value.
Behind this Excel formulas not calculating automatically error following are the reasons. So, check out the reasons along with its fixes to troubleshoot Excel formulas not calculating issues.
1. Show Formulas Mode Is Left On
One very common reason behind Excel formula not calculating error is that by mistakenly show formula mode has been activated in the worksheet.
So, to fix this Excel formula not calculating issue it’s compulsory to turn off Show Formulas mode.
Here is the step to be followed:
- Go to the Excel Formulas tab and from the Formula Auditing group tap to the Show Formulas
2. A Formula Is Entered As Text
The second reason behind the occurrence of Excel formula not calculating error is that Excel formulas is been formatted like text. For checking this out, follow these steps:
- From the Excel ribbon go to the home tab and then choose the Number group.
- Now choose the formula cell after then have a look over the Number Format box.
- If it’s showing text in the number group then change it to General and press F2 button from the keyword, being in the cell.
- Now enter any Excel formula so that it will recalculate the data again and show the calculated data.
Steps To Recalculate Excel data
If due to any reason, you want to set the Calculation option to Manual. In excel you have the option to force the Excel formulas to recalculate the data again. For this, you need to click on the calculate button.
So, check this out how to force the Excel formulas to recalculate:
For Recalculating Entire Workbook Data:
- From the Formulas tab choose the Calculation group.
- From the opened Calculation group tap to Calculate Now.
For Recalculating Any Specific Active Sheet:
- From the Formulas tab choose the Calculation group.
- From the opened Calculation group tap to Calculate Sheet.
If you to perform this recalculation task in all sheets of your opened Excel workbooks. Then for this just press the Ctrl + Alt + F9 button simultaneously.
In case you want to recalculate one single formula on the sheet then choose the formula cell. After then get into the editing mode by double-tapping the cell or by hitting the F2 button. At last hit Enter key from your keyboard.
Automatic Solution: MS Excel Repair Tool
Apart from the manual solution sometimes the Excel file gets corrupted and starts 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 these 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 my best to provide ample information to solve the Excel Formulas Not Working, calculating or updating error message. Make use of the given solution to fix the error and utilize the Excel file completely for maintaining crucial data.