This sample dataset holds the income and tax to be paid by each person. Here, income and tax on income are two dependent variables.

## Method 1 – Checking if Calculation Options is Set to Manual

**Steps**

- Go to the
**Formula**tab in the ribbon. - Select
**Calculation Options**.

- In
**Calculation Options**, select**Automatic**.

- Now, when a variable is changed, any Excel formula in use will update automatically.
- Here, the percentage of tax to be paid is changed from 10% to 15% which automatically updates the relevant column.

## Method 2 – Show Formulas Option Prevents Excel Formula from Updating Automatically

**Steps**

- Go to the
**Formula**tab in the ribbon. - Uncheck the
**Show Formulas**option from the**Formula Auditing**group.

Check if the Excel formula are updating or not.

## Method 3 – Incorrect Cell Format Blocks Formula to Update Automatically

The formula will not update automatically if the **Number** format is set to **Text**.

**Steps**

- Go to the
**Home**tab in the ribbon. - In the
**Number**group, check the**format**.

- If it is in
**Text**format then change it to**General**.

Check if the Excel formulas are updating or not.

## Method 4 – Circular References in Formula Prevents Excel from Updating

Circular references can be defined as a formula that includes itself or refers to another cell that depends on it.

Circular references can cause Excel to slow down and prevent Excel formulas automatically updating. Check the circular reference and then recalculate your datasheet.

## Method 5 – Excel Formula Not Updating Due to Manual Calculation Mode

When **Macro** is used, Excel can’t update the formula automatically.

**Steps**

- Check the
**VBAProject**sheet or**Module**in Visual Basic by pressing**Alt+F11**.

- Now, you can delete it or use the following code to get the automatic calculation.

```
Sub AutomaticCalculation()
Application.Calculation = xlCalculationAutomatic
End Sub
```

## Method 6 – A Space Before Equal Sign in Formulas Blocks Automatic Updating

**Steps**

- Check any active formulas one by one for a space before the equal (=) sign.

- If you find any space delete it.
- Press
**Enter**to apply the formula.

- The Excel formula will update changes automatically.

If a formula is written without an equal sign, Excel counts it as a normal text value. This is one of the main reasons why the Excel formula is not working or not updating automatically.

**Steps**

- Check your formula box one by one.
- Ensure there is an equal sign before every formula.

- Press
**Enter**to apply the formula.

- The Excel formula will update changes automatically.

## Method 7 – Excel Treats Formula with Numbers in Double Quotes As Text and Will Not Update Automatically

**Steps**

- Check in the formula box if there are any double quote mark around the number.

- Delete those quotes and recalculate the formula by pressing
**Enter**.

- The Excel formula will update changes automatically.

## Method 8 – Using the Wrong Character to Separate Function Arguments Prevents Automatic Updates

Not every country uses the same symbol to separate lists.

**Steps**

- Go to the
**Home**tab in the ribbon. - Select
**Number Format**from the**Number**group.

- Select
**More Number Formats**from the dropdown box.

- Select
**Date**from the**Category**. - Change the
**Location**. - Click
**Ok**.

**Download Practice Workbook**

Download the practice workbook

**<< Go Back to Excel Formula Errors | Excel Formulas | Learn Excel**

Hello, I added a sumcolor function in my VBA, and that function won’t update automatically when I change a cell’s color. Any suggestions?

Hello Nathan,

first of all, if you want to use sum color function, you can use the following code.

Function SumByCol(SumRange As Range, SumColor As Range)

Dim SumColValue As Integer

Dim TotSum As Long

SumColValue = SumColor.Interior.ColorIndex

Set pCell = SumRange

For Each pCell In SumRange

If pCell.Interior.ColorIndex = SumColValue Then

TotSum = TotSum + pCell.Value

End If

Next pCell

SumByCol = TotSum

End Function

Then, close the VBA code and select any cell where you want to put the sum value. After that, write the following formula

=SumByCol(C5:C10,C13)

Where the range of cells C5 to C10 defines the range. C13 defines the color. You need to show only color in one cell. From where you can take the reference. Here, C13 denotes that color reference. So, when you want to change the color, you have to change the color reference also.

Hi, I have a spreadsheet on Sharepoint that will not calculate some forumale. When we open the document in the app it all works fine – it’s ONLY on Sharepoint. Since 4-5 people need to access to the file – often 2-3 at once, we need it on Sharepoint. Any ideas why it’s ONLY online that we have an issue?

Hello Craig,

We are not dealing with SharePoint at this moment. So, I can’t give you a proper solution to this problem. In the future, if we work with SharePoint, we will get back to you and provide you with a useful solution.

Thanks.