Formulas in Excel are used to perform automated mathematic operations on data. Text starting with “=” is assumed to be a formula by excel.
The problem arises when Excel formulas fail to do the proper calculation. It can be a nightmare for people whose business depends upon these calculations.
In this blog post, we have gathered possible reasons and solutions to fix the problem of Excel formulas.
Why do Excel
formulas fail to show results?
If your Excel
formula is not showing results, there could be a few possible reasons such
- You accidentally enabled “Show Formulas” in excel.
- Excel thinks your formula is text (Syntax are not followed)
Problem Scenarios & Solutions
Often, the reasons behind excel formula corruption come under the categories mentioned above. Here are the different scenarios and recovery methods:
Show Formulas is enabled
If your Excel formulas are corrupt, check if you have accidentally or intentionally enabled ‘show formula’ feature of Excel. This feature, instead of applying calculations and then showing results, displays the actual text written by you. It can be used to view all formulas quickly, but if you are not aware of this feature, and enabled it accidentally, it can be a headache. To disable this mode, Go to ‘Formulas’ and click on ‘Show formula enabled.’ If it’s previously enabled, it will be disabled by just clicking on it.
Cell format set to Text
There could be a case when you’ve avoided all scenarios of error, but still, your required result is not displayed. A possible reason could be that the cell format is set to text. This means that anything written in any format in that cell will be treated as regular text. If so, change the format to General or any other. To get excel to recognize the change in the format, you may need to enter cell edit mode by clicking into the formula bar or just press F2.
Automatic Calculations feature
There is an “automatic calculation” feature in Excel, which tells Excel to do calculations automatically or manually. If Excel formula is not showing results, it may be because the automatic calculations feature is set to manual. This issue is not easily detected because it results in calculating formula in one cell but if you copy it to some other cell, it will retain the first calculation and will not recalculate on the base of the new location.
To fix this, first, click on ‘File’ written on the top left corner of the screen, then Click on ‘Options’ from the left menu bar. As a result, a pop-up will appear on the screen, Select ‘Formulas’ from the left side menu and then change the ‘Calculation options’ to ‘Automatic’ if it’s currently set as ‘Manual,’ and click OK. This will redirect you to your sheet and start performing calculations manually.
Excel considers your formula as text
There is a proper way to tell Excel that your text is a formula. If you don’t write the formula in a particular format, Excel considers it as simple text and hence no calculations are performed according to it. The following could be the reasons for Excel formula corruption in such cases:
- No equal sign: Every formula in excel should start with an equal sign (=). If you miss it, excel will mistake your formula as regular text
- Space before equal sign: You are not supposed to enter a space before equal sign. Maybe a single space will be hard for us to detect, but for Excel, it breaks the rule of writing formulas.
- Formula wrapped in quotes: You need to make sure that your formula is not wrapped in quotes. People usually make this mistake of writing a formula in quotes, but in Excel, quotes are used to signify text. So your formula won’t be evaluated. But you can add quotes inside formula if required for example: =SUMIFS(F5:F9,G5:G9,”>30″)
- Match all parentheses in a formula: Arguments of Excel functions are entered in parenthesis. In complex cases, you may require to enter more sets of parenthesis. If those parentheses are not paired/closed properly, Excel may not be able to evaluate the entered formula.
- Nesting limit: If you are nesting two or more Excel functions into each other, for example using nested IF loop, remember the following rules:
- Excel 2016, Excel 2013, Excel 2010, and Excel 2007 only allow to use up to 64 nested functions.
- In Excel 2003 and lower, only allows up to 7 nested functions
Enter numbers without any formatting: When you use a number in the formula, make sure you don’t enter any decimal separator or currency sign, e.g. $, etc. In an Excel formula, a comma is used to separate arguments of a function, and a dollar sign makes an absolute cell reference. Most of these special characters have built-in functions so avoid using them unnecessarily
What to do if the manual solutions don’t work?
If you’ve tried out the manual solutions mentioned above but still can’t get Excel to show the results of formulas, you can try repairing your Excel file with the help of automated Excel recovery software, Stellar Repair for Excel. This reliable and competent software detects faults within Excel files and repairs them to make such files work the way they’re supposed to work. With this product in tow, you can bid adieu to Excel troubles for good. Armed with an interactive GUI, this software is extremely easy to work with, and its advanced algorithms allow it to fend off Excel errors with ease.
Formulas serve as a key utility of Excel, which is extensively used. So, any malfunction in formulas can result in faulty decision making especially those decisions that are based on the calculation and its derivatives!
In this blog post, we presented the
plausible reasons for the specific case wherein an Excel formula doesn’t show results,
and also the methods you can try out for fixing this problem. These fixes are
based on aspects such as using correct cell formatting, formula syntax, settings,
We also covered the possibility of Excel file corruption being the reason for this, which obviously can’t be fixed by making changes in the implementation or formatting part. For such potential cases of file corruption, Stellar Excel Repair tool might help.