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