Among the many awesome features in Excel, Name Box doesn’t get talked about enough.
While it looks like a simple name box (just like its name says), there are some cool things you can do with it.
In this article, I will cover everything you need to know about Name Box in Excel and some amazing things you can do with it.
What is Name Box in Excel?
Let’s start with the very basics – What is a Name Box?
Name Box is a simple text box that is located at the left part of the formula bar.
It’s a bar below the ribbon containing only the Name Box and the Formula Bar.
You can also resize the Name Box by placing the cursor on the three dots between the Name Box and Formula Bar, clicking the left mouse key, and then dragging the cursor left or right.
With a Name Box, you can do some useful things, such as:
- Create a Named Range
- Select any range of cells, rows, or columns in the same worksheet or any worksheet in the workbook.
- Go to any macro subroutine by typing its name in the Name Box.
- Assign Names of Charts or Shapes
I will cover how each of these (and some more) work in detail later in this article.
Note: If you want the cursor to go to the Name Box, you can use ALT + F3 (hold the ALT key and press the F3 key), This will put your cursor in the name box
Where is the Name Box? Name Box Not Showing!
If, for some reason, you can see the Name box, the most common reason would be that the entire Formula Bar grid is hidden.
Here are the steps to make the Name Box reappear in your Excel:
- Click the View Tab in the Ribbon
- Within the Show group, check the Formula Bar option.
If the Formula Bar option is already checked and you still do not see the Name Box, try to uncheck it and then check it again.
If even this doesn’t work, try closing the Excel application and open it again.
Also read: How to Hide or Show Formula Bar in Excel?
Things You Can Do Using Name Box in Excel
Now, let me show you some amazing time-saving things you can achieve by using the Name Box in Excel.
Create Named Ranges
The most useful thing you can do using Name Box is create a named range.
A named range allows you to refer to a cell or range of cells with a name instead of the cell reference.
For example, instead of using A2:A20, you can give it a name, say SalesData, and then use the name SalesData instead of the range in formulas.
Let me show you how to create a named range using Name Box.
- Select the cell or range of cells for which you want to create a named range.
- Click on the Name Box to bring the cursor there. You can also use ALT + F3 to bring the cursor focus on the Name Box.
- Enter the name that you want to assign to the selected cell or range of cells.
- Press the Enter key.
The above steps would create a named range that would refer to the cells you selected in step 1. Now, instead of using the cell reference to the selected range of cells, you can use the name.
For example, if I selected B2:B10 as the range and used the name SalesData to create the named range, I can use the below formula to get the SUM of this data:
=SUM(SalesData)
When you click on the Name Box arrow, it will show you the list of all the Named Ranges you have created with it.
Also read: How to Delete Named Range in Excel?
Go To a Specific Cell Quickly
If you want to go to any cell quickly, you can do so by entering that cell’s reference in the name box and hitting the Enter key.
For example, if I want to go to cell K10, I can manually enter K10 in the Name Box and then hit Enter. This would instantly take me to that cell.
This would be useful if you need to often go to some specific cells that are far off in the worksheet.
Also read: Select a Far-off Excel Cell or Range
Select a Range of Cells (or Select Rows/Columns)
You can also use the Name Box to select a range of cells anywhere in the worksheet.
For example, if I want to select the range A1:D20, I can do this by entering this cell reference in the name box and hitting the enter key.
When I enter two cell references with a colon in between, the Name Box considers that to be the entire range that needs to be selected.
If you want to select an entire row (say the first row), enter 1:1 in the Name Box and press Enter.
To select multiple rows, you can enter the starting row number and the ending row number with a colon between them. For example, to select rows 2, 3, and 4, you need to enter 2:4 in the Name box and hit the Enter key.
Similarly, to select one column (say column A), enter A:A in the Name Box and press Enter.
And to select multiple columns, enter the starting column number and the ending column number with a colon between. So, to select columns B, C, and D, enter B:D in the Name Box and press enter.
| To Select | Example |
|---|---|
| Entire Row | 1:1 |
| Entire Column | A:A |
| Multiple Contiguous Rows | 1:4 |
| Multiple Contiguous Columns | A:D |
Also read: Shortcut to Select Entire Column (or Row) in Excel
Select Multiple Range of Cells (Non Contiguous)
You can also use the Name Box to select cells or ranges that are not continuous. To do this, you need to enter all the different cells or ranges with commas in between in the name box.
To select two non-adjacent cells (say A1 and D4), you need to enter A1,D4 in the Name Box and then press enter. This will select both the cells at the same time.
Similarly, you can also do this with ranges. For example, if I want to select A1:B20 and H5:K10, I can enter A1:B20,H5:K10 in the Name Box and press enter.
If you have created name ranges, instead of using the cell references, you can also use the names of those named ranges.
Also read: Select Every Other Row in Excel
Navigate to Another Sheet
If you are working with many worksheets in your workbook, you can also use Name Box to quickly navigate to another sheet.
To do this, you need to enter the name of the sheet in the name box along with a cell reference in that sheet.
For example, if I want to go to Sheet2, then I can enter Sheet2!A1 in the Name Box, and it will take me to cell A1 in Sheet2.
Note that you always need to have an exclamation mark after the sheet name and before the cell reference in that sheet.
In case there are spaces in the name in your worksheet, you need to put the worksheet name within single quotes followed by the exclamation mark and the cell reference of that sheet to which you want to go.
For example, if my sheet name is Sales Data, then I will have to enter ‘Sales Data’!A1 in the name box.
Also read: How to Switch Between Sheets in Excel?
Get the Number of Rows and Columns in the Selected Range
Name box also shows you the total number of rows and columns in a selected range while you are selecting it.
When you hold the left mouse key and select a range manually in a worksheet, you will notice that the row number and column number are shown in the name box in the following format – 10R x 2C.
This tells me that there are ten rows and two columns in the selected range.
Assign Names to Chart Objects or Shape Objects
Whenever you insert a chart object or any shape object in a worksheet in Excel, it gets a default name, which would be something like Rectangle: Rounded Corners 1
You can select any shape or chart in your worksheet and then change its name by typing it manually in the Name Box.
This could be useful when you’re working with multiple charts or shapes and you want to quickly select one specific chart/shape.
So if I have named all my charts, I want to go to any specific chart, no matter where I am in my workbook, I can just enter the name of that chart in the name box and hit the enter key, and that chart would get selected.
Go to the VBA Macro Code By Typing the Name in the Name Box
If you work with a lot of macros and subroutines, here is a neat trick for you.
Enter the name of any subroutine in the name box, and as soon as you press the enter key, it will automatically open the VB editor and place your cursor in that subroutine.
I’ve recently learned this Name Box trick, and I think I’ll be using it a lot now.
Also read: Useful Excel Macro Examples for VBA Beginners
Select Current Row or Column
Here’s another one that I don’t think many people know about.
If you enter the letter R in the name box and hit the enter key, it will automatically select the current row in the worksheet.
And, if you enter the alphabet C in the name box and hit the Enter key, it will automatically select the current column in the worksheet.
And this is not case-sensitive, so you can enter R or r and C or c.
FAQs about Name Box in Excel
Below are some commonly asked questions people have about the Name Box.
What is the Difference Between Name Box and Formula Bar?
While both Formula Bar and Name Box are part of the same bar in the Excel interface, they have different purposes.
The Formula bar is where you can write your formula and also edit the content of the cell.
Name Box is where you can name a cell or a range of cells to create name ranges and also quickly navigate to cells or ranges in the same worksheet or other worksheets.
Can I place the Name Box above the ribbon?
As far as I know, there is no way for you to place the name box bar above the ribbon. The only flexibility you get with the name box is to either hide it or resize it.
Is there a shortcut to hide and show the Name Box in Excel?
To hide a Name Box, you will have to hide the entire formula bar. This can be done using the shortcut ALT + W + V + F (press these keys one after the other in succession). This works as a toggle, so if you use the keyboard shortcut once and the Formula Bar and Name Box are hidden, it will get visible, and if it is visible, it will get hidden.
In this article, I covered everything you need to know about the Name Box in Excel, as well as some useful tricks you can use in your day-to-day work.
I hope you found this article helpful. If there are any other name box tricks that you use and that I have not covered in this article already, let me know in the comments section.
Other Excel articles you may also like:
