How to Use 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.

Select Specific Rows and Columns Together. You can even select several multiple rows and columns together. For instance, type A:C,5:7 and hit Enter to get the following selection.

This is interesting because you can immediately see where rows and column values overlap. In the screen below, it is the data in the range A5:C7.

In the above case, you can also just isolate the overlap between cells by typing A:C 5:7. Omit the comma and include a single space between the ranges to see that Excel highlights the cells where the two ranges intersect.

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 selected cell address or the address of the entire range in any formulas.

In the screen below, I assigned the name Interest to cells B3 to B7. I select the range of these cells, typed the word Interest in the Name Box, and then hit Enter. All range names in a workbook must begin with a letter, underscore, or backslash. Spaces are not allowed.

See this illustrated step by step tutorial to learn how to work with Named Ranges in Excel. Named Ranges are excellent descriptors if you also program with macros in Excel.

5. Name Every Object in Your Excel Worksheet

A worksheet can have different objects like pictures, charts, macro buttons, form controls like buttons and checkboxes, etc. Excel uses generic names like Chart 1, Chart 2… to name them. You can navigate through all objects by typing these names in the Name Box.

But just like the facility of Defined Ranges for easier formulas, you can assign objects your own descriptive names. It makes them easier to find.

1. Select the chart, picture, or object.
2. Place the cursor in the Name Box and type a new descriptive name.
3. Press Enter.

6. Insert (Or Delete) Multiple Blank Rows With the Name Box

There are several ways to insert blank rows in Excel. One of them is with the Name Box. This method is useful when you want to insert many rows in one shot. Head to the Name Box and use the format starting row: ending row with the numeric values for the rows you want.

For example, if you want to insert 10 rows from row 2, then type 2:11 in the Name Box and hit Enter. Excel will select the next ten rows starting from row 2 (it is “eleven” because the second row will also be counted in the next ten row) selections.

Then, right click on the selected rows and click Insert. Ten blank rows will be inserted starting from row 2.

This method is handy when you want to insert thousands of blank rows for some reason.

7. View a Macro

As mentioned before, you can give your own names to macro buttons in Excel. But you can also launch the Visual Basic editor to get into the source code for any macro.

Type the name of the macro in the Name Box. The VBA Editor opens with the macro’s source code for you to edit or see.

The Name Box in Excel Is a Productivity Shortcut

The time saving uses of Excel’s Name Box demonstrate why you shouldn’t neglect it’s utility. As it is a core navigational feature in Excel, you just have to use it. But creating Named Ranges and its prowess for quick selections show how you can better grasp a complex spreadsheet.

That’s just the start you need to use more of Excel’s time saving secrets to boost your productivity.

Image Credit: Rawpixel/Depositphotos

Read the full article: How to Use the Name Box in Excel

from MakeUseOf https://ift.tt/2JDgI6s
via IFTTT