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

__Absolute References in Excel (06:27 mins)__|__Transcript____Absolute Reference Example Video (04:58 mins)__|__Transcript__

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

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

Figure 2

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

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

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

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.

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.

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.

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.

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

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

Figure 15

Figure 16

**Things to Remember**

- An absolute reference
- 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.