Name Box in Excel
The Name Box in Excel highlighted by the red box in the screenshot is a square on the left of the formula bar in Excel. This allows you to give selected cells or groups of cells a name instead of referring to them using their row and column coordinates.
This is how you can give a name to Name Box:
Now, follow the steps to assign names to the data ranges:
Select the student data excluding the header “Name,” “Subject,” and “Marks.”
- Click and drag from A2 to C5 to select the cells containing student information.
Input the desired name for the data range in the Name Box in excel.
In the Name Box (located next to the formula bar), enter “Students” and press Enter. Now, the range A2:C5 is named “Students.”
Add additional data (e.g., marks) below the existing data.
- Assume you have added more student data below the existing table:
Assign a name to the combined data of students and marks.
- Now, select the extended range from A2 to C7 (including the new data).
Input the desired name for the combined data range in the Name Box in excel.
- In the Name Box, enter “Smarks” and press Enter.
Editing and Deleting Name Ranges:
Edit the Name of the Data Range:
If a name needs correction, follow these steps:
Navigate to “Name Manager” under the “Formulas” menu.
Click on “Edit” and change the name as required.
Deleting the Name of the Range:
Just like editing, select that name within the “name manager,” then click delete.
How to Use the Name Box in Excel:
Count the number of students in the student table.
Select Name Range:
- Highlight the name “Students” in the Name Box.
- Enter the COUNTA formula: =COUNTA(
Automatic Recognition:
- Excel automatically recognizes the named range. You can see it appearing in the formula bar.
Complete the Formula:
- Close the formula with a closing parenthesis and press Enter.
- The result, in this case, should be 5, representing the count of students.
SUM with the Name Box in Excel:
Objective: Sum up the marks of all students using the named range “Smarks.”
Select Name Range:
Highlight the name “Smarks” in the Name Box.
Click on the dropdown arrow in the Name Box in excel, select “Smarks.”
VLOOKUP with Name Box:
Use VLOOKUP to find the marks for a student from Table B using the named range “Sdata” for Table A.
Create Name for Table A Data:
Now, let’s create Table B where you want to use VLOOKUP:
Table B
Now, follow the steps:
Create Name for Table A Data:
- Highlight the data range in Table A (excluding headers) and assign the name “Sdata” to this range using the Name Box.
Input VLOOKUP Formula:
- In cell F2 of Table B, enter the VLOOKUP formula:
=VLOOKUP(E2, Sdata, 2, 0)
- E2 is the lookup value (student’s name in Table B).
- Sdata is the named range for the data in Table A.
- 2 is the column index for marks in Table A.
- 0 is used for exact match in the range_lookup parameter.
