Gent Technologies

Relative and Absolute cell referencing

It is possible to copy a formula throughout the spreadsheet by using the Fill Handle option (click on the cell containing the formula, move the pointer to the bottom-right corner of the cell, click and drag down or across as far as required).  This is known as Relative References.  Excel automatically changes the references to the cells as you use Fill Handle.

However, there are occasions when you are required to reference a particular cell value that cannot change this reference as you Fill Handle.  This fixed reference is known as an Absolute Reference.

To enter an absolute reference into a formula, the dollar sign ($) must be added before each letter or number, for example the cell D4 will now be $D$4.  As Excel is automatically calculating the formula as you Fill Handle, it will always reference D4 when calculating that part of the formula.

To add the dollar sign without typing it manually, press the F4 key at the top of the keyboard while typing in the formula.

Circular References

If a formula has been set up that refers to its own cell directly or indirectly, this is known as a Circular Reference.  Some engineering and scientific formula calculations will require circular references.  Microsoft Excel cannot resolve formulas containing circular references by normal calculation methods.  Excel must calculate each cell involved once by using the results of the previous calculation it has performed.  It will repeat this 100 times to attempt to resolve the calculation.

If you make a circular reference while creating a formula Excel will display a warning message.  If the circular reference is accidental, click on the OK button.  The Circular Reference Toolbar will be displayed to help you redesign the formula.

Quick Links
Excel Test 1 Excel Test 2 Excel: Selecting Cells Excel: Forumlas Excel: Cell Referencing Word Test 1 Word Test 2 Word Test 3 PowerPoint Test 1 PowerPoint Test 2 Access Test 1 Internet Test 1