Excel Formulas Not Calculating Automatically (6 Possible Solutions)

Here is a video overview of this article.

To demonstrate various reasons why Excel formulas may not be recalculating automatically or correctly, and solutions, we’ll use the following dataset containing a Sales statement for January, February, and March. Our dataset contains 5 columns: Product, Sales in January, February and March, and Total Sales.

Various Excel formulas are not calculating automatically in our dataset. Let’s fix the issues.

Notes: In this tutorial we have used Microsoft Office 365. The position of some commands may vary a little in different versions of Excel.

Reason 1 – Inappropriate Calculation Options

If the Calculation Options feature is set to Manual, Excel formulas will not calculate automatically.

2-Choose the Manual option

Solution – Set Calculation Options to Automatic


  • Click the Formulas tab in the ribbon.
  • Click the drop-down arrow under Calculation Options in the Calculation group.
  • Select Automatic.
  • Formulas now update automatically.

3-Select the Automatic feature

The same outcome can be accomplished using the Excel Options dialog box.


  • Click the File tab in the ribbon.
  • Click Options.

4-Selection of the Options feature

  • The Excel Options dialog box appears.
  • Click on the Formula tab.
  • Set the Workbook Calculation to Automatic.
  • Click OK.

5-Choose Automatic feature from Excel Options dialog box

Read More: [Solved]: Excel Formulas Not Updating Until Save

Reason 2 – Cell Value Is Formatted as Text

Another common reason why Excel formulas are not calculating automatically is unintentionally formatting cells that contain formulas as text.

6-Cell value is formatted as Text

Solution – Converting Text Values to Number Format


  • Select cell E6 and press Ctrl + 1. The Format Cells dialog box pops up.
  • Select the Number tab.
  • Select Number from the Category drop-down list.
  • Check the Use 1000 Separator(,) option.
  • Click OK.

7-Converting Text Value to Number Format

Excel formulas now calculate automatically.

8- Excel formula calculates automatically

Note: Either Number or General format may be used instead of Text.

9-Excel formulas not calculating automatically

Read More: [Fixed!] Formula Not Working in Excel and Showing as Text

Reason 3 – Presence of Apostrophes

In our dataset there are trapped apostrophes in several cells.

11-The presence of an apostrophe can result in a wrong calculation

Solution – Removing Apostrophes

Apostrophe signs or leading spaces will have to be removed one by one.


  • Select a cell where the apostrophe sign is trapped. Here, we begin with cell C8. Though the sign is not showing in the cell, its clear from observing the alignment of the data that the format is incorrect.

12-Presence of Apostrophe in cell C8

  • Click on the Formula Bar.
  • Click the Delete button to delete the apostrophe sign manually.
  • Press Enter.

13-Removing Apostrophe from cell

  • Use the same process to delete the other trapped apostrophes from the cells where they appear.

14-Removing all apostrophes symbol

Formulas now calculate automatically.

Reason 4 – Leading or Trailing Spaces

In our dataset, there are several leading or trailing spaces preventing Excel formulas from calculating automatically.

4.1-Excel formulas not calculating automatically due to leading or trailing spaces

Solution – Removing Leading or Trailing Spaces


  • Select a cell where the leading or trailing spaces are trapped.
  • Click on the Formula Bar.
  • Press Backspace to delete the leading or trailing spaces manually.
  • Press Enter.
  • Similarly, delete the other trapped leading or trailing spaces from all the cells that contain them.

4.2-Removing all of the leading or trailing spaces

Reason 5 – Wrong Cell Reference

A common mistake causing formulas not to calculate as expected is the incorrect use of absolute cell references.

For example:

  • Enter =B5 into a cell and AutoFill it down, will result in =B6 being copied down.
  • Enter =$B$5 into a cell and AutoFill it down, will result in =$B$5 being copied down.

17-Using wrong cell reference

In the above screenshot, formulas using absolute references are causing incorrect output in cells D17 and E17.

Solution – Use Appropriate Cell References

