Excel Won't Calculate My Function

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.


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 actual function text, instead of calculating the function's value.

Possible Reason 1:

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
or
  • 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.

Solution 1:

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:

  1. Use the mouse to select the cell(s) containing the formula (this must be done one column at a time).
  2. Excel Text To Columns Option Button

    From the Data tab on the Excel ribbon, select the Text to Columns option.

    This will bring up the 'Text to Columns' dialog box.
  3. Within the 'Text to Columns' dialog box, make sure the Delimited option is selected and click next.
  4. Make sure all the delimiter options are unselected and then click next again.
  5. You should now be offered a selection of Column Data Formats. Select General and click the Finish button.

Possible Reason 2:

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

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

Solution 2:

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.


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

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.

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.