Absolute References in Excel

Introduction

In order to understand absolute references, it is important to understand relative references. In this lesson, you will compare absolute references to relative references and learn some of the basics about when to use them.

These videos illustrate the lesson material below. Watching the videos is optional.

Relative References

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.

Example 1
Consider the formula: =A1+B1 in cell C1. In this example, cell A1 has 1 in it while cell B1 has a 3 in it. This means that the formula in C1 equals 4.

Image shows Excel spreadsheet. Cell A1 holds 1, cell B1 holds 3, and cell C1 holds the formula =A1+B1.

Figure 1

When using relative references, using the cursor to drag the equation to another cell will not copy the original formula. Instead, it will automatically change depending on where you copy it.

To drag an equation that has been created already to another cell, select cell C1. There is a little square on the bottom right corner (see Figure 2). Drag the little square to cell C2 by moving it downward (see Figure 3).

Image shows Excel spreadsheet. Cell A1 holds 1, cell B1 holds 3, cell C1 holds 4, cell A2 holds 2, and cell B2 holds 5. There is an arrow pointing to the small green square that appears in the bottom right corner of cell C1 when the cell is selected.

Figure 2

Image shows Excel spreadsheet. Cell A1 holds 1, cell B1 holds 3, cell C1 holds 4, cell A2 holds 2, and cell B2 holds 5. There is a dashed arrow from the small green square in the bottom right corner of cell C1 and pointing to the bottom right corner of cell C2, indicating that the small green square can be selected and dragged down to cell C2.

Figure 3

This automatically shifts the reference cells down one row also, so it now adds the two cells to the left of cell C2.

Image shows Excel spreadsheet. Cell A1 holds 1, cell B1 holds 3, cell C1 holds 4, cell A2 holds 2, cell B2 holds 5, and cell C2 holds 7 (which is the answer to the formula =A2+B2).

Figure 4

Relative references are great for copying patterns rather than exact formulas and numbers. To demonstrate this, add more data to the cells in column A and column B. Copy the equation from cell C1 down through the rest of the cells on column C. Notice that the formula in column C is the addition of the two previous cells to the left.

Figure 5

Figure 6

Sometimes you want the variables in your equations to remain constant. For example, if you change column B cells to all be the number 3, then you will notice that column C is still the sum of the two previous cells to the left due to the formula.

Figure 7

Absolute References

Absolute references provide a different way to set up equations. An absolute reference is a cell reference that doesn’t change when an equation is copied. Use the dollar sign ($) to indicate that something does not change. If you want to reference the same cell each time, put a dollar sign in front of both the letter and the number in the variable, like $B$1.

$B$2 Neither the row nor the column will change. This means that it is only referencing a single cell and nothing will shift when the equation is copied.

Once an equation has an absolute reference value in it, you can copy the equation anywhere you like, and the equation will always refer to the absolute reference.

Example 2
Returning to the previous example problem, try working it out using an absolute reference. Notice all the data is shifted two rows down and the data in column B has been deleted. In row 1, cell A1 has the word constant and cell B1 has the number 3 which will be the absolute reference cell. Cell C3 has the first equation where it is still referencing the two cells to the left, A3 and B3.

Image shows Excel spreadsheet. Cell A1 contains the word Constant, cell B1 holds 3, cell A3 holds 1, cell C2 is selected to show the formula it holds (which is =A3+B3), cell A4 holds 2, cell C4 holds 2, cell A5 holds 3, cell C5 holds 3, cell A6 holds 4, cell C6 holds 4, cell A7 holds 5, cell C7 holds 5, cell A8 holds 6, cell C8 holds 6, cell A9 holds 7, and cell C9 holds 7.

Figure 8

Instead of cell B3, you want cell B1 (the number 3) to be constant in the equation. Change cell B3 in the equation to an absolute reference to cell B1. To do this, either type dollar signs ($) before B and the 1, or select cell B1 and then press the F4 key on the keyboard, which will automatically create the dollar signs ($) before the B and the 1. The relative reference to cell B1 is now changed to an absolute reference and looks like $B$1.

A following formula can be seen in cell C3 with cell references from cell A3 and B1: =A3+$B$1. The second cell reference is in absolute reference form.

Figure 9

Cell C3 has a new value of 1 plus 3 which is 4. Copy the equation down to the other cells in column C. They all now have the addition of 3 to the number in column A of the corresponding row.

A formula in cell C9 is seen in the image with cell references from cell A9 and B1. The formula looks like this: =A9+$B$1. The second reference is in absolute reference form.

Figure 10

Absolute references are a wonderful tool to understand in Excel. They make it easy to perform calculations on the value in a single cell over and over again.

Mixed References

In some cases, you may want to only change the row or the column, but keep the other fixed. In these instances, you can use a mixed reference. For example, a dollar sign before the letter of the reference, like $B1, means that the column does not change. A dollar sign before the number of the reference, like B$1, means that the row does not change.

$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.

Mixed references are helpful when working with multiple data points within a table.
For the purposes of your assignments in PathwayConnect, you will focus on relative and absolute references only.

Absolute References in Calculations

Example 3
Practice using absolute references to calculate simple interest. Use the following equation:

Interest Earned = (original amount)*(interest rate)*(number of years)

In this example, you are earning 5% interest, and you have $1000 to invest.

The image shows that Percent is 5%, Amount is $1,000.00, and Year is numbered from 1-4, with no values under the Interest Earned column.

Figure 11

Both the interest rate and the starting amount must be input in the equation as absolute references because neither of them will change. However, since the year will be changing, it will be left as a relative reference.

The following formula is in cell B5, with the first and second references in absolute reference form: =$B$2*$B$1*A5.

Figure 12

After you have input the equation to cell B5, you can select cell B5 and drag it down with your cursor to add the equation to additional cells.

The following formula is in cell B11 with the first and second references in absolute reference form: =$B$2*$B$1*A11.

Figure 13

In this example, it becomes very apparent if you have used relative references instead of absolute references. If you use relative references, Excel will display errors because it will be using cells without numeric values as part of the equation (see Figure 14).

The following formula is in cell B5: =B2*B1*A5.

Figure 14

As with other equations in Excel, it is helpful to reference cells so you can alter variables without having to edit every cell. The images below show examples of how the final amount changes when you alter either the starting amount (Figure 15) or the interest rate (Figure 16).

The image shows different values under the Interest Earned column when you change the amount in cell B2.

Figure 15

The image shows that values change under the Interest Earned column when you change the interest rate in cell B1.

Figure 16

Things to Remember

An absolute reference is a cell reference that doesn’t change when an equation is copied.
A dollar sign ($) before both the letter and number in a cell indicates an absolute reference.

Need More Help?

Study other Math Lessons in the Resource Center.
Visit the Online Tutoring Resources in the Resource Center.
Contact your Instructor.
If you still need help, Schedule a Tutor.