Simply remove the dollar symbol ($) from the culprit Excel formulas.


  • Select cell C17 and remove the dollar symbol ($) from the formula.
  • AutoFill the formula up to column E.

18-Removing dollar symbol from formula

Note – Binary to Decimal Conversion

Computers utilize binary, a system composed of a combination of 1s and 0s, to store numbers. However, our understanding of numbers is based on the decimal system, which operates on a base 10. Consequently, Excel needs to convert numbers between binary and decimal formats, sometimes resulting in unexpected disparities.

The maximum accuracy of Excel’s calculations is 15 significant digits. Therefore, as result of the conversion from binary to decimal, there could be very slight rounding errors. With some logical arguments, this can produce an unexpected result.

Reason 6 – Numbers Enclosed in Double Quotes

Numbers enclosed in double quotes leads to a mix-up between text and numeric values, causing formulas to stop calculating automatically. In the below image, the SUM function returns the wrong output for this reason.

6.1-The SUM function returns the wrong output

  • The error is occurring because of double quotes in the IF function, resulting in some numbers being treated as text.

6.2-The IF function enclosed number with double quotes

Solution – Use Proper Arguments in Formulas

Simply removing the double quotes from the IF function’s arguments results in the correct output being returned.

6.3-Removing double quotes from Excel formula

Read More: [Fixed!] SUM Formula Not Working in Excel

Tips to Fix Formulas That are Not Calculating Automatically in Excel

Tip 1 – Applying Proper Cell Formatting

Choosing the wrong cell formatting is another possible cause for Excel formulas not to calculate automatically. Selecting suitable cell formatting will resolve this error.


  • Select the range C6:E16.
  • Click the Home tab.
  • Choose a suitable format from the Number group, such as Currency for our cells.

10-Chose the Currency format for our cells

The formulas now calculate correctly.


When entering 12/10 into a cell, what do we want to return in that cell? Do we want the result of 12/10? Or do we want the result of 10th December? By default, Excel assumes that we desire the 10th of December of the current year.

The most frequent type of automatic conversion in Excel pertains to dates. In Excel, dates are represented as numbers, indicating the number of days since December 31, 1899. Our current year is 2023. If we enter 12/10, Excel thinks we want to get the 10th of December, and returns 45271 as the number of that date.

15.1-Automatic conversion of Dates to Number

Tip 2 – Uncheck Circular Reference Option

The activation of the circular reference option sometimes causes trouble for Excel formulas in evaluating values automatically. Turning off this feature resolves this issue.


  • Go to the Formula tab.
  • In the Formula Auditing group, click the drop-down arrow under Error Checking.
  • Turn off Circular Reference.

15-Unchecking Circular Reference option

The errant formulas now calculate correctly.

Tip 3 – Turn Off Show Formulas Command


  • Click the Formula tab.
  • From the Formula Auditing group, click on the Show Formula option to turn it off.

16-Turning off the Show Formula feature

The errant formulas now calculate correctly.

What Are the Reasons for Using Manual Calculation Mode in Excel?

  1. Control over Calculation: By default, Excel automatically recalculates all formulas whenever a change is made to a cell. Manual calculation mode gives you more control over when calculations occur.
  2. Performance Improvement: Manual calculation can improve the performance of Excel, especially when working with large and complex spreadsheets, where automatic recalculation after every change can slow down Excel’s responsiveness. By using manual calculation, you can make multiple changes to the spreadsheet and then recalculate all the formulas at once, saving processing power and improving efficiency.
  3. Error Detection and Troubleshooting: Manual calculation mode allows you to review and audit your formulas before performing calculations. This can help identify errors and inconsistencies in the spreadsheet. By manually triggering the recalculation, you can verify the accuracy of formulas, check for potential errors, and debug any issues that may arise.
  4. Data Validation: In certain cases, you may need to validate data before performing calculations. Manual calculation mode allows you to input and verify data without triggering automatic recalculations. This ensures that the data is accurate and complete before performing calculations, helping maintain data integrity.
  5. Avoiding Circular References: Circular references occur when a formula refers to the cell it is located in, creating an infinite loop of calculations. Manual calculation mode can help identify and resolve circular references by requiring you to manually recalculate the formulas. This makes it easier to identify and correct circular references, preventing calculation errors.
  6. Resource Management: Automatic calculation consumes computer resources, especially in large spreadsheets. By using manual calculation mode, you can conserve system resources, reduce CPU usage, and optimize the performance of your computer.

