Updated: Feb 4
Previously we finished with Data Validation, if you have any questions about that, do not hesitate to ask in the comments. This time we will be doing the Name Range.
To practice Excel you need a data sample. Use this website to generate fake data of any fields and length you wish. https://mockaroo.com/
I have made a data of Last Names, First Names, Genders, etc., who work in ABC Corp.
Where to find it?
You can find the Name Range under the “Formula” Tab here:
What is Name Range?
As the name suggests, it is for naming a range. You can name a whole column of a table, 2 or more columns of any range. We will see what are different ways of naming a range and how is this beneficial.
A named range is just a human-understandable name for a range of cells in Excel. By using the name range in excel, you can make your formulas much simpler to comprehend better. You can assign a name for a range in an excel sheet for a function, for a constant, or a table data.
Why you should be using named ranges in Excel?
3 reasons to use named ranges in Excel
Data Validation: Using a named range to create a dropdown list in a cell makes data entry easier and cleaner.
Formulas: A named range is the best choice when using an Absolute Cell Reference ($A$1) to refer your formula to the same cell (no matter where it's copied).
As our tutorial name is “Learn Excel as you go”, I will try to explain any of the new terms appearing in explaining the main topic.
As I have explained in one of the previous parts that you can reference a cell by entering its name, e.g., A2, C21, D212, etc., These types of references are called “Relative References”. Why? Because when you drag or copy the cell containing a reference, the reference automatically changes according to the jump you have made in copying or dragging. For example, we have written =sum(c2:i2) in the Total column here:
When you drag this cell, after pressing Enter, downwards, the value of C2:i2 will change accordingly as you are going down to C3:i3, C4:i4, and so on.
This relative reference has got its benefits. But what if you want that this cell reference should not change wherever you paste this formula or wherever you drag? Here you will use absolute reference.
When you put a “$” sign in front of either column reference or the row reference, that value will not change and thus is called the absolute reference.
Like in the above image, we have calculated the Average percentage of the whole class and we want it somewhere on that sheet or elsewhere on another sheet. If we do not use an absolute reference, copying this cell value will change whenever we change our paste range. Therefore, we will use an absolute reference, in this case as, $k$10. Now if we write =$k$10 in a cell, it will always put the value from K10, even if we drag that cell downward.
Therefore, both relative and absolute references have their uses.
Coming back to Name Range
As advised before, always convert any of your data into a table. It will be much beneficial if you are using a named range.
Now the above image is showing our data in a Table. We want to make a named range of the last names. So, what we will do is to click on the Formula tab and then Name Manager:
After clicking on New, you will see this:
Name your range as you like, leave the scope as Workbook so as you can use this name for the whole workbook. Write a comment in the comment box (optional), if you want yourself to remember why have you named this range or for what. Then in the refers to box, you have to write the range, which will be included within this name.
As our data is already in the table so we will use Table format, i.e., “Table Name”, then a “[“, “Column name”, then end bracket “]”. Like this, =employees[LastName]. That’s it.
Instead of using table format reference, you can directly use the range reference. Let me do both ways.
Now in the below image, you can see both the name ranges:
If you click on each of them, you can see below in the “Refers to” box what value they carry:
You can use any one of them and the result will not be different. But, what if I put one more row of data after the 500th person in my Table:
Now open the Name Range Dialogue Box, and click on each Name Range and see the difference between the Table Format Reference and the Simple Cell Reference.
If it were not for the table, Row 502 might not have been automatically enclosed by the name ranges. Each time you enter new data, you have to update the name range manually. It is just because of the data in a table, that you don’t have to worry about the update of the name range.
Using the Name Range
Wherever you require this name range, you can use it in your workbook by just entering this name of the range, i.e., “LastName”, in our case, and Excel will recognize the whole name range.
You can use it in any formula or just for reference, etc.
When your data is huge and the reference values are high, along with ever-changing data, always use Name Range to rememberable names to your specific ranges.
In the upcoming tutorials, I may be using Name Range a lot to capture or reference a range to be used in a formula. You will find it is much easy, scientific, and helpful to use named ranges rather than to use simple cell references all the time.
One point of mentioning here is that within the name range, absolute referencing will be used all the time.
Any Questions will be welcomed.