Tag Archives: name box

Use Excel Name Box for Navigation and Selection

Excel, Office, , , , , ,

In a previous post we covered navigating in Excel using the Name Box by typing a cell reference or a name for a cell or range of cells in the box or using the Go To dialog box (see link below).

compass for navigating your worksheet

When you name cells, they are called Range Names and become much more powerful, or at least more convenient, compared to using cell references.

Name Box for Navigation

The box reflects the current, active cell but does so much more. Although it looks separate, it is really part of the Formula Bar, so if you hide the Formula Bar, it disappears also.

The Excel Name Box

A quick recap on accessing the Name Box. There is no keyboard shortcut for landing there but you can press F5 function key to display the Go To… Dialog box and type in a cell reference in the Reference box; press ENTER or click OK, to go directly to that cell.

The Go To Dialog Box

Name Box for Selection of  Cell Ranges

Selecting huge ranges of data can be frustrating but if you know the cell references for the desired selection, the Name Box is the way to go. Just type in the range (or as close to it as you remember), i.e., a150:r8765, press ENTER, and that entire range is highlighted.

Tip: If you forgot column(s) or row(s) or included too many, just use SHFT and arrow keys to add or remove them. (Beats re-selecting two or three times).

Create Range Names in Name Box

The real power of the Name Box is to use it to create a name or ID for an often-used cell or range of cells, such as Commission or Regions, etc. You can create names that refer to cells, formulas, or a specific value.

The names can then be used for quick navigation or in a formula instead of cell references. You can create as many named ranges as desired and they are accessible from any worksheet in that workbook.

Steps to create range names:

  • Select the cell(s) that you want to name
  • Click in the Name box
  • Type a name for the cells (descriptive but short). There are some rules for range names:
    • Must start with a letter or an underscore
    • No spaces (can use an underscore to represent a space) *
    • Name may contain letters and numbers and periods but nothing that could be mistaken as a cell reference (examples: C, 2, R7C4)
    • Not case sensitive
    • Is ABSOLUTE cell referencing by default
    • Press ENTER after naming you range

 * Examples of naming a range could be: TotalSales or Total_Sales

Range Name example

Note: If there is a formula in that cell, it displays in the Formula Bar and the applied name in the Name Box.

I can now be anywhere in my workbook and return to that range with a click on the dropdown arrow in the Name box:

Name box with cell name

Now I don’t need to know the cell reference or even where it is located and can use that name in a formula, either for part of the formula or with other range names. For instance, if I had a commission rate in F8 and had named that cell, my formula would be: =TotalSales*Commission from any cell in my workbook. How sweet is that!

Name Box Limitations

There are several ways to create named ranges but the Name Box is the quickest. Be aware though that you can only create range names here, you cannot edit or delete them. That must be done from the Formulas tab on the Ribbon. You can also have Excel create range names for you using existing row and column labels to name them (covered later).

See original post for ways to navigate your worksheet:   https://gaylelarson.com/quick-navigation-excel-worksheet/

See the blog on Using Range Names in Formulas for more ways to use this great feature!  https://gaylelarson.com/use-range-names-in-formulas/

Are you liking this handy Name Box? Let me know in the Comments below!

Quick Navigation in Excel Worksheets

Excel, Office, , , , , ,

Go There in a Flash in Excel

You inherited the biggest spreadsheet on the planet and have to scroll and troll to ranges miles away from your current screen! Don’t despair, Excel has several tools to get you there quickly…

The Go To Dialog Box

You can jump to any area of your worksheet with this handy command. Using the Ribbon, Home tab, Editing group, Find & Select drop down arrow, and select Go To… Just type in a cell reference, click OK or press ENTER, and you are there!

OK, that might beat scrolling but not by much, so here’s the corresponding shortcut:

Press CTRL G or F5 function key to auto display the Go To Dialog Box and it is ready for your entry. It also remembers your previous cell location(s).

The Name Box

Want the quick mouse navigation trick? The Name Box is your ticket! Just click in the box and type the cell reference (such as T44456); press ENTER and you are there. (Unfortunately, there is no keyboard shortcut for the actual Name Box but you could use F5 to display the Go To box, type the cell reference and press ENTER).

keyboard angled 2 x 3
Where did I put that?

Range Names

The amazing Name Box is multi-talented. We saw we can highlight large ranges of cells and also jump to a faraway cell location just by typing the cell reference(s) in the Name Box. But what if you are short on sleep and can’t remember the cell reference(s)? No problem. There is a reason it is called the “Name Box”. All you have to do is find that cell once, select it and give it a name that makes sense to you, like Sales or Profit, etc.

Steps:

  • Navigate to desired location
  • Click on desired cell (or range of cells)
  • Click in the Name Box and type a name, i.e., Sales
  • Press ENTER

To return to that location any time, just click the drop-down arrow on the Name Box and select the name you gave it, and you are magically transported there!

Important things to know when naming a cell:

  • Range Names must start with a letter but can contain numbers.
  • You can use more than one word to name a range but it cannot contain spaces.
  • Capitalize the separate words, like TotalSales, or the underscore can be used to represent the space, i.e., Total_Sales.

Range Names are ABSOLUTE by default, meaning the name will always refer to the cell even if the content is moved. And, even more impressive, Range Names can also be used in formulas instead of cell references. That Excel is one smart cookie!