Microsoft Office Tutorials and References

In Depth Information

**Turn Calc On/Turn Calc Off**

unchanging cell reference. Get this to work correctly by using an absolute

reference to the cell.

To use an absolute reference to a cell, use the dollar sign ($) before the row

number, before the column letter, or before both. Do this when you write the

first formula, before dragging it to other cells, or you will have to update all

the formulas.

For example, don’t write this:

=A4 × (B4 + A2)

Write it this way instead:

=A4 × (B4 + $A$2)

This way all the formulas reference A2 no matter where you copy them,

instead of that reference turning into A3, and A4, and so on.

Turn Calc On/Turn Calc Off

The Excel default is to calculate your formulas automatically as they are

entered or when you change the worksheet. In some situations you may want

to set the calculation to manual. Leaving the setting on automatic is usually

not an issue, but if you are working on a hefty workbook with lots of

calculations, you may need to rethink this one.

Imagine this: You have a cell that innocently does nothing but display the

date. But then there are dozens of calculations throughout the workbook that

reference that cell. Then there are dozens more calculations that reference

the first batch of cells that reference the cell with the data. Get the picture?

In a complex workbook there could be a lot of calculating going on. And the

time this takes can be noticeable.

Turning the calculation setting to manual lets you decide when to calculate.

Do this in the Excel Options dialog box; click File tab on the Ribbon, and then

click Options. In the dialog box click the Formulas tab, in which calculation

options are selected as shown in Figure 18-4. You can check for one of the

automatic calculations settings or for manual calculation.

Pressing F9 calculates the workbook. Use it when the calculation is set to

Manual. Here are some further options: