ExcelFunctions.net

Search Site:

This page discusses what you can do when your function doesn't calculate correctly in Excel.

We consider the following two cases:

Case 1: The Cell Displays the Function Instead of its Calculated Value |

Case 2: The Formula Doesn't Update After Changes |

If none of these cases describe your problem and your Excel function is returning an error (rather than simply not calculating), you may find help on the Excel Formula Errors page.

A | B | |
---|---|---|

1 | TEXT1 | =LEFT( A1, 1 ) |

2 | TEXT2 | =LEFT( A2, 1 ) |

You type in a function and when you press Enter, the cell displays the actual function text, instead of calculating the function's value.

This problem often arises because the cells containing the formula are formatted as 'text' instead of the 'General' type.

This could have occurred because either:

- A text format has previously been applied to the cell

- You have insert a new column next to a column that is formatted as text, and the new column has 'inherited' the formatting of the adjacent column.

This formatting will cause Excel to interpret anything that is subsequently entered into the cell (including formulas) as text.

To correct this, you need to convert the cell's data type to the 'General' data type. This can be done using Excel's **Text To Columns** tool as follows:

- Use the mouse to select the cell(s) containing the formula (this must be done one column at a time).
From the

This will bring up the 'Text to Columns' dialog box.**Data**tab on the Excel ribbon, select the**Text to Columns**option.- Within the 'Text to Columns' dialog box, make sure the
option is selected and click__D__elimited**next**. - Make sure all the delimiter options are unselected and then click
**next**again. - You should now be offered a selection of Column Data Formats. Select
and click the__G__eneralbutton.__F__inish

You may have the display option, "Show formulas in cells instead of their calculated results" switched on.

CTRL-` to show/hide formulas

When activated, this option displays all formulas in the current Worksheet.

The option is located in the options panel, under 'Advanced' options. However, it is much faster to turn on/off this option using the keyboard shortcut **CTRL-`** as a toggle (i.e. press the control key, and while holding this down, press the ` (grave accent) key).

(Note that, on many keyboards the ` key is at the top left of your keyboard, next to the number 1).

Try pressing the keyboard shortcut **CTRL-`**, to see if this causes the formula to display its calculated value.

If this does not solve your problem, press **CTRL-`** again, to return the "Show formulas in cells instead of their calculated results" option to it's previous setting.

You have a formula that you know you have entered correctly, but when you change the formula, or change the values of its dependent cells the formula continues to show the old value.

This problem is usually caused by the setting of the **Calculation** option for your Excel spreadsheet.

By default, Excel will recalculate its functions every time a cell value changes. However, the **Calculation** option can be set to switch off automatic recalculation, and once switched off, it is very easy for the user to forget about this option.

To correct this you need to set the **Calculation** option to **Automatic**. To do this:

- Select the
**File**tab, and from this, click on**Options**to display the 'Excel Options' dialog box; - Select the option
**Formulas**from the list on the left of the dialog box; - Under the
**Calculation options**heading, select the optionand click__A__utomatic**OK**.

- Select the option
**Excel Op**from the main Excel menu (displayed by clicking on the Excel Logo on the top left of the spreadsheet). This will display the 'Excel Options' dialog box;__t__ions - Select the option
**Formulas**from the list on the left of the dialog box; - Under the
**Calculation options**heading, select the optionand click__A__utomatic**OK**.

- From the top of the Excel window, select the drop-down menu
and from within this, select__T__ools;__O__ptions... - Select the
**Calculation**tab; - Under the
**Calculation**heading, select the optionand click__A__utomatic**OK**.

This sets your Excel spreadsheet back to the Automatic setting so that it recalculates formulas every time a change is made to your spreadsheet.