The Difference Between Relative and Absolute References in Excel

relative absolute references excel

Is it important to know the difference between relative and absolute references in Excel? Absolutely! Pardon the pun, but I couldn’t resist it. But seriously, don’t skip this article. Knowing the difference between absolute and relative references in Excel can take you from Excel novice to master in not long at all.

The key difference between relative and absolute cells is that relative cell references move when you copy them, but absolute references do not. Absolute references are noted by a dollar sign in front of the cell reference. So a relative cell reference might look like =A1+A2 but an absolute reference might look like =$A$1+$A$2. (Actually, the formula can also look like =$A1+$A2 or =A$1+A$2, but let’s not get ahead of ourselves.)

Knowing this information, there are four types of references you should know. I recommend opening up your own blank Excel sheet to follow along. (Note that I’m not using the official names here, but I’m hoping “absolutely absolute” catches on.)

Relative References

No dollar signs at all! If you copy this bad boy from place to place, the formula will move with it. So if you type =A1+A2 into cell A3, then copy and paste that formula into cell B3, it will automatically change the formula to =B1+B2.

Absolutely Absolute

All dollar signs, baby. If you type =$A$1+$A$2 into cell A3, then copy and paste that formula into cell B3, it will stay =$A$1+$A$2. Amazing!

Absolute Columns

Here’s where it gets crazy. If you type =$A1+$A2 into cell A3, then copy and paste that formula into cell B3, it will stay =$A1+$A2. But if you copy and paste that formula into A4, then it becomes =$A2+$A3. That’s because the column references are absolute, but the row references aren’t. When I get confused by this, I think about it as if I’m “freezing” the columns so that my cell always references column A, no matter where I copy and paste it or drag it down.

Absolute Rows

Conversely, you can make the rows but not the columns absolute. If you type =A$1+A$2 into cell A3, then copy and paste that formula into cell B3, you’ll get =B$1+B$2. However, if you copy and paste that formula into A4, it stays =A$1+A$2 because its row references are absolute.

One more quick tip before you go: on your keyboard, the shortcut to making cells absolute is to press F4 while editing in the formula bar. This allows you to cycle through the different types of absolute references if you press it multiple times while typing the formula. Try it!

I hope this article has made you a master of absolute and relative references. If you want some more Excel tips, keep reading & Magoosh also offers Master Excel: Beginner to Advanced to improve your Excel skills. If you have any questions, post them below!

One Response to The Difference Between Relative and Absolute References in Excel

  1. VIJAY July 4, 2019 at 6:38 am #

    Really very help full for me


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!