Microsoft Excel is a widely used application for doing automatic calculations. But, sometimes, Excel formulas are not calculating automatically. In this article, we are going to demonstrate to you **6** possible solutions to the issue of formulas that are not calculating automatically in Excel. If you are also curious about it, download our practice workbook and follow us.

Here is the overview of today’s article.

## Excel Formulas Not Calculating Automatically: 6 Possible Solutions

We’ve selected a dataset that represents the sales statement of January, February, and March. Our dataset contains **5 columns:** **Product, Sales in January, February and March, and Total Sales. **Excel formulas not calculating automatically our dataset. We will fix this problem using this dataset.

**Notes:** All the operations of this article are accomplished by using the **Microsoft Office 365** application. So, the position of some commands may vary a little.

### 1. Modifying Calculation Options

While the **Calculation Options** feature is **Manual**, if you change any data then the Excel formulas are not calculating automatically.

#### Solution: Set Calculation Options as Automatic

You can solve this issue by setting the **Manual **option to the **Automatic **option. Let’s follow the instructions below to learn!

- Go to the
**Formulas**tab >> Click on the**Calculation Options**option**drop-down**arrow from the**Calculation**group >> Select**Automatic**option. - You will notice that the formula will update automatically.

You can also do that by changing some features from the **Excel Options** dialog box.

- Go to the
**File**tab >> Select**Options**option.

- As a result, an
**Excel Options**dialog box will appear. Now, click on the**Formula**tab >> Set the**Workbook Calculation**option as**Automatic**>> Click**OK.**

Hence, we can say that our approach works effectively, and we are able to resolve the problem of formulas not calculating automatically in Excel.

**Read More: ****[Fixed]: Excel Formula Not Showing Correct Result**

### 2. Cell Value Is Formatted as Text

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

#### Solution: Converting Text Value to Number Format

If we convert the Text value to Number format, the formula will calculate automatically. Let’s follow the instructions below to learn!

- Select cell
**E6**and press**Ctrl + 1**As a result, the**Format Cells**dialog box pops up. From that dialog box, select**Number**tab >. Choose**Number**from the**Category**drop-down list >> Check the**Use 1000 Separator(,)**option >> Press the**OK**option.

- Finally, the Excel formula calculates automatically.

**Notes:**

If the cell that contains the Formula is in text format then the formula is not calculating automatically. To solve this issue, simply convert the text value to a Number or General format.

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

### 3. Presence of Apostrophes in Dataset

The presence of an **apostrophe** sign in the dataset does not allow the Excel formula to calculate value automatically. You may notice in the image we have a similar type of trouble in our dataset. We have several trapped apostrophes in cells from **C6 **to **E16**.

#### Solution: Removing Apostrophes or Leading Spaces

We will remove an apostrophe sign or leading space one by one. Let’s follow the instructions below to fix the problem!

- At the start, select a cell where the apostrophe sign is trapped. First, we click on cell
**C8**. Though the sign is not showing in the cell, you can figure it out by observing the data alignment.

- Now, click on the
**Formula Bar**and press the**Delete**button to delete the**apostrophe**sign manually >> Press**Enter**.

- Similarly, delete the other trapped apostrophes sign from the cells that contain the apostrophes symbol.

Finally, we can say that our example works successfully, and we are able to resolve the trouble of formulas not calculating automatically.

### 4. Excel Formulas Not Calculating Automatically Due to Leading or Trailing Spaces

The Excel formulas are not automatically calculating due to the Leading or Trailing spaces. From our dataset, you may notice several leading or trailing spaces. That’s why the Excel formulas are not calculating automatically.

#### Solution: Removing Leading or Trailing Spaces

You can fix this problem by removing the leading or trailing spaces from your dataset. Go through the process below to fix the problem!

- Select a cell where the leading or trailing spaces are trapped >> Click on the
**Formula Bar**and press the**Backspace**button to delete the leading or trailing spaces manually >> Press**Enter**. - Similarly, delete the other trapped leading or trailing spaces from the cells that contain the leading or trailing spaces.

### 5. Wrong Cell Reference

In the early stages of learning Excel, one of the fundamental concepts we encounter is utilizing the **dollar ($)** symbol to anchor cell references. For instance,

- While typing
**=B5**into a cell and**AutoFill**it down, it will change to**=B6** - While typing
**=$B$5**into a cell and**AutoFill**it down, it will remain as**=$B$5**

We quickly become accustomed to the referencing syntax, but its familiarity can lead to errors when copying formulas to different cells.

