Publish date:
Updated on

Absolute vs. Relative Formulas in Excel

Learn about the difference between absolute and relative formulas in this Howcast Excel tutorial with expert Shir Moscovitz.

Transcript

Hi, my name is Shir and I'm the founder and CEO of shirconsulting.com where we focus on converting the existing data from your business into massive savings and extraordinary profits. Today we're going to learn the basics of Excel. Let's get started.

Absolute vs. Relative CELL reference - What is it and why is it important? Well first off a lot of people get this wrong so pay close attention so that you are the smart kid on the block who knows what's going on. A reference to a CELL can either be Relative or Absolute. So what that means is - as we see here in Net Salary it is referring to F2 which is this CELL right here minus the taxes right here which is G2, G2. That's a Relative reference. So when I take that and I copy it over and I put it down over here the Relative reference is carried over. So now in this CELL it now refers to the two CELLS to the left of where I Pasted not to these two that were up here.

If I were to instead make this an Absolute reference which I can do by simply adding a "$" in front of both the COLUMN letter and the ROW number just like that and the same thing over here. I'm going to anchor those or make those Absolute references, hit ENTER to make it take effect, CTRL+C to COPY it, go down here to CTRL+V or PASTE and notice that the value is still the same because it is still referencing the same exact CELL. And no matter where I COPY and PASTE this to it will always reference those CELLS. I'm just going to expand it so you can see what's going on. Same value because it's the same FORMULA at all times.

So let's go through an example of saving a lot of time using the correct principles of Absolute vs. Relative reference. So here we have a list of Gross Salary. Now if we wanted to do this incorrectly, I could simply take the value from here - the Gross Salary - and multiple it by the Tax Rate which is down here and then hit ENTER. So this happens to be correct because it's referencing the right spots. As soon as I take this and COPY and PASTE it down, it doesn't work and it doesn't work because now it is referencing some CELL down here which is not what I wanted
and some CELL over here which is what I wanted.

So instead I'm going to delete these because these are wrong. I'm going to anchor or make an Absolute reference out of the Tax Rate which is B12 - also a great vitamin. Let's add a currency symbol here and here to anchor that spot. And now when I COPY and PASTE it down, it will stay exactly where it needs to. Each of these Formulas reference the same Tax Rate while still relatively moving down to match the right Gross Salary. With this one, I need only a Relative reference. So I'm going to take the Gross Salary minus the Taxes and I need a Relative reference so that it goes all the way down and COPIES each Formula Relative to the right spot. And that's the difference between Absolute and Relative CELL reference. It's a really small change that makes a huge difference.

Popular Categories