Today I want to touch on another elementary concept in Excel: ABSOLUTE VALUES.
It is important to note that using absolute cell references would have no value whatsoever unless you intend to copy the formula.
RELATIVE CELL REFERENCES
By default, a cell reference is relative. This means that when the formula in a cell is copied from one cell to another, the formula is changed to match the new cell. It is therefore copied relative to the new location.
For example, when you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left, and in the same row. A formula that contains a relative cell reference changes as you copy it from one cell to another.
ABSOLUTE CELL REFERENCES
In some situations, you may need the formula (or part of the formula) to stay the same when you copy it. You will then need to make use of an absolute cell reference. Unlike relative references, absolute references do not change when copied.
An absolute reference is designated in a formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both. (The dollar signs could also be inserted by pressing the F4 key)
For example, if you copy the formula =A2+B2 from cell C2 to D2, the formula in D2 adjusts to the right by one column and becomes =B2+C2. If you want to maintain the original cell reference in this example, you can make the cell reference absolute by preceding the columns (A and B) and row (2) with a dollar sign ($). Then, when you copy the formula =$A+$B from C2 to D2, the formula stays exactly the same.
Absolute references are especially handy when you have to refer to a single cell in a formula, such as in the example below:
NEED HELP WITH EXCEL?
Struggling to implement these tips? Please feel free to book a session with me. My email address is email@example.com
Showing all records: