Excel Name Box (Table of Contents)
- How to Give Name in Name Box?
- How to Edit and Delete the Name of the Data Range?
- How to Use the Name Box in Excel?
Definition of Name Box in Excel
The box located to the left side of the formula bar which addresses the selected cell or group of cells in the spreadsheet is called Name box. In the below screenshot highlighted with a red color box is the Name box.
This Name box helps to address the group of cells with a name instead of addressing rows and columns combination. In this tutorial, we will cover how to create a Name box and how to use it while working with data.
How to Give Name in Name Box?
Consider a small example of student’s data like below. In the below screenshot Name box representing the selected cell A1.
Now we will select the student data alone from the table excluding header “Students”.
After selecting the data, go to the name box and type the name which you want to name the data range. Here I am giving the data range name as “Students”. After Inputting the name Press Enter Key, it will create the name.
Now, whenever we want to select the student’s data range we can select from the Name box drop down as below.
If we add the marks data of the students as below.
We already gave the name “students” to the column A data. Still, we can give the name to the combined data of students and marks as “Smarks”.
How to Edit and Delete the Name of the Data Range?
We have seen how to give a name to the data range. Now, we will see how to edit and delete the name.
#1 – Edit the Name of the Data Range
In case if we incorrectly input the name or want to change the name. We gave the name as “Smakrs” for the student data range incorrectly, which should be “Smarks”. Now, we will change the name from “Smakrs” to “Smarks”
Name box does not have the option to edit the given name we should change the name in “Name Manager” which is under “Formulas” menu. Click on the “Name Manager” to view the available names.
Click on the option “Edit” on the top.
So that we will get the below “Edit Name” box.
Edit the name to “Smark” as required.
In case, if we want to increase or decrease the range of cells we can change in “Refers to” option.
# 2 – Deleting the Name of the Range
It is as similar to how we edit the name. Click on the “Name Manager”. Select the name of the range which we want to delete. Click on the “Delete” option on the top.
This is how we will create, Edit and Delete the Name in the name box.
How to Use the Name Box in Excel?
Let us understand how to use the name box with a few examples.
Example #1 – Count Formula with Name Box
Suppose we want to count the number of students from the student’s table. We can use the count formula with the name of the range. In the below screenshot we input only the half name of the range then the system displays the name automatically. Select the name and close the formula.
Below you can see the output.
As the number of students is five it should display 5. Remember, it is not COUNT it is COUNTA formula.
Example #2 – SUM with the Name Box
Now we will see how to perform the SUM operation using the name feature. Our task is to sum the marks of all the students. In SUM formula we will give the range name “Smarks” which we created earlier instead of the range B2 to B6.
After using the formula output is shown below.
So, to replace the range address we can use the name of the range. The name range will also pop up if we input the first few letters of the name range.
Example 3 – VLOOKUP with Name Box
We have to perform VLOOKUP for Table B to find the marks from Table A.
Create a name “Sdata” for data in Table A as below.
Now input the VLOOKUP Formula in Table B.
Once you select the “lookup_value” as E3 give the table array as “Sdata” which is the name of data range from Table A. As the range has 2 columns input column index as 2 then input zero(for True).
Expand the formula to get marks for other students.
Example #4 – Excel Name for Constant
We can use the Excel name feature for creating constant also. We will see one example of this to understand in a better way. Consider a table having data of a number of hours of employees as below.
Charge Per hour will be 20 rupees. So, we will create a constant with the value 20. Click on the Name formulas menu.
Click on the Name Manager and the below window will appear.
Click on the “New” option and it will take you to the below screen.
Give the name as Rate 20 and in “Refers to” give constant value 20 as below and click on OK.
Now go to our table and input the formula for multiplication with the use of name constant. Start the formula with Equal symbol and select the number of hours option.
Add a Multiplication symbol.
Now give the constant name we created.
Select the Rate20 and Press Enter Key. It will multiply the number of hours with 20.
Drag the formula from Cell B2 to B5.
Things to Remember About Name Box in Excel
- While giving the name for a range make sure there should not be any spaces as it will not take if we input any spaces in between.
- Don’t include punctuations in the name.
- The name will be case sensitive hence it will be the same fact whether we give the name as “Hai” or “hai”. While calling the range you can use any case letters.
- The name should start with a letter or Backslash “\” or Underscore “_” . Other than these if the name starts with any other letter Excel will throw you an error.
- The name can be applicable at sheet level or workbook level, it depends on our selection while creating the name in name manager.
- The name should be unique as it will not allow duplicate values.
- The name can be a single character to Eg: “A”.
Recommended Articles
This is a guide to Name Box in Excel. Here we discuss how to use the Name Box in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
