The Difference Between Relative and Absolute Excel Cell Reference

One of the bigger challenges in learning Excel is understanding Excel cell reference — or the difference between Excel relative cell reference and Excel absolute cell reference.

excel cell reference, excel relative cell reference - magoosh

Image by pathdoc

The Excel cell reference you are likely used to and already using is an Excel relative cell reference, so let’s take a look at an example.

Excel Relative Cell Reference

Excel cell reference - magoosh

In the spreadsheet above, we are totaling the numbers from Round 1 and storing the sum in cell B7. The formula for cell B7 is:

B7: =SUM(B2:B6)

Next, we are applying a multiplier to our total for Round 1, and you will find this multiplier value stored in cell A2. The formula for our new total with multiplier in cell B8 is:

B8: =B7*A2

We can see the Excel relative cell reference in action when we compute the same totals for Rounds 2 and 3. We will start by copying the formula from cell B7 to C7 and D7. Here are the steps to do this:

  1. Click in cell B7. Press Ctrl + C on your keyboard or right click on the cell with your mouse and choose Copy.
  2. Click in cell C7. Press Ctrl + V on your keyboard or right click on the cell with your mouse and choose Paste.
  3. Click in cell D7. Press Ctrl + V on your keyboard or right click on the cell with your mouse and choose Paste.

If you did this correctly, the formulas for cells B7, C7, and D7 will look like this:

B7: =SUM(B2:B6)
C7: =SUM(C2:C6)
D7: =SUM(D2:D6)

Excel cell reference, excel relative cell reference, formulas - magoosh

Even though we copy and pasted the formula from cell B7, the cell references in our SUM formula in cells C7 and D7 were automatically updated. In this situation, this is exactly what we wanted!

Excel Absolute Cell Reference

To demonstrate the need for absolute cell references, we will repeat the copy and paste process for the “With Multiplier” row. Here are the steps to do this:

  1. Click in cell B8. Press Ctrl + C on your keyboard or right click on the cell with your mouse and choose Copy.
  2. Click in cell C8. Press Ctrl + V on your keyboard or right click on the cell with your mouse and choose Paste.
  3. Click in cell D8. Press Ctrl + V on your keyboard or right click on the cell with your mouse and choose Paste.

If you did this correctly, the formulas for cells B8, C8, and D8 will look like this:

B8: =B7*A2
C8: =C7*B2
D8: =D7*C2

Excel cell reference - magoosh

In this case, this is not the result we desired because in cells C8 and D8, Excel changed our A2 cell reference to B2 and C2. But, we need the cell reference for the multiplier to be A2 in all three cells.

This is where the Excel absolute reference comes in. Absolute cell references are defined by using the dollar sign ($) character in front of the column and/or row you do not want Excel to update when copying and pasting cell formulas.

Let’s change our formula in cell B8 to use an absolute cell reference for cell A2. The new formula looks like this:

B8: =B7*$A$2

Now, if you repeat the copy and paste steps above, the formulas for cells B8, C8, and D8 will look like this:

B8: =B7*$A$2
C8: =C7*$A$2
D8: =D7*$A$2

Excel cell reference, absolute references - magoosh

We now have a mixture of relative and absolute references in our formula. The first cell reference is relative, so Excel will update the cell reference as we copy and paste. The second cell reference, $A$2, is absolute so Excel will keep the reference to cell A2 as we copy and paste.

As you work more with Excel, you will learn that the majority of your cell references will be relative. You will also recognize when you need to switch a relative cell reference to an absolute cell reference.

Check out our Excel Lesson Videos to learn more about using relative and absolute cell references in Excel.

One Response to The Difference Between Relative and Absolute Excel Cell Reference

  1. Michelle October 3, 2018 at 8:23 am #

    Fantastic!!


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!