Excel Formulas Not Calculating? How to Fix it Fast

You’ve created the reports for your management meeting, and, just before you print copies for the executives, you discover that the totals are all showing last month’s values. How do you fix it—fast?

1. Check for Automatic Recalculation

On the Formulas ribbon, look to the far right and click Calculation Options. On the dropdown list, verify that Automatic is selected.

When this option is set to automatic, Excel recalculates the spreadsheet’s formulas whenever you change a cell value. This means that, if you have a formula that totals up your sales and you change one of the sales, Excel updates the total to show the correct sum.

When this option is set to manual, Excel recalculates only when you click the Calculate Now or Calculate Sheet button. If you prefer keyboard shortcuts, you can recalculate by pressing the F9 key. Manual recalculation is useful when you have a large spreadsheet that takes several minutes to recalculate. Instead of waiting impatiently while it recalculates after every change you make, you can set the recalculation to manual, make all of your changes, and then recalculate at once.

Unfortunately, if you set it to manual and forget about it, your formulas will not recalculate.

2. Check the Cell Format for Text

Select the cell that is not recalculating and, on the Home ribbon, check the number format. If the format shows Text, change it to Number. When a cell is formatted as Text, Excel makes no attempt to interpret the contents as a formula.

After you change the format, you’ll need to reconfirm the formula by clicking in the Formula Bar and then pressing the Enter key.

Note: If you format a cell as General and you discover that Excel is changing it automatically to text, try setting it to Number. When a cell formatted as General and the cell contains a reference to another cell, Excel copies the format of the referenced cell. Choosing any format other than General will prevent Excel from changing the format.

3. Check for Circular References

Look at the bottom of the Excel window for the words CIRCULAR REFERENCES.

Like circular logic, a circular reference is a formula that either includes itself in its calculation or refers to another cell which depends on itself. Be aware that a circular reference can, in some instances, prevent Excel from calculating a formula. Correct the circular reference and recalculate your spreadsheet.

Next Steps

You can fix most recalculation problems with one of these three solutions. Now, fix that report, and get ready for your meeting.

Or continue your Excel education here.