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.

Solution – Set Calculation Options to Automatic

STEPS:

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.

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

STEPS:

Click the File tab in the ribbon.
Click Options.

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

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.

Solution – Converting Text Values to Number Format

STEPS:

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.

Excel formulas now calculate automatically.

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

Reason 3 – Presence of Apostrophes

In our dataset there are trapped apostrophes in several cells.

Solution – Removing Apostrophes

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

STEPS:

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.

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

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

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.

Solution – Removing Leading or Trailing Spaces

STEPS:

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.

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.

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.

STEPS:

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

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.

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

Solution – Use Proper Arguments in Formulas

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

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.

STEPS:

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

The formulas now calculate correctly.

Note:

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.

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.

STEPS:

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

The errant formulas now calculate correctly.

Tip 3 – Turn Off Show Formulas Command

STEPS:

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

The errant formulas now calculate correctly.

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

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

➤ 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

Excel Formulas Not Calculating Automatically.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Lily Chua Feb 14, 2024 at 2:41 PM

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:

=’Jan”23′!F8+’Feb’23’!+’Mar”23′!+…………’Dec”23′!F8

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 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.
  Go to your workbook. Right-click on the sheet tab where you are performing your calculation and select View Code.
  
  Inside the code writing space, write the following code:
  Private Sub Worksheet_Change(ByVal Target As Range) Call ForcedReCalculation End Sub Sub ForcedReCalculation() Application.CalculateFull End Sub
  
  Save your workbook (as .xlsm format)
  
  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.
  
  Regards,
  
  Nafis
  
  ExcelDemy

Excel Formulas Not Calculating Automatically (6 Possible Solutions)

Reason 1 – Inappropriate Calculation Options

Solution – Set Calculation Options to Automatic

Reason 2 – Cell Value Is Formatted as Text

Solution – Converting Text Values to Number Format

Reason 3 – Presence of Apostrophes

Solution – Removing Apostrophes

Reason 4 – Leading or Trailing Spaces

Solution – Removing Leading or Trailing Spaces

Reason 5 – Wrong Cell Reference

Solution – Use Appropriate Cell References

Reason 6 – Numbers Enclosed in Double Quotes

Solution – Use Proper Arguments in Formulas

Tips to Fix Formulas That are Not Calculating Automatically in Excel

Tip 1 – Applying Proper Cell Formatting

Tip 2 – Uncheck Circular Reference Option

Tip 3 – Turn Off Show Formulas Command

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

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

Things to Remember

Frequently Asked Questions

Related Articles

Leave a reply Cancel reply