How-To

How to Fix Excel Not Auto Calculating

By Adam Davidson

Published July 10, 2023

Are you having problems with Excel not auto calculating? Here are a few fixes that should get things working again.

Excel formulas can seem like magic. You’ll enter a formula and Excel calculates the result immediately. If you then change a value that the formula is referencing, the result of your formula will automatically change to reflect the new data.

At least, that’s what usually happens. If your Excel formulas aren’t recalculating as you expect, then there are a few possible causes.

Here are some options to try if you’re having problems with Excel not auto calculating.

Check if Excel Auto Calculation in Enabled

One of the most common causes of Excel not calculating is that the calculation options for your spreadsheet are set to manual. In this mode, cells will only calculate when you force them to do so. If Excel isn’t auto calculating, it’s likely that this mode has been turned on.

To enable auto calculation in Excel:

1. Open Excel and click the Formulas menu.
   
2. In the Calculation section, click Calculation Options.
   
3. Ensure that Automatic is checked.
   
4. You can also access the same options through the File menu.
   
5. Select Options at the very bottom of the left-hand menu.
   
6. Click Formulas.
   
7. Under Calculation Options ensure Workbook Calculation is set to Automatic.
   
8. If Calculation was already set to Automatic, and your formulas were still not auto calculating, you’ll need to try one of the fixes below.
9. Make sure that you set auto calculation to Automatic before you try the remainder of the fixes on this list, as the Manual calculation mode will stop them from working.

Check Cell Formatting

Formulas in Excel won’t always calculate if the cell that contains them is formatted as text. This may be the reason that your formulas aren’t auto calculating in Excel.

If you create a formula in a cell that isn’t formatted as text, and then convert it to text, Excel will still auto calculate the cell but leave the contents in text format. However, if a cell format is set to text before you enter your formula, the cell will just display the formula, and not the result.

You can easily check the format of any cell and change it to the correct format in order for auto calculation to work.

To check cell formatting in Excel:

1. Click the Home menu.
   
2. Select a cell that isn’t auto calculating.
   
3. In the Home ribbon, check the Number Format in the Number section.
   
4. If the Number Format is Text, click the arrow at the end of the Number Format box.
   
5. Select General.
   
6. Click on the formula in the Formula Bar.
   
7. Press Enter and your cell should now calculate.
8. The cell should recalculate as expected from now on.

Check Your Formulas

Another cause of formulas that won’t recalculate can be very easy to miss. If there is a space or an apostrophe before your formula, it won’t calculate as Excel treats the whole formula as text.

This can be very difficult to spot. An empty space isn’t very noticeable, and an apostrophe at the start of a formula doesn’t show in the cell itself. You can only see it in the Formula Bar.

To check your formula formatting in Excel:

1. Click in a cell that isn’t auto calculating.
   
2. Look in the Formula Bar to see if the cell starts with an apostrophe.
   
3. If there is one, click to the right of the apostrophe, and press Backspace to delete it.
   
4. If there is no apostrophe, the issue may be due to an empty space before your formula.
5. In the Formula Bar, click just to the left of the = (equals) sign.
   
6. Press Backspace a few times to be sure that you have removed any spaces before your formula.
7. Press Enter and your cell should now recalculate as expected.

Disable the Show Formulas Setting

If your cells are showing the formulas themselves rather than the results of those formulas, this may be because the show formulas setting is on. This setting allows you to see the formulas in cells rather than their results, which is useful when you’re creating your spreadsheet, or trying to find the cause of an error.

However, when this setting is turned on, it means your formulas won’t recalculate.

To disable the show formulas feature in Excel:

1. Click the Formulas menu.
   
2. In the ribbon, check the Show Formulas button. If the button has a darker background, the setting is on.
   
3. If this is the case, click the Show Formulas button to toggle the setting off.
   
   
4. Your cells should now auto calculate as expected.

Why Would I Use Manual Calculation Mode in Excel?

You may be wondering why manual calculation mode exists in Excel—surely you would want your formulas to automatically calculate? There may be times, however, when manual calculation mode can be useful.

If you’re working with large spreadsheets that contain a huge number of formulas, auto calculation of all these formulas can take a long time and slow your spreadsheet down. If you have a lot of changes to make, this can quickly become a bit of a pain.

By turning on manual calculation mode, you can make all the changes you want without having to wait for Excel to recalculate all your formulas. When you’ve finished making your changes, you can turn auto calculation back on, so that the cells only need to recalculate once.

How to Force Formulas to Calculate in Manual Calculation Mode

If you’re working in manual calculation mode, there are times when you will want your cells to calculate. Rather than having to switch to automatic calculation mode and then back again, you can force your formulas to recalculate with a simple click.

To force formulas to calculate in manual calculation mode:

1. Click the Formulas menu.
   
2. To force all formulas in the entire workbook to calculate, in the Calculation section, click Calculate Now.
   
3. You can also use the keyboard shortcut F9.
4. If you only want formulas in the current worksheet to calculate, click Calculate Sheet.
   
5. You can also use the keyboard shortcut Shift+F9.

Eradicate Excel Errors

Thanks to the steps above, you can quickly fix Excel if it isn’t auto calculating results from your formulas. With complex spreadsheets, it’s easy for things to go wrong, but there are plenty of other useful fixes for Excel problems.

You can learn how to fix the ‘retrieving data’ error in Excel. You might want to learn how to fix problems with formula references in your Excel spreadsheets. And if Excel keeps crashing, there are several fixes you can try to get things back on track.