How To Use The Name Box In Excel

Using the Name Box in Excel

You can’t go anywhere in Microsoft Excel without using this humble little box. But where is the Name Box in Excel? It lies right next to the formula bar in a worksheet and displays the address of the selected cell or an object. But that’s just the most obvious use. You can navigate around the sheet with it or use it as a powerful selection tool.

But that’s just the scratching the corners of the box. Let’s look at all the productivity purposes the Name Box was created for.

1. Find the Address of the Selected Cell

As shown in the screen above, the Name Box shows C4 because the cell C4 is selected right now. Click on any cell to get the cell address to use in a formula.

The Name box won’t just give you the address of one cell. It can be applied to any group of cells too. For instance:

Select a block of cells. The Name Box will identify the first cell in the selected group of cells.

Select a range of non-contiguous cells. You can select several cells that are not grouped together. Press the Ctrl key and choose a bunch of non-contiguous cells. The Name Box in Excel will display the address of the last cell you select.

You can go the other way also. If you want to go to a specific cell or a range of cells, then you can type the address of that cell in this Name Box and select them in a pinch.

Let’s see how it can speed up your work when you have a lot of rows, columns, and even sheets to work with.

2. Use the Name Box to Select Cells

Let’s do away with the mouse to select a block of cells with their addresses alone.

Select a block of cells. In the screenshot below, I typed A2: B10 in the Name Box and then pressed Enter. This selects a block of cells and you didn’t have to travel across your worksheet with the mouse.

Select a range of cells on another worksheet. Add the sheet reference (E.g. Sheet2!A5: B10) in the Name Box.

Select multiple blocks of cells. As shown with an example in the screen below, you can type A1: A5, C1: C5, G1: G5, and then press Enter to select any number of non-adjacent blocks of cells at once.

You can jump from any cell to any cell by just typing these cell addresses. To jump to any cell in another worksheet within the same workbook, type the sheet name, an exclamation point, the cell address, and then press Enter. For example, Sheet2!A10.

3. Select Rows and Columns With the Name Box

Use the Excel Name Box as another speed trick to quickly select rows and columns. These are handy Excel shortcuts when you want to want to avoid the mouse.

Select the current row. Type the letter R in the Name Box and press Enter to select the current row.

Select the current column. Type the letter C in the Name Box and press Enter to select the current column.

Do remember that the letters R and C are reserved by Excel for this reason, and you cannot use these single alphabets to create Named Ranges in Excel (learn more about Named Ranges below).

Select any number of rows. Want to highlight the first five rows? Type 1:5 in the Name Box and then press Enter to select rows 1 through 5. You must select two or more rows. If you type a single number in the Name Box (for instance, 1) and press Enter, Excel shows an error message.

Select two or more columns. Want to highlight the first five columns? Type A: E in the Name Box and then press Enter to select columns A through E. Again, you have to give two or more columns.

Find the total number of rows and columns. The Name Box displays the total number of rows and columns when you keep the left mouse button pressed or hold down the Shift key while selecting a block of cells.

4. Create Named Ranges for Excel Formulas

Imagine an Excel worksheet that references several cell ranges in multiple formulas. It’s easy to get confused with all the alpha-numeric cell names. Wouldn’t it be easier if these cells had their own descriptive names that told you at a glance what the cells were all about?

Excel uses Named Ranges to do just that. And you will use the Name Box in Excel for that.

1. Select a cell or a range of cells.
2. Give a descriptive name to the selection in the Name Box. Press Enter.
3. Use this name instead of the selected cell address or the address of the entire range in any formulas.