Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you have a lot of cell ranges to deal with, you might find it easier to give them friendly names. For example, you might find it easier to deal with something called the sum of January than deal with the sum of B6:B9. But range names have a little quirk and we are going to look at that also. Well, over here we have regions that we are selling to and we have months January through June. Let's select just the numbers here in the January column and go up to the name box. You see the name box right now is at B6, because B6 is the first cell we are selecting.
So, click in the name box and type January and press Enter. Just click anywhere in the worksheet to deselect. Now, when we go back up to the name box, click that down arrow. There is January. Select it and January becomes selected. That's great. Let's do for February. Select February. Click under the name box, type February, press Enter. Again, you could deselect and click up here. Now, there January and February. Well, there is an easier way to do this. Let's select from the header of March down through the last number, this whole area.
Now, up here on the Ribbon bar go over to the Formulas tab. Over here, you see we have this group called Defined Names. Now click over here Create from Selection. Let's move this out of the way. The dialog box says okay, we'll create names. Where do you find the names to apply? Top row is selected by default, because it found these names in the top row. Click OK and again just deselect. Now, when we go up here, we can see oh! All right there is April and here is June. You notice this is an alphabetical order, not in location order.
So now that gives us the range names. Well, how we can use this? Let's go over here into January where we want to calculate the total for January. We'll use the SUM function and we'll try this manually. We'll say =sum, open the parenthesis. Now, instead of dragging down, we want to put in January. Now, if you know that the range name is called January, you can simply start typing it in. You may notice that it appears in this little box. So you could continue typing in January or you can press the Tab key to fill it in. Just press Enter or I'll press Ctrl+ Enter to enter it in and there's the sum.
Well, that's great. I'll just press the Tab key. Let's do this sort of a different way. Again, we'll type in the SUM function. Now, maybe you don't remember what all the range names are. This can very easily happen if you have a lot of range names. Well, we are still in the Formula tab over here and in this Defined Name section click where it says Use in Formula. Now, you can see all the range names and you can choose February. Again, just press Ctrl+ Enter and now you have February. Let me show you a third way to do this. I'll just hit the Right Arrow key.
For March, let's type =sum, open up the parenthesis, press the F3 key on your keyboard, and this brings up the Paste Name box. Now, just double-click March and it puts it in. Again, press Ctrl+Enter. Well, here's the quirk that range names have is they are always absolute references. Put your mouse pointer on the Auto Fill dot in the lower right-hand corner of that cell. When your mouse pointer becomes crosshair, just drag out to the end and look at what happens.
It makes the sum of each of these the sum of March. Well, it doesn't make a heck of a lot sense to have the total of April, May, and June the sum of March. So, we have to select this and delete this. If you are going to use range names, you really do have to type in the Sum function manually for each one. So, whether you find that easier or if you don't find that easier, I'll leave up to you. But there are times in Excel, especially when you're dealing with large amounts of data, that you really do want to use range names.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 87137 Viewers
80 Video lessons · 136251 Viewers
59 Video lessons · 54921 Viewers
52 Video lessons · 68801 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.