Excel Cell References Explained: Relative, Absolute and Mixed ($)

• A cell reference is a description of its row and column position.
• All cells have references, and as Excel has 16384 columns and 1048576 rows cell references could look like anything from A1 to CF320 to XFD100000.
• Cell references could come in three formats: relative, absolute and mixed.
• Relative references look like the earlier examples: they are simply a combination of letters and numbers e.g. A22, H14, etc.
• Absolute references are letters and numbers with two dollar signs - one before the row reference and the other before the column reference e.g. $A$22, $H$14.
• Mixed references have only one dollar sign - and this sign could come either before the row reference or before the column reference e.g. $A22, A$22, $H14, H$14.
• The dollar sign indicates a 'locked' status for the cell reference, either for the row position, the column position, or both row and column positions. So absolute references have both row and column position locked, while mixed references that look like $A22, $H14 have column positions locked and those that look like A$22, H$14 have row positions locked.

There are several implications of understanding this when creating formulas in Excel. One example is - if you had a constant factor, say in position A3 of the cell that you wanted to multiply all the items in a column (say Column C) by, if you just typed =C4*A3, and tried to drag down the formula would increment by one with each new row. So you would end up with entries resulting from =C5*A4, =C6*A5, =C7*A6, which was not your intent. To prevent this from happening, change the A3 reference in the first formula from a relative to an absolute reference so that both row and column positions are locked when you extend the formula. Thus the formula will read =C4*$A$3. Another example is if you had a first column of items (say Column A) that you wanted to use to multiply values up till Column G. To build the formula such that the first column reference remains constant but you can drag down to extend to further rows below, you would use a mixed reference (e.g. $A3).