Article ID: 154134

Article Last Modified on 1/9/2007

APPLIES TO

- Microsoft Excel 97 Standard Edition

This article was previously published under Q154134

## SYMPTOMS

In Microsoft Excel 97, some formulas on a worksheet may not be recalculated automatically. This behavior may occur even if you choose to use automatic calculation for formulas in the worksheet.

## CAUSE

This problem may occur in either of the following cases.

### Case 1

- You create a range of formulas that reference data in other cells. -and-

- You insert a row within the range of formulas. -and-

- You select the values and the formula in the row beside the new row, and then fill the data into the new row. -and-

- You change the values of the cells that the formulas reference.

NOTE: To see an example, see Example 1 in the "More Information" section in this article.

### Case 2

- You create a range of two or more equivalent formulas that reference data in other cells. -and-

- You create a second range of equivalent formulas, and the second range intersects the first range of formulas. -and-

- You change values in the cells that the first range of formulas references.

NOTE: To see an example of this behavior, see Example 2 and Example 3 in the "More Information" section in this article.

## RESOLUTION

To resolve this problem, do one of the following:

- To correct this problem, obtain Microsoft Excel 97 Service Release 2 (SR-2).

For additional information about SR-2, please see the following article in the Microsoft Knowledge Base:151261 OFF97: How to Obtain and Install MS Office 97 SR-2

- If you are unable to obtain SR-2, then obtain and install the Excel 97 Auto Recalculation Patch.

For information about downloading and installing this patch, please see the following article in the Microsoft Knowledge Base:174868 XL97: How to Obtain the Excel 97 Auto Recalculation Patch

- If you are unable to do either of the above, follow these steps to temporarily work around this problem:

- On the Edit menu, click Replace.
- In the Find What box, type =. In the Replace With box, type =. Click Replace All.

## STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Office 97 Service Release 2 (SR-2).

## MORE INFORMATION

To select automatic recalculation, click Options on the Tools menu, click the Calculation tab, and then click Automatic.

### Example 1

To see an example of the problem as described for the first case in the "Cause" section, follow these steps:

- Create a new workbook in Microsoft Excel 97.
- Type the following into the workbook:
A1: 1 B1: 2

- Select cell C1. Type =SUM(A1:B1) and press ENTER.
- Select A1:C1. Drag the fill handle to C3.
- Select Row 3. On the Insert menu, click Rows.
- Select A2:C2. Drag the fill handle to C3.
- Select B1:B4. Type 5. Press CTRL+ENTER.

Note that values in C1:C3 are recalculated correctly but that the value in C4 is not.

### Example 2

To see an example of the problem as described in the second case in the "Cause" section, follow these steps:

- Create a new workbook in Microsoft Excel 97.
- Select A1:A10. Type 1. Press CTRL+ENTER.
- Select B1:B10. Type 2. Press CTRL+ENTER.
- Select C1:C10. Type =SUM(A1:B1). Press CTRL+ENTER.
- Select C8:I8. Type =$A$17. Press CTRL+ENTER.
- Select B1:B10. Type 3. Press CTRL+ENTER.

Notice that all values are calculated correctly except for the value in C10, which is not recalculated.

### Example 3

To see another example of the problem as described in the second case in the "Cause" section, follow these steps:

- Create a new workbook in Microsoft Excel 97.
- Type the following into the new workbook:
A1: 1

A2: 1

A3: 1

A4: =SUM(A1:A3) - Select A1:A4. Drag the fill handle to C4.
- Select column C. On the Insert menu, click Columns. Make sure column C is still selected and click Columns on the Insert menu again.
- Select B1:B4. Drag the fill handle to D4.
- Select A3:E3. Type 2. Press CTRL+ENTER.

Notice that all values are calculated correctly except for the value in E4, which is not recalculated.

## REFERENCES

For more information about automatic recalculation, click Contents And Index on the Help menu, click the Index tab in Microsoft Excel 97 Help, type the following text:

recalculating formulas, calculation methods

and then double-click the selected text to go to the "Change the way Microsoft Excel calculates formulas" topic.

Additional query words: XL97 recalc auto recalculation wrong function calc

Keywords: kbbug kbfaq kbpending KB154134