Hi, this is Wayne again with a topic “Benefits of Naming Cells in Excel”.
In this tutorial, I’m gon na show you some of the benefits that come from naming cells in Excel and to show this I’ve set up a simple health tracker, or at least the beginnings of one. Let’S say I’m working as a personal trainer, and I would like to help Jason Smith here to track his health and his improvement, so I’ve got Jason’s name here, his age and his height in inches. So now all I really need to do is write a formula and put it here in this cell and the formula will do most of the work for me, calculating Jason’s, BMI or body mass index. I have here a description of the formula that we need to create in Excel and that is Jason’s weight, multiplied by 703, at least if you’re, using inches and pounds and then divide that by a Jason’s, height squared and the height again would be in inches.
So that’s what we need to recreate in Excel in this particular cell. So let’s do it step. One is just to click on the cell: tap the equals sign on the keyboard Jason’s weight for week.
One of this tracker is going to be right here. So I just click there: multiplied by the asterisk represents multiplied by 703 and then I’m gon na divide that by Jason’s height, so that’s right here, 75 I’ll just click there on cell b4 and then to put in squared. You need to add this little symbol here. For me, it’s shift six.
If I hold shift and tap six, it puts that symbol in and then you put two 4 squared or 3 4 cubed etc. Now, if I tap enter on the keyboard, it returns a number. Now, let’s put a weight in there to see if it worked. Let’S say: 265 pounds 33.1 BMI. So I believe that that’s correct but watch now what happens when I go to week? Two, let’s say: Jason loses a couple of pounds and I would like to use the autofill handle here in the lower right corner of the cell to copy this formula that calculated the BMI I’d like to just copy it down here.
So I don’t have to read so I’ll use that autofill handle it copies the formula, but look it didn’t work now. The reason why is because, when you use the autofill handle it tries to in a smart way change the numbers in your formula, so here in cell c7, notice that the formula is looking to be for for the height, but when I dragged it down look now, It’S looking for cell b5! Well that just gives it this instead of a number, and so that’s why I get this error message. So all of this can be solved and also it’ll, be easier for me to really understand my formula if I just name certain cells, so here in b4, I’m gon na click on 75, that’s Jason’s height in inches and with that selected. If you look here in the upper left corner, there’s something called the name box and right now it shows b4 as the name of that cell, but I can just highlight that and I can change it to be called height now, when I was done typing height. I tapped enter on the keyboard.
That’S very important, a really common mistake. People make when they’re trying to do this is they’ll type, the name and then forget to tap enter and they just click away, and that way it does not record the name that you want. That cell to be called – and it can really throw them off later another way to do the same thing, to name a cell instead of going here, you can click on a cell and then go to the formulas. Tab and they’re in the defined names group you’ll find define name so I’ll click on that and it brings up this pop-up, and this accomplishes the same thing as what I did here in the upper left. But it does give me some additional options: notice that Excel automatically figured out what this represents.
It represents the age of Jason Smith, so it put in the word age for me next, what’s the scope that I’m talking about here? Do I want to create this name for the entire workbook, for every spreadsheet that I put in this workbook, or do I want to limit it just to the health tracker sheet? In this case, it doesn’t matter either way, I guess I’ll leave it as workbook and then underneath that I can type in comment. So this is the age of the client and then I’ll click, OK and now that cell is also named. So I have height, and I have age now to make sure that this worked. You could go here to the upper left to the name box and click on the arrow that appears there. When you click it brings up any named cells that you have.
You can also just go here again to the formulas: tab define names, group and click name manager, and it brings up all of the names that you’ve established in your workbook or worksheet. Now a couple of things to be aware of, when you name a cell in Excel. First of all, the name cannot begin with a number or a symbol. It must begin with a letter of the alphabet so watch out. For that, the other thing to avoid is putting a space in the name. So if I try to edit this name, so I select age go to edit and if I try to put in age of Jason, that wouldn’t be a good idea anyway, because I want to use this for other people that are training.
But if I click OK, look it doesn’t allow it because I put spaces in the name. If you really want to have spaces, just hold shift and tap the minus sign on the keyboard, and that will give you an underscore. But in most cases it doesn’t really matter that much and you can just type it all as one word so I’ll click OK to get out of there and then the final thing to watch out for is you can’t use alphanumeric names, for example, b3 or f10. Why can’t you do that? Because there already is a cell named f10 or b3, and so it’s too confusing so try to avoid letters and numbers together in your names. Don’T use blank spaces and the name must start with a letter. Ok, I’m gon na close that out and let’s try the formula again, but this time instead of using b4, I’m just going to use the name for B 4, which is height so I’ll, delete what I already have in this space click away and then click back. So this cell equals Jason’s weight, which is here multiplied by 703 divided by and then here. At this point I could click on 75. If I wanted to – and it would put in the word height – see that I’m going to delete that, though, to show you the other options that we have this time instead of clicking on the height cell, I’m just gon na type. The word height now, as I type it notice, that my named cell pops up here as an option and that’s what I want so I’ll double click on it. And then I need to still put that up. Arrow shift 6 and then the number to 4 squared, and then I tap return on the keyboard now that I’ve done it this way, with a name to sell watch what happens when I use the autofill handle, I pull it down and it works the reason why It works when you name the cell is because named cells create absolute references. So when I use the autofill handle and drag it down, it tries to adjust based on the cell and when I simply called this b4, it was changing it to become B 5 and then B 6. As I dragged down the page well, it can’t do that if the cell is named height, it just always is an absolute reference to this cell and to Jason’s height, so that solved one of the problems that I have in this spreadsheet and I can just click And drag this down all the way down to the bottom, and now, as the week’s go by and Jason loses a little bit of weight each week, we can track that and it’s automatically done through this formula that references a named cell now sometimes people name cells.
Simply for their own ease of use and clarity. So, for example, if for some reason I wanted to divide Jason’s age by his height, I don’t know why I would, but I could just type in equals, and then I could just type in the number 46 divided by 75 or I could, instead of using name Cells just click on B 3, divided by B 4, but because I’ve named those cells, all I have to do is type in age divided by height, and you can see it suggests height here. Also one other thing to be aware of. If you tap f3 on the keyboard, you get a pop-up with a list of all of your named cells, and you can just click on the one that you want to use and click OK, but either way because of these named cells in my head, I’m thinking In real terms, I’m thinking this cell is equal to age divided by height, instead of having to think about B 3 divided by B 4. So many people find that easier to think about and to use in Excel a couple of final things to be aware of.
When naming cells number one, you can also name ranges. So, for example, I could click here on a one and then drag down to the lower right corner of my data, which really at this point, ends here in C 59 I’ll just release the mouse button and then I can go up here again: formulas tab define Names group and go to define name and I’ll, just click that and I’ll call this health tracker. So I am naming everything that’s important on this spreadsheet.
For my health tracker, I’m naming it health underscore tracker and then I could put in some comments and then click. Ok, so now that range is also named and I could reference it in a formula if it made sense to do that some health tracker and then enter so it just added up all that stuff. That doesn’t make any sense, but it is possible to name a range and then the last thing about it. That’S kind of cool is: I can go here in the upper left corner to the name: box, click, the arrow and go down to health tracker. That highlights everything, that’s important and then, let’s say I’d like to print this and hand it to Jason. As a report, all I have to do is go to file and then I’ll go to print and look what it’s trying to print it’s trying to print the whole spreadsheet. So instead I’m gon na click here and go down and choose print selection, because I had named that range. It just made it much easier to print the exact right selection.
I just click choose health tracker and everything is selected that I want to print and then I go print it. So I hope you see the benefits of from time to time, naming a cell or naming a range thanks for watching. I hope you found this tutorial to be helpful if you did. Please click the like button below and consider connecting with me on my social media accounts like Facebook, Pinterest and Twitter, and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and when you do subscribe, please click the bell that way.
You’Ll be notified whenever I post another video and watch for another video from me at least every Monday. If you want to support my channel become a supporter of mine through my patreon account and you’ll see a link to that in the description below .