How to Cell Reference in Excel

Hi everyone, my name is Kevin. Today we are going to look at how to do both absolute and relative references in Microsoft Excel. If this sounds like a very exciting topic, well hey, you came to the right place. Hey, all joking aside, relative and absolute references in Microsoft Excel are really foundational knowledge to have. Especially as you start launching into creating formulas in Microsoft Excel, being able to do a relative reference or an absolute reference is really just foundational knowledge. As full disclosure before we jump into this, I'm supposed to say this in any video where I talk about Microsoft products, I work at Microsoft as a full-time employee. Luckily for you today, I'm going to walk you through step-by-step how to use absolute and relative cell references, so you're going to be an expert by the end of this. All right, well enough talk, why don't we jump into it. Here I am on my desktop and I have an Excel sheet open here. This is the latest and greatest version of Excel that comes with Office 365. However, if you have an older version of Excel, don't despair. Absolute and relative cell references have been around for a long time and you'll be able to do this as well. You'll see here, just to orient you to the data that I have on this sheet, I have a whole bunch of different items here. Let's say that I had a YouTube channel. This is some stuff that I might want to buy. You might want a light, a camera, a microphone, a computer, and I have a different quantity of each item. Maybe I have two lights, three cameras, if I want different camera angles of myself, maybe I have different microphones, and so forth, and each one carries a cost. What I want to do today is I want to calculate the total cost of these items, and I also want to calculate how much sales tax these items cost. I can use just a simple formula to do this, and I'll use this to demonstrate what an absolute reference is and what a relative reference is. Let's go ahead and get started here with the formulas. To calculate the total cost of my lights, it's a simple formula, but I basically want to say two times 105. Let's go ahead and enter a formula. I'll say equals two, and I'm going to multiply that by 105, and then I'll hit enter. It says that it's $210 for my two lights, and that's correct. Now, what it's doing is you'll see that this is a relative reference. Let me exactly show what that means. Here, basically, what I programmed it to say is I want to use cell B2 and cell C2, so the two cells to the left of this one. Now, if I take this formula, and what I'm going to do is I'm going to copy this same formula down. Here, you'll see the same formula copied down, but instead of looking at B2 and C2, if I click in this next one, now it's doing B3 times C3. If I jump down to the next one, it does B4 times C4, and then the last one, B5 times C5. What's happening here? Well, I took this formula, and as I move the formula down one row, it moves everything down one row. These two cells are now relative to this one. These are the left ones, and then the same with this, and the same with that. That's a relative cell reference. As I move the formula, the cells within the calculation are also moving by the same amount. Because I moved this down by one row, it also moved all the items that I'm calculating in the formula down by one row, and it does it for every single one. Here, I moved it down to B5, C5, and it also moved it down by that same amount. A relative formula is not just limited to going down rows. It also applies to columns. Here, I have the same exact tables, what I have above, and I want to do the same type of calculation, but instead of having my data go down by rows here, they go across by columns. What I'm going to do is I'll do the same exact thing here, where I do two, and then I'm going to multiply it by 105, and that's the total cost of my light. Here, it's 210, and I'm going to drag the formula across. What you've noticed here is instead of adjusting the row, because the row hasn't changed as I move my formula across, here I stay in row 11, but you'll see what changes is the column that I'm in. Here, as I move across, you'll see that all the formulas adjust. Here, in my first cell, I'm calculating B9 times B10. If I go over to column C now, you'll notice that it automatically updates the column. What it does is the formula updates as I move it across. It's a nice thing, and it makes it really easy to drag and drop your formula to calculate against a lot of data. Now, what we're going to do is now let's see what is an absolute cell reference. Here, I'm going to come in, and I want to calculate the sales tax for each of these items. Here, you'll see the sales tax is 10%. What I can do is I'm going to say, okay, let's take the total cost of 210, and what I'm going to do is I'm going to multiply that by the sales tax of 10%, and then I'm going to hit enter. Here, it's $21. 10% of 210 is 21. Perfect. Works just as expected. Now, what I'm going to do is I'm going to drag it down just like I did with the total cost, and here I'll just drag it all the way down, but here you see it didn't multiply 10% by 3,600, and so why is that? Well, here I'm going to click into it, this formula, so it's taking the 210 times 10%, but now as I drag the formula or the cell and the formula down one row, what it does is it moves. It's looking at relative references, and it also moves this down by one row, and it moves this down by one row, but I don't want to move this down, the sales tax down by one row because I simply say, well, the sales tax is 10%, and that's going to stay in that position, so this is where an absolute reference comes into play, and the way you could apply an absolute reference is I basically want to say, hey, when I'm looking at the sales tax, don't change the row, and the way you can do that is you simply insert a dollar sign here, so I'm going to put it in front of the row because I want that row to be fixed, so I'm going to type in a dollar sign, and then I'm going to hit enter, and now what I can do is I can drag this formula all the way down and you'll see that it's now kept that sales tax in the same position, so I'm going to click into this bottom one here, and you'll see that here for the total cost, it moved that down as I was moving down my sales tax calculation because that's a relative reference. However, here for the sales tax, I basically locked that in position by putting this dollar sign in front of the one. Now what I'll do is I'm going to do the same formula down here where I type in equals, we'll do the total cost, and then I'll multiply it by the sales tax, and here you'll see the same problem as I pull it across. By default, it's doing a relative reference there, but I want to lock, in this case, the column since I'm moving it to the side, and so what I'll do is I'm going to put a dollar sign in front of, let's see, this should be H, and I'm going to put a dollar sign in front of H. I'll put it in front of this one as well, and what this will do now, so let me pull my formula all the way across, so what it's done now is it's locked the column to this one as I move my formula across. Now what I can also do is I could put a dollar sign in front of both of these, and this locks both the column and the row, and so I could copy this down, and so this way neither will change, and it'll always point at this cell regardless of, you know, where else I put this formula. Now a quick trick that I want to show, what you can also do is, let's say that I didn't have the dollar signs in there, this is a nice little shortcut key that you can apply, so you simply click on this H1, so you click there, and then you press the F4 key, so function 4, and you press that, and it toggles on dollar signs in front of both the column and the row, and if I click it again, it'll just do it on the row. If I click it again, it'll just do it on the column. I click it again, it goes away, and I'm going to hit F4 again, and that'll apply the dollar signs to each one. Now as I've done formulas in Microsoft Excel, I use absolute references all the time. It's extremely helpful, especially if you have some cell that you want to refer back to, and that's not going to change no matter where you put your formula. Absolute formula, or absolute references are great for that. All right, well there you have it. That's how you use both relative and also absolute references in Microsoft Excel. I hope that you found it to be pretty easy. Once you understand what it means, relative is basically as you move your as you change the cell that the formula is in, the formula adjusts based on where the new cell is. Absolute references are always the same regardless of where you place that formula in your sheet. If you're able to use absolute and also relative references now in your Excel spreadsheet, please give this video a thumbs up. If you want to see more videos like this, hit that subscribe button. That way you'll get a notification anytime new content like this comes out. If you have any other ideas or any other videos that you want to see me cover in the future, leave a comment down below. I'll take a look and I'll add it to my list of videos to create in the future. All right, well thanks a lot for tuning in. Hopefully this helped, and hey, I'll see you next time. Bye.