If you use formulas in Excel, more than likely, you’re referencing other cells. The way a cell is significantly referenced affects how a formula appears in other cells.
Relative references adjust and change based on their location in a spreadsheet. Absolute references, on the other hand, stay the same regardless of where they are copied.
So if you write a formula in cell C1 that makes an absolute reference to cell A1, you will still refer to cell A1 and B1 even after pasting this formula to cell C2. However, a relative reference refers to cells A2 and B2.
Absolute References in Excel
By default, every cell reference in Excel is a relative reference. If you refer to cell A1 in a formula as A1, Excel will interpret this to be a relative reference, whereas referring to it as $A$1 will make it an absolute reference.
Absolute references are useful when you want to refer to a constant value in a formula. Let’s check the example.
If you have two scores for each team in a robotics competition, one from their first set, and another from their second set. The total score for each team is the sum of their scores on both sets multiplied by a constant multiplier.
Let’s write the formula for the first team with an absolute reference and then copy it for the other teams.
- Select cell C3.
- In the formula bar, write the formula below and press enter:
=(A3+B3)*$B$1This will add A3 (score from set 1) with B3 (score from set 2), and then multiply the result by B1 (the multiplier). Note that A3 and B3 are relative references, while B1 is an absolute reference.
- Grab the fill handle and drag it over the cells below. Excel will now calculate the final score for all teams.
Click on each of the cells of the final score and observe the formula in the formula bar. As you can see, the first two reference cells have changed, but the third one has remained constant. This is because the third reference cell is an Absolute Reference.
Control Your Excel References
In Excel, absolute references help keep values constant in formulas. You now know how to use absolute references when you want to copy a formula, but copying formulas in Excel is more than that.
