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 is*Automatic calculation*when disabled,*show the guy*Active, 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 is*Automatic calculation options*, especially when disabled. The meaning of this sentence is*handbook*option is enabled. Therefore, when the cell value specified in the formula changes, the formula is not updated.

closure*Automatic calculation*It 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 valid*automatic*calculate. Read on to find out how*automatic*I*Manual calculation*optional 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 value*calculation capabilities*Now*automatic* *calculation capabilities*.

together with*Manual calculation options*See 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 check*calculation capabilities*, we find that is set to*handbook*.

from the inside*handbook*condition, 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 use
*officially*To underline:

*calculator*(**Shift + F9**) will recalculate the active sheet while*do the math now*Applies to the entire workbook. To recalculate all open workbooks, press**Ctrl + Alt + F9**key.

- these manual methods or you can change
*calculation capabilities*back to*automatic*. We may have given a solution to this problem, but that is all.

### How to enable automatic calculation

this is a correction*Manual calculation options*exists. choose*officially*tab, therefore*calculation capabilities*icon from*calculate*club. click*automatic*selection.

Signs of*handbook*reach*automatic*exists*calculation capabilities*and any stuck formulas will be recalculated immediately*automatic*option 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 change*calculation capabilities*.

If you want, you can change*automatic*When you have finished working in the workbook, select*handbook*selection.

## Show type selection enabled

The pillars are wide open,*number format*Peeled off, the formula is exposed, gobbling up the formula output. What happens? If the assumption is correct*show the guy*As 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 point*show the guy*feature; when on, makes any text format. This*number format*will 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 turn*show the guy*option disabled.

to go*officially*> tab*Formula overview*the group checks whether*show the guy*Highlighted in dark gray to indicate the feature is enabled:

click*show the guy*power off button.

The worksheet will revert to its original form without substantially changing the values or formatting:

you can work**Ctrl + `**(highlight) key as keyboard shortcut for toggle*show the guy*Open and closed.

**notes:**

*show the guy*It only applies to the active sheet.

*show the guy*Applies 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 standard*text*That 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 highlighted*number format line*, which is apparently formatted as*text*replace*Generally*or any other related*number format*.

Make it now. select s*text*Form. click the right arrow*number format line*and select*Generally*or any other formatting you want to apply to the cells.

Learn more*number format*,choose*Multiple number formats*Options at the bottom of the menu. You will be directed to*Format the cells*Dialog box for applying and editing additional formatting.

After choosing the format (we chose*coin*form of*number 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 application*number 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 guess**A nurse**When 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 references*officially*> tab*Formula overview*group >*error checking*menu >*circular reference*selection. 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 in*Statusna traka*ali*Statusna traka*The 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 value*Formula 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!