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:
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:
From the Data tab on the Excel ribbon, select the Text to Columns option.
This will bring up the 'Text to Columns' dialog box.You may have the display option, "Show formulas in cells instead of their calculated results" switched on.
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 Ctrl key, and while holding this down, press the ` (grave accent) key).
(Note that, on many keyboards the ` key is at the top left, 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:
This sets your Excel spreadsheet back to the Automatic setting so that it recalculates formulas every time a change is made to your spreadsheet.