Hi, this is Wayne again with a topic “Excel Relative vs Absolute Cell References”.
In this article, i will explain and demonstrate the differences between relative cell references in excel and absolute cell references in excel, and this is a very key important concept to understand when using formulas and functions in excel. And you can see the spreadsheet that i’m using for this video is a mileage reimbursement sheet this tracks, each of the employees of a company and how many miles they drive in their own personal cars, but doing company business and to be fair to those employees. The company is going to reimburse their mileage, you can see it goes from january through june. Okay. First, let’s look at relative cell references, and this really is the most common type of cell references that you’ll need.
So let’s say the company reimburses mileage twice a year. So it’s halfway through the year and now we need to calculate the total mileage for the first half of the year to do that for katina steadman. Just click on the cell to the right of her monthly mileage totals and then just type equals on the keyboard sum left parenthesis, and then i just need to describe or click and drag to show the range that i would like to add up that. I would like to sum i’ll just click and drag to highlight that range.
I should put in a right parenthesis, but it’s not necessary in this case and then i’ll tap enter on the keyboard and there is the total mileage that katina has driven in her own personal vehicles for company business. If you’ve watched my other excel videos, you know that i don’t have to recreate that formula again for epiphania or brook or any of these other fine people. Instead. All i have to do is click on my first formula now it looks like i clicked on the number 721, but that’s not true.
This cell does not contain the number 721. It contains a formula – and you can see that here in the formula bar and that formula returns the results 721 so clicking on that cell h3. In this case, i’m going to go to the lower right corner and click and hold the click on the auto fill handle, or sometimes it’s called the fill handle.
It’S that little square in the lower right corner of the cell. I click and hold the click and then drag down to copy that formula all the way down and there you go. You can see that it automatically adjusted the formula the autofill handle.
When i dragged it, it was trying to copy the contents of this cell and it succeeded, but again this cell doesn’t have 721 in it. It has this formula upon copying and pasting that formula all the way down in column h. Look what excel did it automatically adjusted the b3 through g3 part of the formula it changed it to b4 through g4 and here b5 through g5? I can also double click to show you that b6 through g6.
So again it adjusted the cell references, the original formula said b3 through g3, but it’s adjusting and changing it from b3 through g3 to b8 through g8 or b13 through g13. All the way down to b22 through g22. Now that is the default when you use the autofill handle, it will automatically make those adjustments for you when you use the autofill handle now. How is it doing that? Well, it’s using relative cell references when i clicked and dragged that autofill handle excel by default said. Okay, this formula is relative. The original formula is summing up the six cells to the left of the current cell.
So one two three four: five: six: it’s adding up all of those to produce the results so when i copied it using the auto fill handle because it’s a relative cell reference, the formula that was pasted in said, okay, i will now sum the six cells to The left of the current cell, one two three four five: six and it added those up and produced a different number from cell h3. So that’s the power and the beauty of relative cell references and you don’t really have to do anything special to specify that you want relative cell references. That’S the default whenever you use the autofill handle. So that’s exactly what i wanted and it’s looking good. So now, let’s look at absolute cell references and how those can be very important.
Let’S say we try the same kind of thing but with total amount owed. So let’s say i’m an accountant for this business and i need to figure out not just the total mileage that each employee drove, but how much the company owes that person and needs to pay them. So, let’s first, do it the wrong way using relative cell references. So i’ll tap equals to figure out how much the company owes katina.
In this case i need to multiply 721 and i’ll just click on h3, and then i need to multiply it by the current reimbursement rate for this company. Let’S say they reimburse 58 cents for every mile, driven so i’ll put in multiplied by which is just the asterisk symbol and then i’ll click here on the reimbursement rate and then tap enter okay, wonderful. It calculates the amount owed to katina she drove 721 miles. That calculates out to being 418.18 of mileage reimbursement. Now here comes the catch. It seems like that went beautifully, but when i click on the cell, that has the formula that i want to copy down to the other employees and i use the autofill handle and i pull that down watch what happens. I release and i get all sorts of error messages now, in addition to the error messages, i do get a valid number here, but i kind of doubt that the company is going to reimburse this person 435 000 for driving only a thousand miles. So, what’s going on here, let’s look at the formulas. If i double click on this formula, you can see what happened.
The original formula was multiplying h3 by i1, and that was correct. Now let me tap escape on the keyboard to go down and show you the second formula. H4, that’s correct, multiplied by i2! Excel is now trying to multiply by the words total owed.
Well, that doesn’t make any sense. Why did it drop down from the mileage reimbursement amount down to total owed? Well, the answer is very simple: whenever you use the autofill handle excel by default, is using relative cell references, and so just like in my total mileage example. Excel is adding 1 to the previous formula, so here the formula had i1. So when i clicked and dragged and used the autofill handle it shifted, i1 to being i2 and then i3, and so let’s look at this formula. This is another good example. H5.
That’S this cell and that’s correct. That’S exactly what i want multiplied by i three, so i one two three yikes: it’s multiplying the mileage that this person drove by the total ode to katina that doesn’t make any sense at all. It’S not even close to being correct. I’Ll tap escape on the keyboard and i’ll hold ctrl and tap z, a couple of times to undo that mistake and i’ll tap it one more time to undo my incorrect formula, even though it worked for katina, it was a failure for all the rest of the Employees, so let’s look at the proper way to calculate this in this formula. I want the cell reference for total mileage to be relative, but i want the cell reference for mileage reimbursement rate to be absolute. I want it to stay there, no matter what.
So how would i do that click on the cell, where i want to put the formula so i type equals, and now i could click here to select h3, but sometimes it’s easier just to type it so i’ll type h3 multiplied by which is the asterisk. And now i’ll type i and then instead of just typing one, i’m gon na type, i dollar sign one. If i don’t put that dollar sign in there, it will treat this cell reference i1 as being relative, and then, when i use the autofill handle, it will move away from the mileage reimbursement rate and i need it to stay there. And so, whenever you need a cell reference to stay, put, no matter what to make it absolute, what you do is you use an asterisk to tie that part of the cell reference down, you’re, locking it in place, so i’m locking in place the reference of one. Let’S try it now.
I tap enter on the keyboard, it returns the same results, but now, when i click and drag using the autofill handle to copy and paste the formula for all the rest of the employees, look it worked. Let’S examine the formulas. If i double click you can see it here.
You can also see it in the formula bar here toward the top. So there’s my original formula that i typed, let’s look at the next one h4 multiplied by. I dollar sign one. What about this person? H5, multiplied by i dollar sign one, so the h is changing h6 h7 h11, but the cell reference in the i column is staying, put it’s staying at one.
Why? Because i locked it down by using a dollar sign now. Another way to do the same thing is to use a dollar sign for both i and one. So i’m going to click here on in this case, cell i3 and i’ll go up to the formula bar.
I very well could have put a dollar sign in front of both the i and also dollar sign one tap enter, and if i copy that down now to the rest of the employees, it doesn’t look like it changed anything in reality. It didn’t change much, but if you look at each of these individual formulas, you see that there’s a dollar sign in front of both. So the only difference there is it locked in the cell reference it locked it in to column. I and row number one. So both are locked in place. It wasn’t necessary to do that, because i wasn’t dragging this number out of i right. I wasn’t dragging it over here to j or to h, so there wasn’t a need to use the dollar sign to lock the cell reference into column i, but it still produces good results.
So the bottom line is with this particular formula that i’ve written the h3 – that is a relative cell reference. It changes as i use the autofill handle and copy paste that formula down that h3 changes, h5, h9 etc, but the i1 part of the cell reference, that is an absolute cell reference and, in this case i’ve chosen to lock down both the i and the one. Those are both going to be riveted to this cell here i1, the more you use excel. The more you’ll see examples of when it’s useful or necessary to use absolute cell references thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow and subscribe and when you do subscribe, click the bell so you’ll be notified.
When i post another video, if you’d like to support my channel, you can support me through my patreon account and you’ll find a link to that in the description below also please consider buying channel merch and you’ll also find that below the video .