How to use named ranges to quickly navigate an Excel workbook

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.

  Pointing a spreadsheet on a laptop

Image: Aajan Getty Images / iStockphoto

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:

  1. Select C3: C6. (You can also apply a name to a single cell).
  2. 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.

Figure A

  excelnavigate-a.jpg "data-original =" https://tr2.cbsistatic.com/hub/i/r/2019/07/22/c657f5d0 -c7aa-42a1-9a7a-92ac20ff42d1 / resize / 770x / 667bbd68455f01be68021d428327286d / excelnavigate-a.jpg

Name the Smith data, using the information panel. /www.techrepublic.com/ "height =" 465 "width =" 620 “/>

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.

Figure B

  excelnavigate-b.jpg "data-original =" https://tr4.cbsistatic.com/hub/i/r/2019/07/22/01780d7c-9bcf-4892- a9a4-906a4488c643 / crop / 720x432 + 124 + 132 / resize / 770x / 3b39d8e76e420ba0db95923e45cb24c5 / excelnavigate-b.jpg

Choose a range in which we are.

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.

Figure C

  excelnavigate-c.jpg "data-original =" https://tr4.cbsistatic.com/hub/i/r/2019/07/22/ec552b7c-cca0-430c- 83c2-2111936cafe2 / crop / 679x473 + 132 + 132 / resize / 770x / c08101b32054c52f7d9d1f1c9a0a56eb / excelnavigate-c.jpg

bathed in the form of a light box in the form of data that they need.

We will create a hyperlink that will select the Smith data, using the range named in the previous section. . Follow these steps.

  1. Right-click on B3 (the cell containing the Smith text value) and choose Link in the resulting submenu.
  2. In the resulting dialog box, click Place in this document in the left pane.
  3. Select Smith from the list of Defined Names ( Figure D ) and click OK.

Figure D

  excelnavigate-d.jpg "data-original =" https: //tr1.cbsistatic .com / hub / i / r / 2019/07/22 / 93b7f87a-8a2c-4f72-a994-d762c4e9295f / crop / 960x518 + 135 + 134 / resize / 770x / 95pc at the time of recreation. .jpg

Assign a link to the text value Smith.

Figure E shows the resulting hyperlink. To access Smith's sales data, click Smith in cell B3.

Figure E

  excelnavigate-e.jpg "data-original =" https://tr2.cbsistatic.com/hub/i/r/2019/07/22/4c332c38-10b7-4a51- 8863-5ee4fa25d65f / resize / 770x / 6cfc2661e5ba3ca3fe5d8ebde3679cf4 / excelnavigate-e.jpg

Use the hyperlink to access Smith's sales data. = "465" wide = "620 “/>

That was extremely easy, and both users and viewers will find hyperlinks easy to use; we have become accustomed to their format and know what they are for. You can also assign a hyperlink to To remove a link, right-click on it and choose Delete Hyperlink in the resulting submenu.

Shortcuts

It's easy to create named ranges and turn them into navigation shortcuts: your users will appreciate it.

Do you use named ranges in other shortcuts? If so, share your method in the discussion of the article.

Send me your question about Office

I answer readers' questions when I can, but there is no guarantee Do not send files unless requested; Initial requests for help arriving with attachments will be deleted without reading. You can send screenshots of your data to help clarify your question. But when you contact me, be as specific as possible. For example, "Please solve the problem in my workbook and solve what is wrong" you probably don't get an answer, but "Can you tell me why this formula is not giving the expected results?" might. Please mention the application and the version you are using. TechRepublic does not reimburse me for my time or experience in helping readers, nor do I ask for a fee from the readers I help. You can contact me at [email protected]

See also

Affiliate Disclosure: TechRepublic can earn a commission on the products and services listed on this page.

For More Updates Check out Blog, Windows Softwares Drivers, Antivirus, Ms Office, Graphic Design Don’t Forget to Look Our Facebook Page Get Into Pc like us & follow on Twitter- @getinpc

Please Note: This content is provided and hosted by a 3rd party server. Sometimes these servers may include advertisements. igetintopc.com does not host or upload this material and is not responsible for the content.