Excel Won't Calculate My Function

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

	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.

Likely Reason:

This problem generally 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.

Solution:

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

1. Highlight the cell(s) containing the function
2. Right click with the mouse
3. Select the Format Cells ... option and ensure the Number tab is selected
4. Under the Category heading, select the option General and click OK
5. 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)

Click here for more information on Excel formatting

Case Number 2: The Formula Doesn't Update

You have a function that you know is correct, but when you change the values of its dependent cells the function continues to show the old value.

Likely Reason:

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 it is very easy for the user to forget about this option.

Solution:

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

In Excel 2010:

1. Select the File tab, and from this, click on Options
2. Select the option Formulas from the list on the left of the window that pops up
3. Under the Calculation options heading, select the option Automatic and click OK

In Excel 2007:

1. Select the option Excel Options from bottom of the main Excel menu (displayed by selecting the Excel Logo on the top left of the spreadsheet)
2. Select the option Formulas from the list on the left of the window that pops up
3. Under the Calculation options heading, select the option Automatic and click OK

In Excel 2003:

1. From the top of the Excel window, select the drop-down menu Tools and from within this, select Options...
2. Select the Calculation tab
3. Under the Calculation heading, select the option Automatic and click OK

This should set your Excel spreadsheet back to the setting in which it recalculates cells every time a change is made.

Return to the Excel Errors Page

Return to the ExcelFunctions.net Home Page