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

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

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

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

#### 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 **1**s and **0**s, 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.

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

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

**Related Articles**

- How to Refresh Formulas in Excel
- [Fixed!]: Excel Formula Not Showing Correct Result
- [Fixed!] Excel Formulas Not Working on Another Computer
- [Solved:] Excel Formula Not Working unless Double Click Cell
- [Solved]: Excel Array Formula Not Showing Result

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

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

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.

If your problem is not yet solved, please join our

ExcelDemy Forumand post this problem with your Excel file attached to it.Regards,

NafisExcelDemy