You see the formula itself instead of the result it calculates, or you see a formula without an equal sign, your formula doesn't respond to certain changes, or it responds with unexpected results. If we are right, welcome to this guide on how to fix corrupted Excel formulas.
The fundamental problem we will discuss today isAutomatic calculationwhen disabled,show the guyActive, types as text, types with or without leading apostrophes and circular references.
Learn more about these Excel formula problems and how to remove them from your workbook, because what good is Excel if its formula performance is a problem?
Get out of the Excel toolbox and let's build it!
Content
Automatic calculation is disabled
The first culprit that could make the formula not work isAutomatic calculation options, especially when disabled. The meaning of this sentence ishandbookoption is enabled. Therefore, when the cell value specified in the formula changes, the formula is not updated.
closureAutomatic calculationIt can help speed up slow files. Excel calculation/recalculation of formulas can lag behind the workbook, especially if it contains many formulas, non-permanent functions in the workbook that are recalculated on each recalculation, links outside the table or workbook or datasheet.
In this case, the closure can be considered validautomaticcalculate. Read on to find out howautomaticIManual calculationoptional behavior.
What are automatic and manual calculation methods
In everyday Excel, when you change the value of a cell referenced in a formula, the formula is recalculated to include the newly entered value. Volatility functions are recalculated each time the workbook is recalculated and the workbook is opened. This is the default valuecalculation capabilitiesNowautomatic calculation capabilities.
together withManual calculation optionsSee Static behavior of types instead. In the example below, look at cell F18. before taxcalculate the totalMultiply the unit price in D18 by the unit quantity in E18 to get $87.50:
Let's say we've just been notified that the price of this product has increased from $8.75 to $9.00, but the price hasn't been entered into the system yet. When we entered a new unit price, the pre-tax total was not updated.
when we checkcalculation capabilities, we find that is set tohandbook.
from the insidehandbookcondition, the formula will not be recalculated unless you recalculate the formula cell itself. Volatile functions are also not updated by opening the file or recalculating in the workbook.
So there will be three ways to recalculate formula cells.
- Editing the cell will recalculate the formula.
- Other calculation options in useofficiallyTo underline:
calculator(Shift + F9) will recalculate the active sheet whiledo the math nowApplies to the entire workbook. To recalculate all open workbooks, pressCtrl + Alt + F9key.
- these manual methods or you can changecalculation capabilitiesback toautomatic. We may have given a solution to this problem, but that is all.
How to enable automatic calculation
this is a correctionManual calculation optionsexists. chooseofficiallytab, thereforecalculation capabilitiesicon fromcalculateclub. clickautomaticselection.
Signs ofhandbookreachautomaticexistscalculation capabilitiesand any stuck formulas will be recalculated immediatelyautomaticoption is selected. Closing or reopening a workbook does not change this setting, it also applies to all other open workbooks and subsequent open workbooks after the changecalculation capabilities.
If you want, you can changeautomaticWhen you have finished working in the workbook, selecthandbookselection.
Show type selection enabled
The pillars are wide open,number formatPeeled off, the formula is exposed, gobbling up the formula output. What happens? If the assumption is correctshow the guyAs a parasite, expanding the width of the columns allows the displayed types to be displayed.
Here is the ideal life for our paper:
Now you have this:
This is only half the table.
In this situation, neither the old nor the new formulas in the spreadsheet will calculate. the formulas will just appear as text. That's the whole pointshow the guyfeature; when on, makes any text format. Thisnumber formatwill disappear; Dates will become sequential numbers, numbers will be aligned from right to left, and formulas will appear as text instead of calculated values.
The selected formula cell will also highlight the referenced cell in the cell edit mode (note the before and after difference in the screenshot above).
In some cases, it is necessary to use this function because it is much easier to see all the formulas than to go to each cell of interest and read the formula from the table.Formula bar. But anyway, you probably no longer have access to the formula or want nothing to do with this disclosure.
In any case, below you will see how to turnshow the guyoption disabled.
to goofficially> tabFormula overviewthe group checks whethershow the guyHighlighted in dark gray to indicate the feature is enabled:
clickshow the guypower off button.
The worksheet will revert to its original form without substantially changing the values or formatting:
you can workCtrl + `(highlight) key as keyboard shortcut for toggleshow the guyOpen and closed.
notes:
show the guyIt only applies to the active sheet.
show the guyApplies to all worksheet types. So if all the types together are not giving you problems, your problems may be explained in the next section.
Suggested Reading: Excel displays formulas instead of results
The formula was entered as text
If other cells of the formula appear and work correctly, but one (or several) cells do not, it is possible that the formula was entered as text. There can be many reasons for this.
The range in the worksheet can be configured as standardtextThat way, when a formula is entered, it appears as plain text instead of returning the result of the formula. Or the format may have been set by someone who forwarded the file to you. If it was you, you'd know why you did it.
What this question will look like on the worksheet:
H20 is not in cell editing mode, just select the cell to display the formula. in highlightednumber format line, which is apparently formatted astextreplaceGenerallyor any other relatednumber format.
Make it now. select stextForm. click the right arrownumber format lineand selectGenerallyor any other formatting you want to apply to the cells.
Learn morenumber format,chooseMultiple number formatsOptions at the bottom of the menu. You will be directed toFormat the cellsDialog box for applying and editing additional formatting.
After choosing the format (we chosecoinform ofnumber format line), you must recalculate the formula cell to accept the formatting change and return the formula result.
Now we have the output of the formula in the applicationnumber format.
Circular references to formulas
It does not produce the expected results if the formula containscircular reference. In Excel, a circular reference occurs when a formula contains a direct or indirect reference to the cell in which it is placed.
Excel doesn't leave much to the imagination when you first use it. when you guessA nurseWhen you type a key in a formula that contains circular references, Excel will generate a pop-up warning that this is a good time to change the position of the formula cell or edit the active formula cell.
A small example of a direct circular reference and the warning message:
This warning only appears the first time a circular reference is inserted into a workbook.
When calculating the sum of the after-tax numbers in column H, we accidentally extended the cell references in the formula to H3:H20 instead of H3:H18. This way, the formula includes the formula cell itself and triggers the circular reference warning.
An indirect circular reference occurs when the target cell contains a formula that refers to a cell that references back to the target cell. Lifecycle for Excel.
When you disable error messages, formulas with circular references end in 0 (or in some cases the last calculated value):
Although this is the sum of the columns and you know that the final value cannot be 0, for the second formula you can assume 0 as the answer, which affects the display of the data set. You can check circular referencesofficially> tabFormula overviewgroup >error checkingmenu >circular referenceselection. The function verifies that there is indeed a circular reference to cell H20. Click on the mentioned cell reference and the function will select the problem cell in the worksheet.
Displays only one circular reference at a time in a submenu. the same as shown inStatusna trakaaliStatusna trakaThe circular reference cells shown are not selected. Use this feature to find all circular cell references and remove them by editing formulas, repositioning cells, or deleting them.
Even or odd apostrophes are missing from the formulas
If a formula is missing an equals sign at the beginning or has an apostrophe at the beginning, the formula will not be evaluated. Both happened for one reason: while the rest of the formula looked good, it wasn't formula anymore.
The operator that makes types real is the equals sign. You can use the rest of the A-OK syntax, but without the equal sign, Excel doesn't get the signal that a formula is being used and returns the value as a string.
No equals, no calculations. Add an equal sign to the formula to perform the calculation. Copy the formula if needed.
Second, you can include an equals sign, but the calculation won't help if the formula is preceded by an apostrophe:
Now we get the equal sign in play, but we still don't get the result of the formula because it starts with an apostrophe. This will display the cell value as text without showing the apostrophe in the cell itself. can be seen in the cell valueFormula bar, which can be deduced from the above. The effect is similar to other signs in that there should be no signs before the equal sign if you want the formula to perform the calculation.
This may be intentional to show the formula, but remove the apostrophe if not intended or when you want to calculate the formula.
General tips:Check that the type itself is correct in terms of syntax, parameters, and operator usage. However, in most cases an error or bad formula message may be triggered indicating that you need to make a correction.
Hopefully we've put our troubleshooting gun to good use and finally solved your prescription woes. Now you know that a type problem can be a problem within the type or it can be an external problem with other functions. Put our problem solvers back in their cases and we'll get them ready for the next round of shooting!