Excel Won't Calculate My Function

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.


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

 AB
1TEXT1=LEFT( A1, 1 )
2TEXT2=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.

Possible Reason 1:

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

This frequently arises 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 1:

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

The quickest way to change the formatting in Excel is to select the cell(s) to be formatted and then select the required cell formatting from the drop-down menu in the 'Number' group on the Home tab of the Excel ribbon (see below):

Excel Format Cells Drop Down Menu on Ribbon

Once the cell formatting has been changed, force the cell to recalculate. This can be done by double-clicking on each of the cells containing the formula. 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 to force this cell to recalculate, and then copy this cell into the remaining cells.

Possible Reason 2:

Key to show formulas
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.

Solution 2:

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).


Case Number 2: The Formula Doesn't Update

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.

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 once switched off, 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 and later:

  1. Select the File tab, and from this, click on Options to display the 'Excel Options' dialog box;
  2. Select the option Formulas from the list on the left of the dialog box;
  3. Under the Calculation options heading, select the option Automatic and click OK.

In Excel 2007:

  1. Select the option Excel Options 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;
  2. Select the option Formulas from the list on the left of the dialog box;
  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 sets your Excel spreadsheet back to the Automatic setting so that it recalculates formulas every time a change is made to your spreadsheet.