The people often say, “What’s in the Name”. we would learn the importance of the name in this article. Yes, In Excel we can define a name of the cell, range or in Formula. This is an incredible feature given by Microsoft in Excel however, a very few people know about it.
In day to day life, Names widely use to refer to people, things and place. For example, we cannot call a man with a dark complex having 5ft 7inch height, simply we call him by his name. Isn’t It?
In this blog post, not only we will learn “How to define a name but also we learn how can we use it in excel”.
Let’s not waste the time, just dive in the ocean of Excel and learn it.
Excel Names types-
In Microsoft, we can define name in two ways-
Defined Names- A name refers to a cell, a range of cell, constant value or formula. When we define a name for a range is called “ named range”.
Table name- we can define a Table name on a database by converting data in Table format (Ctrl + T)
How to define Excel name range-
1- Name Box-
- Select the range(A2: A5)
- Type a name in Name Box (Highlighted in below image)
- Enter
Voila! A new name range is created.
2- Define a name by using the “Define Name” option
- Select the cell (A2: A5)
- Formulas Tab
- Define Tab
- Type name in the name box
- Scope column select workbook by default
- We can specify any comment if needed
- Check the refers to, if needed otherwise it takes the range by default
- Ok
3- Define a Name range by using the Name Manager
- Select the range (A2: A5)
- Go to Formulas tab
- Name Manager (Ctrl+F3)
- New
- Ok
4- Define a constant name
move one more step forward, Excel allows us to define a name without the selection of any cell and that will be called “Constant Name Range”
5- Define a name in Formula
We can give a name to an excel formula. For example, if we have a stock calculation sheet and wanted to calculate the total amount. Let’s see how can we use it?
- Go Formulas Tab
- Define Name
- Type
- Define Name
- Define a formula E.g. (=sum (sheet1! $E: $E)-1)
Note: We have given a name to the entire column E, In the formula, sum function will take whole range except the heading because of -1.
The best part of this process is that when we add more data inside the table, the Sum function will increase the range and the total amount will calculate the value automatically. We can use this name range not only the current sheet but also in any of the existing sheets.
6- Define name with option “Create from selection”
If we have a database in such a manner that we need to define name to each row/column. Instead of define name one by one, we can define name in a quick second with the option “Create from selection”. Just follow the process-
- Select the database
- Go to Formulas->> Create from selection or (Ctrl+Shift+F3) my preference
- Select Row or column or both as per your requirement (Refer the screenshot below)
- Ok
As a result, Excel created name range as per the headings. We can check the name range, just select the range B19: B25, it will reflect the name “Jan” inside the Name Box beside of formula bar or Select the range B19: E19, It will reflect name “Sandy “inside the name box beside of formula bar.
Note: If there is any space between the words, space will replace with the sign “_” (Underscore).
Excel naming rules-
- Excel can not consider space or any special character during the define Name range.
- Any name can contain maximum of 255 character
- Any name can start with Letter, Underscore (_) or backslash (/). If we type any name apart from these, excel will not accept the window.
- We can not give a name like “A1”, “A1C”,” B1C1” etc.
- Excel name range is case sensitive for example “ City”, “city”, “CITY”
How to Edit, Delete the naming range-
To delete or edit naming range-
- Go to name manager or press(ctrl+F3)
- Click on Edit
- By clicking on this option we can change the name
- We can change the range as well
- Select the name range
- Click on the Delete option
- Apply the option “Yes”
- Ok
Example: For instance, If we need to find out the total amount for a specific city like “Delhi”, Normally we use formula-
Or, we can give meaningful names to the ranges and individual cells and supply those names to the formula-
This is how we can define a Name in Excel and can apply these in a smart way. Thank you so much for reading this blog, If you have any query or question feel free to write it down in the comment box. You can directly reach us through email or contact number which is mentioned on our website www.nurturetechacademy.in
Stay tuned and hope to see you in our next blog.
Happy Learning 😊