Spreadsheets- Advanced Calculations
References
Switch between relative, absolute, and mixed references
- Select the cell that contains the formula.
- In the formula bar, select the reference you want to
change.
- Press F4 to toggle through the combinations. The "Changes
To" column reflects how a reference type updates if
a formula containing the reference is copied two cells down
and two cells to the right.
Formula being copied
| Reference (Description)
|
Changes to |
| $A$1 (absolute column and absolute
row) |
$A$1 |
| A$1 (relative column and absolute row)
|
C$1 |
| $A1 (absolute column and relative row)
|
$A3 |
| A1 (relative column and relative row)
|
C3 |
Circular reference
When a formula refers back to its own
cell, either directly or indirectly, it is called a circular
reference. Microsoft Excel cannot automatically calculate all
open workbooks when one of them contains a circular reference.
You can remove a circular reference, or you can have Excel calculate
each cell involved in the circular reference once by using the
results of the previous iteration.
 Locate
and remove a circular reference
If the Circular Reference toolbar is not
displayed:
- Click Customize on the Tools
menu
- Click the Toolbars tab
- Select the Circular Reference
check box
Finding the Circular Referente
- On the Circular Reference
toolbar, click the first cell in the Navigate Circular
Reference box
- Review the formula in the cell.
- If you cannot determine whether the cell
is the cause of the circular reference, click the next cell
in the Navigate Circular Reference box.
Note The status bar displays the word "Circular,"
followed by a reference to one of the cells contained in
the circular reference. If the word "Circular"
appears without a cell reference, the active worksheet does
not contain the circular reference.
- Continue to review and correct the circular
reference until the status bar no longer displays the word
"Circular."
Previous
| Next
|
