Cell Referencing

Relative Cell References

Calling cells by just their column and row labels (such as "A1") is called relative referencing. When a formula contains relative referencing and it is copied from one cell to another, Excel does not create an exact copy of the formula. It will change cell addresses relative to the row and column they are moved to. For example, if a simple addition formula in cell C1 =A1+B1 is copied to cell C2, the formula would change to =A2+B2 to reflect the new row.

Absolute Cell References

There are occasions when copying cell references that you don’t want the cell references to be adjusted automatically. To prevent this change, cells must be called by absolute referencing. This is accomplished by placing dollar signs ($) within the cell addresses in the formula. Continuing the previous example, the formula in cell C1 would read =$A$1+$B$1 if the value of cell C2 should be the sum of cells A1 and B1. Both the column and row of both cells are absolute and will not change when copied.

Mixed Cell References

Mixed cell referencing is when only part of the cell reference – the column letter or row number – is absolute, as in $A1 or A$1.

  B C D E
5  
January Visits
February Visits
% of Visits
6 CYP
3
2
=C6/C$8
7 EVC
5
8
8 Totals
=SUM(C6:C7)
=SUM(D6:D7)

When the formula in E6 gets copied to cell E7, it becomes =C7/C$10. The first cell reference (C6) is relative, so it is adjusted one row down. In the second cell reference (C$10), the row number is not adjusted because it is absolute.


Home | Workspace | Worksheets | Toolbars | Namebox | Modify Workbook | Add Columns | Enter Data
Move Cells | AutoSum | Formulas | Basic Functions | Insert a Function | Referencing | Named Ranges
Formatting Toolbar | Format a Cell | AutoFormat | Page Properties | Printing | Quiz