Excel Practice
Absolute References in Excel
In order to understand absolute references, it is important to understand relative references. This lesson compares absolute references to relative references and explains some of the basics on when to use them.
Relative Reference: A relative reference changes when copied to another cell. Write a relative reference by using the cell name with the letter of the column and the number of the row for the cell being referenced.
Examples:
A1
F37
Absolute Reference: The entire cell reference or just parts of the cell reference does not change when copied to another cell. Write an absolute reference by putting a $ symbol in front of the portion of the reference that should remain unchanged when the equation is copied.
Examples:
$B2 The column doesn’t change but the row changes as an equation is copied.
B$2 The row doesn’t change but the column changes as an equation is copied.
$B$2 Neither the row nor the column changes when the equation is copied. This means that it is only referencing a single cell and nothing will shift when the equation is copied.
The projects and assignments in this class will only require a single cell absolute reference such as $F$4 where both the column and row remain the same.
Tip
A quick way to make a reference absolute without typing in the $ symbols is to click on the reference cell and then click on the F4 key on the keyboard. (You may need to click on other keys too depending on how your computer accesses the function keys.)
Video Tutorial
The first part of this video goes over how relative references work in order to compare them to absolute references. At about minute 2:40, the video starts to explain why we need absolute references, when to use them, and how to enter them into an equation.
Video Source 06:27 mins | Transcript
When to use Relative References
We use relative references when we want to copy the pattern of an equation.
For example, there are two lists of data; one list in columns A and another in column B. In column C we want to add the number across the row from columns A & B. To do this, write an equation in column C adding the two cells to the left. Example: In C1, type the equation
=A1+B1.
If we copy this equation, using relative references, down the rest of the cells in C1, the pattern of the equation will copy not the exact cells. Instead, they will copy the pattern of picking the two cells to the left and adding them together.
When to use Absolute References
An absolute reference copies the exact cell and not just the pattern of the equation.
Suppose we have an equation we want to copy, but one of the variables in the equation should be the same for all the cells. Use an absolute reference for this variable within the equation.
Example: You want to add all the cells in column A to the same number or constant.
Do this by assigning one cell to be the constant amount.
In this example we have our data in cells A3:A9 and our constant in cell B1.
The next step is to write an equation to add the cell in column A to the number in the constant cell. We do this by using an absolute reference for the constant cell.
In cell C3 we write =A3+$B$1
Notice that in our equation, the A3 is a relative reference and the $B$1 is an absolute reference for B1.
Now when we copy this equation through the rest of the cells in column C, it will automatically change the reference for the cell from column A but it will keep the absolute reference the same every time.
We see in cell C6 that the equation for the A column cell has changed to cell A6 but the absolute reference to B1 has stayed the same.
Example Video
The following video demonstrates another example of using absolute references in Excel. It shows how to use them, why they are helpful, and tips on how to write them faster.
Video Source 04:58 mins | Transcript