ExcelFunctions.net

Search Site:

This page discusses what you can do when your function wont calculate in Excel.

We discuss the following two cases:

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

Case 2: The Formula Doesn't Update |

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 function as you typed it, instead of calculating the function's value.

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

This frequently occurs when you insert a new column, next to a column that is formatted as text. In this case, the new column 'inherits' the formatting of the adjacent column.

To correct this, you need to change the formatting of the cell and then force the cell to recalculate:

- Highlight the cell(s) containing the function
- Right click with the mouse
- Select the
option and ensure the__F__ormat Cells ...**Number**tab is selected - Under the
**Category**heading, select the option**General**and click**OK** - In your spreadsheet, use the mouse to double-click on each of the cells containing the formula (this forces Excel to recalculate these cells)
*(Note: if you have a lot of cells containing the same formula in a column, rather than double-clicking on each cell to force a recalculation, it may be quicker to double click on your first cell and copy this cell into the remaining cells)*

CTRL-` to show/hide formulas

The key combination **CTRL-`** (i.e. press the control key, and while this is depressed, press the ` key) displays __all__ the formulas in a spreadsheet.

Therefore, if this key combination has been pressed you will see the formulas, rather than their results.

Return to the normal view (showing the results, rather than the formulas) by repeating the same key combination (i.e. press CTRL-`)

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

You have a function that you know you have entered correctly, but when you change the values of its dependent cells the function 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** - Select the option
**Formulas**from the list on the left of the window that pops up - Under the
**Calculation options**heading, select the optionand click__A__utomatic**OK**

- Select the option
**Excel Op**from bottom of the main Excel menu (displayed by selecting the Excel Logo on the top left of the spreadsheet)__t__ions - Select the option
**Formulas**from the list on the left of the window that pops up - 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.