Calculating Cells & Dependent Cells

Calculate

By default, spreadsheets are in Auto Calculate mode.  This means that any formulas immediately recalculate when any dependent cells are updated with new values. You can change this mode to manual calculation mode if you like.

To Turn-Off Auto Calculate

  • Uncheck the Auto Calculate menu item in the Options menu.  The spreadsheet will not automatically calculate formulas when new values are entered, you must manually calculate formulas. 

To Manually Calculate Formulas

  • Select Calculate Now from the Options menu.  If the spreadsheet is in Auto Calculate mode, there is no need for manual calculation. 

Dependent Cells

Any cell containing a formula which references other cells or named selections is considered a dependent cell.  In other words, the cell depends on values from other places in the spreadsheet.  Consider the following table:

  A B C
1 2.5 $50.00 $125.00
2 4.0 $25.00 $100.00

In this example, the respective values in columns A and B are multiplied to calculate the values in column C.  Thus, cells A1 and B1 have one dependent, cell C1.

Dependent cell identification is useful when you have very complex data and formulas.   You can select a single cell and using the Dependent cell command, identify all the cells that depend on the selected cell for values. 

To Identify Dependent Cells

  1. Select a cell with a value. 

  2. Select Select Dependents from the Options menu.   All cells containing references to the selected cells are highlighted. 

Absolute & Relative Cell References

Dependent cells are cells containing formulas which reference other cells or named selections.  The cell depends on values from other places in the spreadsheet.  Thus, when you are copying, cutting or otherwise moving cells in your document, two things can occur.  Either the dependent cell reflects the change in location (Relative Cell Reference), or the formula remains the same when moved around the spreadsheet (Absolute Cell Reference). 

Relative Cell Reference 

Generally, if you copy or cut a dependent cell that has a formula in it, and then proceed to paste it in a different cell address, the formula reflects the change.  For example, if you wish to multiply the values in cells A1 and B1, you enter the formula '=A1*B1' into cell C1.  If you then copy C1 and paste it into C2, the formula is modified to show the change in cell-location.  C2 therefore has the formula '=A2*B2', and the final result of C2 is the multiplication of A2 and B2, not the original A1 times B1.

  A B C
1 2.5 $50.00 $125.00
2 4.0 $25.00 $100.00

Absolute Cell Reference 

If you do not want the formula to change when you move it to a different cell, then you need to inform the dependent cell by typing a dollar sign ($) before each part of the function's characters.  This action prevents the formula from changing when it is copied or moved to a different cell address.  For example, in the spreadsheet below, the formula '=$A$1*$B$1' is entered into cell C1.  This multiplies cell A1 by cell B1.  Since a dollar sign was entered before each character in the formula, when C1 is copied and pasted into cell C2, the formula remains the same and the result of the function does not change.

  A B C
1 2.5 $50.00 $125.00
2 4.0 $25.00 $125.00


NOTEYou can have formulas that include both absolute and relative cell references.  Just type in the dollar sign ($) infront of any character within a formula or function that you do not want to change if the formula is moved to a different cell location. 

Copyright © 1999, Gobe Software, Inc