Join Chris Grover for an in-depth discussion in this video Naming cells and ranges, part of Learn Excel 2010: The Basics.
- View Offline
Which is easier to remember? C3, or tax rate? D12, or April? For most human beings, words are easier to remember than numbers or codes. Computers don't seem to care, but to accommodate the humans, Excel lets you name cells and ranges of cells. Then you can use those names in your formulas, instead of C3 and D12. So let's look at a practical application. Here we've got a shopping list with items and quantities, unit cost. We figure out the subtotal, then we apply a tax that's a percentage, and we figure out the total.
Well, the tax is an ideal thing to use a name for. I'll show you why. Here we're using the actual number 0.09, we multiply the subtotal by 0.09 to get our tax. And we could copy that all the way down, use 0.09 for each one of these items. The problem with that is, if the tax rate changes, then we have to change each and every single formula. You'll often run into things like this, there's a better way to do it and that's to reference this cell that's up here.
So to do that, we'd remove the number, and then we'd reference the cell. And we have to remember to make that an absolute reference, because it's just a single cell. And then we could copy that down our list here. And that's okay. If you look at this formula, though, it really doesn't say anything to you. You can't, to translate what's going on here, you have to go back and reference the cells. You have to see D4, oh yeah, that's the subtotal, and E2, okay, that's my tax rate. And then you understand what's going on. Another option is to name the cell Tax.
The way that you do that is you go over here, this is the name box. So you click in that, and then you just type in the name that you want to apply. Wanna call that Tax. And then in our formula, we can get rid of this absolute reference, and just use the word, use the name. You can see as I start typing, it shows up on my list here. I've got a couple of functions here. But down here, you see the little label. That's the symbol for a name, and you can see Tax. So I can just double-click on that, and now we have our new formula, and it references the Tax cell. And it's an absolute reference, so if I want to copy it down, it works for all of my formulas, and I can go in and look at these formulas, you can see each one of them uses the tax name in the formula.
And that's really handy, particularly handy for things like tax rates or any kind of rate that you apply regularly to a work sheet. Let's look at another example. Here we have household expenses, we've got months across the top, and we have the expenses on the left side here. I'm going to select the whole group of them, and I'm going to use a command in the formulas, Defined Names group. This is where you'd go to manage your names.
You can bring up a dialog box that'll help you do that. You can define the name for a specific thing. What we're going to use is Create Names From Selection. This is a really speedy way to apply names to the columns and to the rows. So I'm going to click this. A little box comes up asking me how I want to create my names. Well I want this top row to define the names of the columns, and I want the left column to define the names of the rows. So that's just exactly what I want, I'm going to click Okay.
And now we have names. You'll see that those names appear in our name box. So, if we want to look at April, I click on that. And it shows me a range of cells, and these cells have the name of April. If I go back to my list, I could choose Gas, over here, and it shows me the range of cells that have the name Gas. Once you do that, here's something that's kind of interesting. If I press = to start a formula, and I could type in April, as I start to type in April, it shows me, prompts me with April, and I can put a space in there. And then, you can see it shows the selection box around April. Now, if I type in gas, it shows the selection box around gas. And if I click Enter, it shows me the intersection of April and gas, and I can use that in a value.
So it gives you a really handy way that you can look up something, and you can use that in a formula. We could also use the whole range in a formula. So suppose we wanted to find out the total of our phone bill for entire year. We could go =, use the sum function. And then in here, we could use the name, phone, and you can see it prompts us with the phone, so I can double-click that, close our function. And what it gives us is the sum for that phone range, and if we look over here, we can double check it, and there it is, and the value is correct. So, in this lesson, you saw how to name a cell or a range of cells. Then you saw how you can use the name in a formula. The lesson also demonstrated the speedy Create Names From Selection feature.
- Understanding Excel and its user interface
- Getting started with basic tasks
- Developing your spreadsheet
- Creating more complex formulas
- Making changes to your workbook
- Visualizing your data with color, charts, and graphics
- Analyzing data
- Printing and sharing your worksheet