In the above screenshot, we are using the absolute reference that’s why the Excel formula shows the same result in the months of **February **and **March**.

#### Solution: Use Appropriate Cell Reference

To get the correct result, simply remove the *dollar symbol* from the Excel formula. Firstly, select cell **C17 **and remove the *dollar symbol* from the formula. Hence, **AutoFill **the formula up to column **E**. As a result, you can solve your problem.

**Notes: 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, due to the conversion from binary to decimal, there could be very slight rounding errors. Even while this might not matter, if it is included in a logical argument, it can produce a different outcome.

### 6. Numbers Enclosed in Double Quotes

Another issue arises when numbers are enclosed in double quotes, leading to a mix-up between text and numeric values. The Excel formula is not calculating automatically. In the below image, we notice that **the SUM function** returns the wrong output.

- We are using double quotes in
**the IF function**. As a result, some numbers are treated as text. That’s why the**SUM**function gives the wrong output.

#### Solution: Use Proper Arguments in an Excel Formula

We can fix this problem by removing the double quotes from the **IF **function’s arguments. As a result, the **SUM **function is calculating automatically which has been given in the below screenshot.

## Tips Users Should Follow to Fix Formulas That are Not Calculating Automatically in Excel

You can also fix the problem by following the below tips.

### 1. Applying Proper Cell Formatting

Choosing the wrong cell formatting is another cause for Excel formulas not calculating the values automatically. The procedure to set the suitable cell formatting is explained below step-by-step:

- Select the range of cells
**C6:E16**>> Hit the**Home**tab >> Choose a suitable format from the**Number**group >> We chose the**Currency**format for our cells.

- As a result, your can solve your problem.

So, we can say that our procedure works fruitfully, and we are able to get rid of the difficulty.

**Notes:**

While entering **12/10** into a cell, what do we want to get the return from that cell? Do you want to get the result of **12**? Or do we want to get the result of *10th December*? In this instant, Excel automatically 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*. Excel returns *45271 *as the number of that date.

### 2. Uncheck Circular Reference Option

The activation of the **Circular Reference** option sometimes causes trouble for Excel formulas in evaluating values automatically. We must turn off this feature so that the Excel formulas can work automatically. Let’s go through the process to learn!

- Go to the
**Formula**tab >> Click on the**drop-down arrow**of the**Error Checking**option from the**Formula Auditing**group >> Turn off the**Circular Reference**command.

- Hence, your Excel formula will not face any difficulty in actuation estimation.

In the end, we can say that our process works properly, and we are able to solve the problem.

### 3. Turn Off Show Formulas Command

To solve the problem, we can turn off the **Show Formula** command. It will help us to fix the issue, which is the **Excel** formula not estimating automatically. Let’s follow the instructions below to learn!

- Select the
**Formula**tab >> From the**Formula Auditing**group, click on the**Show Formula**option to turn off it.

- You will figure out that the formula will calculate the value automatically.

Therefore, we can say that our method works precisely, and we are able to fix the issue.

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

There are several reasons why you might choose to use the Manual Calculation Options in Excel:

**Control over Calculation:**With manual calculation, you have the ability to control when Excel calculates formulas. By default, Excel automatically recalculates all formulas whenever a change is made to a cell. However, in manual calculation mode, you can choose to recalculate the formulas manually, giving 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. 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 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.

➤ You can perform the following actions to recalculate an **active sheet**:

- Hit
**Shift + F9**simultaneously, or - Go to the
**Formulas**tab >> Select the**Calculate Sheet**feature from the**Calculation**

➤ To recalculate the **entire workbook**, you can perform following actions:

- Press
**F9**key, or - From the
**Formulas**tab, select the**Calculate Now**feature from the**Calculation**

➤ You can also recalculate the **all sheets in all open workbooks** by pressing **Ctrl + Alt + F9** simultaneously.

In order to recalculate a specific formula on a sheet, perform the following actions:

- Choose the cell containing the formula.
- Enter the editing mode by pressing
**F2**or double-clicking the cell. - Press the Enter key to execute the recalculation.

## Things to Remember

- Press the
**“F9”**key to manually recalculate the active worksheet. This can help if there are specific cells or formulas that need to be recalculated. - 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. - Circular references occur when a formula refers to the cell containing the formula itself. Excel may not calculate circular references automatically. 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**

Download this practice workbook for practice while you are reading this article.

## Conclusion

I would like to address the problem of Excel formulas not automatically calculating. I hope that this article will be helpful for you and you will be able to fix the problem of formulas that are not calculating automatically in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Keep learning new methods and keep growing!