It’s important to note that when using manual calculation mode, you need to remember to manually recalculate the formulas to ensure accurate results.

How to Force Formulas to Recalculate in an Excel Workbook or Worksheet

In the event that you require the Calculation option to be set to Manual, there are two methods to compel the recalculation of formulas: utilizing the Calculate button on the ribbon, or employing one of the following shortcuts.

➤ To recalculate an active sheet:

  • Press Shift + F9, or
  • Go to the Formulas tab >> Select Calculate Sheet from the Calculation group.

➤ To recalculate the entire workbook:

  • Press F9, or
  • From the Formulas tab, select Calculate Now from the Calculation group.

19-Recalculate Excel sheet using keyboard shortcut

➤ To recalculate all sheets in all open workbooks, press Ctrl + Alt + F9.

➤ To recalculate a specific formula on a sheet:

  • Choose the cell containing the formula.
  • Enter Editing mode by pressing F2 or double-clicking the cell.
  • Press Enter to execute the recalculation.

Things to Remember

  • Press the “F9” key to manually recalculate the active worksheet.
  • Ensure that the cells containing the formulas are formatted as “General” or the appropriate format for the expected result. Improper formatting can cause incorrect or no calculations.
  • Identify and resolve any circular reference errors.
  • If your formulas include external references to other workbooks or files, ensure that the linked files are accessible and the formulas are correctly referencing them.

Frequently Asked Questions

1. What should I do if pressing F9 doesn’t recalculate the formulas?

Answer: If pressing F9 doesn’t trigger a recalculation, check if the formulas have external references to other files or workbooks. Ensure that these references are valid and the linked files are accessible. Also, check if any specific calculation dependencies or settings are preventing automatic recalculation.

2. How can I troubleshoot formulas not calculating in a specific range of cells?

Answer: If formulas are not calculating in a specific range, check if there are any conditional formatting rules or data validation settings that may be preventing calculations. Verify that the cells are formatted correctly and that there are no manual overrides inhibiting automatic calculation.

3. Can protected cells or worksheets affect formula calculation?

Answer: Yes, protected cells or worksheets can restrict formula calculation. Ensure that the necessary cells or worksheets are not locked or protected if they require automatic calculation.

Download Practice Workbook

Related Articles

<< Go Back To Formulas not Working in Excel | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

  1. Hi, Mr Soumit Dutta,

    Thanks for your advise on the error of Excel formula not calculating automatically. But my problem is the formula that had been used for more than 12 years, now was prompt error. The formula had been using under same workbook adding 12 worksheets as follow:


    Can you kindly advise me how to touch up such error. Every year end I need to ‘Sum up the total salaries of the year.

    Thanks you in advance.

    Best Regards

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 25, 2024 at 10:14 AM

      Hi Chua!

      It brings me great joy to know that you found our advice helpful. You are most welcome. As for troubleshooting your problem, it would be best if you could provide us with the Excel file. However, you could use the following steps to try to solve your problem on your own.

      1. Go to your workbook. Right-click on the sheet tab where you are performing your calculation and select View Code.
      2. Inside the code writing space, write the following code:
        Private Sub Worksheet_Change(ByVal Target As Range)
            Call ForcedReCalculation
        End Sub
        Sub ForcedReCalculation()
        End Sub
      3. Save your workbook (as .xlsm format)
      4. Rerun your calculation.

      If your problem is not yet solved, please join our ExcelDemy Forum and post this problem with your Excel file attached to it.




Leave a reply

Advanced Excel Exercises with Solutions PDF