Introduction
In this lesson, you will learn about relative references in Excel.
This video illustrates the lesson material below. Watching the video is optional.
Relative References in Excel
When you reference a cell, you use the name of the cell as a variable. Referencing cells, which is sometimes referred to as creating relative references, is incredibly useful. It tells Excel that you want whatever is found in one cell to be put into another cell.
Example 1
Figure 1
The number 2 is in cell A1. Follow the steps below to reference that number in another cell:
- Select another cell (in this case, A3).
- Type the equal sign (=).
- Type the name of the cell, or select the original cell (in this case, A1).
- Press Enter.
The figures below show what this looks like in cell A3:
Figure 2
Figure 3
If you check the formula bar for cell A3, you will find that it contains the equation: =A1.
If you change the number in A1, it will automatically change the number in A3. In the example shown in Figure 4, it shows that cell A1 was changed to the number 9 which automatically changed A3 to the number 9.
Figure 4
This is the primary reason for using cell referencing. If you just type in the numbers manually, you must go back and change any formulas you have used as well. Cell referencing saves time and energy, which becomes more valuable as the math becomes more complex.Referencing Cells in Formulas
Referencing cells is particularly useful when writing formulas.
Example 2
The number 9 is in cell A1, and the number 2 is in cell B1.
Figure 5
You could write an equation by just typing: \(=9+2\) in cell C1, as shown in Figure 5 above. However, you can also write an equation by referencing the other cells in cell C1, as described below:
- Type the equal sign (=) into a cell (in this case, C1).
- Select the cell that has the first number (A1).
- Type the plus sign (+).
- Select the cell that has the second number (B1).
- Press Enter to calculate the equation.
Figure 6
Referencing cells in an equation is useful for two reasons. When you reference cells while performing an equation, you can see the equation you used in the formula bar at the top of the Excel sheet, as shown in Figure 6.
You can also change any of the variables you referenced, and the formula will automatically update. As shown in Figures 7 and 8, if you change cell B1 to 9, the answer for cell C1 will be 18, because \(9+9=18\).
Figure 7
Figure 8
Referencing Cells with Addition
There is a set of numbers in the table below. These numbers come from multiplying the numbers in column A with the numbers in row 1.
Figure 9
As learned before, you can reference any of these cells in another cell simply by typing the equal sign (=) followed by the reference number for the cell.
Example 3
If you wanted to reference the number 28 (from cell B5), you would type =B5 into cell B10, and 28 would appear after pressing Enter.
Figure 10
Figure 11
Example 4
Practice addition using some of these variables, in this case cell B2 and cell F2.
- In cell D10, type the equal sign (=).
- Select the cell that has the first number (B2).
- Type the plus sign (+).
- Select the cell that has the second number (F2).
- Press Enter to calculate the equation.
Figure 12
To verify that Excel performed the equation correctly, you can manually check the answer on your own. If you perform the equation \(7+11\), you find that 18 is the correct answer.
Note that the letters in the variables do not need to be capitalized. Excel will recognize them whether they are lowercase or uppercase.Referencing Cells with Multiplication
Using the same table of numbers as used with the previous addition problem, practice a multiplication problem with two variables.
Example 5
Multiply cell D4 and cell G2.
- In cell F10, type the equal sign (=).
- Select the cell that has the first number (D4).
- Type the star key (*).
- Select the cell that has the second number (G2).
- Press Enter to calculate the equation.
Figure 13
If you are concerned about the answer you get, you can always check the results by manually multiplying 27 and 12, which is 324.Why Reference Cells?
Referencing cells allows a formula to automatically update after a variable is changed, without rewriting the entire formula. This saves time and effort and provides immediate results.
Example 6
If you change the value of cell B1, notice all the numbers in column B automatically changed to be multiples of 5. You do not have to manually change them because referenced cells are being used. Notice the value of cell D10 has also changed because its formula was =B2+F2.
As another example, you could change cell G1 to 15. Doing so will automatically change the value of cell F10 because G1 is referenced in cell F10’s formula.
This is what makes reference cells so useful in Excel. The references are variables and the variables can be changed. This allows for cells that refer to other cells to be changed automatically.
Figure 14
Things to Remember
- Referencing a cell means using the name of the cell as a variable.
- When typing in the name of a referenced cell, the letters do not need to be capitalized.
- Feel free to check an answer which Excel calculated by manually adding or multiplying the numbers from your equation.