Named ranges are not just for formulas. Here are two ways you can use named ranges to create shortcuts for quick navigation in a Microsoft Excel workbook.
There are many ways to move on a sheet or from one sheet to another in an Excel workbook; however, you can anticipate the needs of users including shortcuts for viewers who are not familiar with Excel. Or, in a busy file, you can include shortcuts to make things easier. In this article, I will show you how to use named ranges and hyperlinks as shortcuts to important data.
I am using Office 365 on a 64-bit Windows 10 system, but can use earlier versions. You can work with your own Excel data or download the demo file.
LEARN MORE: Office 365 Prices and features for the consumer
How to create a named range in Excel
Most users learn about named ranges from the beginning: it's a name applied to a cell or a range of cells. Most of us use them in expressions, but you can also navigate with them. Before you can use a named range to navigate, you must create one. To do this, select the cell or range and then type the name in the Name Box control to the left of the formula bar. To illustrate, we name the data of Smith ( Figure A ) Smith as follows:
- Select C3: C6. (You can also apply a name to a single cell).
- Click inside the Name Box control, type Smith and click Enter. A name must start with an alpha character, an underscore (_) or a backslash (). You cannot start with a number. Nor can the names contain space characters or most punctuation characters, or conflict with a cell reference. The names are not case sensitive.
There are other ways to create a named range, but this is one of the simplest. You cannot create a named range in the browser edition.
How to navigate to a named range using the Name Box control
The Name Box control is also the fastest way to navigate. Click the Name box drop-down menu and select a named range to move to that range. For example, move to a sheet other than the one that contains the data. Then, in the drop-down menu ( Figure B ), select Smith. Excel will quickly select the Smith data in the sheet. This also works in the browser. Alternatively, you can press F5 and select a named range.
This is one of the simplest ways to move quickly from one area of an Excel workbook to another. However, when you create a named range using Define Name in the Defined Names group on the Formulas tab, you can limit the scope to the current worksheet. When you do, the name will appear only in the drop-down menu when the named range is part of the active sheet.
You need to know about the Name Box to use it, so it won't always be the best route. To create a more intuitive shortcut, you can provide hyperlinks.
How to navigate to a named range using a hyperlink
Control of the Name Box requires the knowledge that its users might not have. In addition, in a control panel situation, you will want to provide intuitive navigation tools, such as hyperlinks. For example, you can include a table of contents, of type, that includes a hyperlink to the sales data for all personnel. The simple table in Figure C illustrates this simple but fine concept.
We will create a hyperlink that will select the Smith data, using the range named in the previous section. . Follow these steps.
- Right-click on B3 (the cell containing the Smith text value) and choose Link in the resulting submenu.
- In the resulting dialog box, click Place in this document in the left pane.
- Select Smith from the list of Defined Names ( Figure D ) and click OK.
Figure E shows the resulting hyperlink. To access Smith's sales data, click Smith in cell B3.