Using Absolute and Relative References in Excel 2010 Formulas
You can use three types of cell references in Excel 2010 formulas: relative, absolute, and mixed. Using the correct type of cell reference in formulas ensures that they work as expected when you copy them to another location in the worksheet. Formulas and functions are at their most useful when using references, so you need to understand them.
Excel is dynamic when it comes to cell addresses. If you have a cell with a formula that references a different cell’s address and you copy the formula from the first cell to another cell, Excel updates the cell reference inside the formula. Try an example:
In cell B2, enter 100.
In cell C2, enter =B2*2.
Cell C2 now returns the value 200.
Select cell C2.
Press Ctrl+C or click the Copy button on the Home tab.
Click cell C3.
Press Ctrl+V or click the Paste button on the Home tab.
If you see a moving marquee around cell C2, press the Esc key.
Cell C3 should be the active cell, but if it is not, just click it once. Look at the Formula bar. The contents of cell C3 are =B3*2, and not the =B2*2 that you copied.
What happened? Excel, in its wisdom, assumed that if a formula in cell C2 references the cell B2 — one cell to the left — then the same formula put into cell C3 is supposed to reference cell B3 — also one cell to the left.
When copying formulas in Excel, relative addressing is usually what you want. That’s why it’s the default behavior. Sometimes you don’t want relative addressing but rather absolute addressing: making a cell reference fixed to an absolute cell address so that it doesn’t change when the formula is copied.
In an absolute cell reference, a dollar sign ($) precedes both the column letter and the row number. You can also have a mixed reference in which the column is absolute and the row is relative or vice versa. To create a mixed reference, you use the dollar sign in front of just the column letter or row number. Here are some examples:
|Reference Type||Formula||What Happens After Copying the Formula|
|Relative||=A1||Both the column letter A and the row number 1 can change.|
|Absolute||=$A$1||The column letter A and the row number 1 do not change.|
|Mixed||=$A1||The column letter A does not change. The row number 1 can
|Mixed||=A$1||The column letter A can change. The row number 1 does not