Automatic or Manual Calculation?: Grouped Sheets causes Calculation Confusion.

Thanks to Simon Hurst and Paul Wakefield for telling me about this calculation weirdo, and credit to Chatman at Accounting Web for discovering it.

Grouped Sheets and Calculation Mode

Try these experiments:

Experiment 1

1. Start Excel
2. Open a workbook in automatic calculation mode and with only 1 sheet selected (so there are no grouped sheets).
3. Add  =NOW() to a couple of sheets and format the cells to show seconds so that you can see when it calculates.
4. Check that Calculation shows Automatic both in File-> Options->Formulas and in the Formulas Tab->Calculation Options.
5. Set Calculation to Manual (does not matter how you do this)
6. Select 2 or more Sheets (hold down Control and select 2 sheet tabs) so that the sheets are grouped (The Workbook title bar should show [Grouped] ).
7. Calculation is still Manual
8. Set the Calculation to automatic using File->Options->Formulas
9. Notice that the workbook calculates, but Calculation immediately returns to manual (check both Formulas Tab->Calculation Options and File->Options->Formulas)
10. Now set the Calculation to automatic using Formulas Tab->Calculation Options
11. Now the workbook calculates and stays in Automatic Calculation Mode, but File->Options->Formulas says its Manual and Formulas Tab->Calculation Options correctly says its Automatic!
12. Now ungroup the sheets and everything works normally

Experiment 2

1. Start Excel
2. Open a workbook in automatic calculation mode and with only 1 sheet selected (so there are no grouped sheets).
3. Add  =NOW() to a couple of sheets and format the cells to show seconds so that you can see when it calculates.
4. Check that Calculation shows Automatic both in File-> Options->Formulas and in the Formulas Tab->Calculation Options.
5. Select 2 or more Sheets (hold down Control and select 2 sheet tabs) so that the sheets are grouped (The Workbook title bar should show [Grouped] ).
6. Excel should still be in Automatic calculation mode, but File->Options->Formulas shows Manual and Formulas->Calculation Options shows Automatic.
7. Calculation is actually Automatic.

Confused? Well so is Excel!

Conclusions

• Looks like it is safer to use Formulas->Calculation Options
• If you are using Grouped Sheets be careful about your Calculation Mode!

Advertisement