You can make your formulas easier to comprehend by assigning names to group of cells, and then using those named ranges in your formulas.
- [Instructor] So far in this course, all the formula's that I've shown you use cell references such as A3 or the range A2:A5 to designate the cells that will be summarized in a formula. Those references work just fine, but it can make it hard to understand exactly which data your formula summarizes. You can make your formulas easier to read by assigning names to groups of cells, and then using those named ranges in your formulas. I'll show you how to create named ranges in this movie.
My sample file is the Names workbook and you can find it in the chapter three folder of the exercise files collection. This workbook has two worksheets, cities and multiple names. I'm starting on the cities worksheet. Let's say that I wanted to create a named range for revenue during the year 2014. So I have four cities, Atlanta, Chicago, Portland, and New York, for each of my three years, 2014 through 2016, and the revenue here. So the revenue that I'm interested in for 2014 would be in cells C2 through C5.
I'll go ahead and select those cells now. And the quickest way to create a named range is to just go to the name box, which is at the top left corner, just above the top left corner of the worksheet. And then type the name. The name of a named range must start with a letter or an underscore character, and you can't have any special characters, such as asterisks or pound signs, except for the underscore. And there are no spaces allowed. So I'll say Revenue2014, and enter.
So now I have those cells selected. If I click away, then the name box displays the active cell, which is A2. If I want to select cells in that range, and the named range I just created, I can go to the name box, click the down arrow, and there I see Revenue2014. So I'll click that, and I've selected my range. I can also create a formula using the range. So I'll click in cell E2, although any blank cell will do.
And I'll type = and if I want to find the sum of those values, I'll type SUM, then a left parenthesis, and now the name of my range, which is Revenue. And you see here, it's available in the formula auto-complete list, Revenue2014. Most of your named ranges will appear there. The only exception is a dynamic named range and I describe that elsewhere in this chapter. Revenue2014 is highlighted so I'll press the tab key and it's also selected now.
So I have sum of revenue 14, and a right parenthesis, and enter, and there's the sum. And I won't bother to change the formatting. Another way to create a named range is to use the new name dialog box. To do that, go to the Formulas tab of the ribbon. And then in the Defined Names group, click the Define Name button. Doing so displays the new name dialog box, so I'll go ahead and type a name for this. And I'll call it, very originally, Revenue2015.
Now I need to choose the scope. And scope tells Excel where you can use this named range and formulas. Right now it is set to workbook, but I could also apply it to either cities or multiple names as worksheets. So if I click the list boxes down near here, you can see Workbook and then worksheet names here. I have never changed from workbook and I can't think of a good reason why to do it. I just wanted to let you know that the option is there. If you want, you can click in the comment box and add a comment, although in this case, Revenue2015 is perfectly acceptable and explanatory so I'll leave comment blank.
Now I need to identify the cells that the named range will refer to. So I currently have cities, E3, which is the selected cell. And I want it to be cells C6 through C9. So in the New Name dialog box, I will click the collapse dialog box button. Next to Refers to. And then I'll go over and select cells C6 through C9 for the year 2015, then click the expand dialog box button.
Everything looks good. C6 through C9, yep, so I'll click OK and I have created my named range. To see it, I'll go back to the name box, click the down arrow, and I see Revenue2014 and Revenue2015. So I know I did everything correct. And I'll press escape. If you want to create multiple names at one time, you can do that. I'll switch to the MultipleNames worksheet. So I'll click the MultipleNames sheet tab. This worksheet has the same data as I had on the cities worksheet, except instead of having it in a table, it is laid out in a cross tab.
So I have Atlanta in 2014, 2015, and 2016. Same for Chicago, Portland, and New York. What I'd like to do is to create a series of named ranges from selected data. So in other words, instead of creating one for Atlanta, Chicago, Portland, and New York, I want to do it all at the same time. My headers, or what I want for the name of the named ranges, are in column A. So I have Atlanta, Chicago, Portland, and New York, so we'll see how Excel handles New York, which has a space in the city name, when you're not supposed to have that in a named range's name.
So I'll select cells A3 through D6 and then I'm still on the Formulas tab, so I'll go to Create from Selection, which is in the Defined Names group. I'll click that button and I get the Create Names from Selection dialog box. The names are in fact in the left column so I'll click OK, and everything appears to have worked. I can verify that by going to the name box and clicking the down arrow. And I have Atlanta, Chicago, and New_York, and the space was replaced with an underscore, and Portland.
So everything looks great. Named ranges make your formulas much easier to understand. Take the time to create a few of them and start using them in your formulas. You'll be glad you did.
- Using operators
- Use cell references in formulas
- Formulas: SUM, AVERAGE, MIN, MAX, MEDIAN, and MODE
- Counting cells
- Summarizing cells conditionally
- Rounding cell values up and down
- Working with list data
- Finding data using VLOOKUP formulas
- Connecting to an external data source
- Cleaning up imported data
- Validating data using rules
- Tracing precedents and dependents
- Identifying errors
- Finding target values using Goal Seek