When you first begin using Excel, you give little thought as to how Excel adjusts the cell references when you copy a formula. You just know that it works, until you copy a formula that returns a result different from what you expected. Excel understands three types of cell referencing: relative, absolute, and mixed.

Relative referencing – Excel’s default for copying formulas. When you copy the formula, the row and column references in the formula automatically adjust to properly reference the correct cells in relationship to the copied formula’s new location. For Example: Suppose you have the following formula in Cell B1 (=A1). When you copy the formula in Cell B1 to Cell E7, Excel counts 3 columns and adds it to the column reference (A + 3 letters = D) and counts 6 rows and adds it to the row reference (1 + 6 = 7). The new Cell reference correctly becomes D7.

Absolute referencing – when you want the referenced cells to always be the same cell no matter where the formula might be copied. For example, suppose you had a constant tax rate that is applied to various purchases. Cell A1 contains the tax rate. In column A starting in cell A3, you have a list of sale amounts and you want to see the amount of the tax amount in column B. You create a formula in cell B3(=A3*A1). You know if you copy that formula down column that the A1 will change but you don't want that to happen because the tax rate is in cell A1. Excel uses the $ to designate an absolute reference. It tells Excel to ALWAYS use that cell location ($A$1)no matter where the formula is copied. The proper reference for your formula in cell B3 is (=A3*$A$1) thus when copied down column be the A1 referencing will remain absolute. Excel copies it exactly the same because it interprets the $ to mean you want an absolute reference to that cell.

Mixed References – when you want either the column or the row reference to be fixed. Excel interprets the reference $A1 as an absolute column reference combined with a relative row reference. Meaning, when copied, the column reference would not change but the row reference would change. Alternatively for a relative column reference combined with an absolute row reference the correct notation would be shown as A$1. In this case the column reference would change but the row reference would not change.

F4 Key
The F4 key on your keyboard will toggle the choices for cell referencing. With the cursor blinking next to the cell reference in your equation, press the F4 key once to get an absolute Column and Absolute Row reference ($A$1); press the F4 key twice to get a relative Column and absolute Row reference (A$1); press the F4 key three times to get an absolute Column and relative Row reference ($A1); press the F4 key four times to return to a relative Column and relative Row reference.