Use Hyperlinks to Create an Opening Menu for a Workbook

October 04, 2021 - by Bill Jelen

Use Hyperlinks to Create an Opening Menu for a Workbook

Problem: I have designed a budget workbook that has various worksheets. Managers throughout the company need to use it, but some of the managers are not entirely comfortable with Excel. A navigation tool would help them get through the worksheet.

Strategy: You can make your first worksheet a menu with hyperlinks. Here’s how:

  • 1. Insert an opening worksheet called Menu. Add an entry for each section of the workbook.

A title in A1 says Welcome to the Budget Worksheet.  An instruction in A3 says Select an Option Below. Cells B4:B6 says Review Actuals, Enter Forecast, Submit to Corporate. These three cells are about to become clickable hyperlinks.
Figure 101. Column B will become clickable hyperlinks.
  • 2. Select cell B4 and then select Insert, Hyperlink or press Ctrl+K.

  • 3. In the Insert Hyperlink dialog, choose Place In This Document from the left icons.

  • 4. Select the correct worksheet. Verify the cell address of A1.

The Insert Hyperlink dialog says Place In This Document. Choose Cell A1 and then the correct worksheet name. In the Current example, the words Review Actuals will be a hyperlink going to Actuals!A1.
Figure 102. Choose the proper worksheet.
  • 5. Optionally, click the ScreenTip button and provide friendly text that will appear when someone hovers over the link.

Gotcha: If you don’t provide ScreenTip text, Excel will display a distracting ToolTip.

Results: The cell becomes a clickable hyperlink. Clicking on the link will take the manager to the Actuals worksheet.

Additional Details: Be sure to provide a hyperlink on the Actuals worksheet to take the manager back to the menu

Additional Details: It is tricky to select a hyperlinked cell. One method: Click on the hyperlink and hold the mouse button for two seconds. When the hand icon changes to a plus icon, let go of the mouse button. To edit an existing hyperlink, you use the Insert, Hyperlink command again or right-click the cell and choose Edit Hyperlink.

See Also: "Remove Hyperlinks Automatically Inserted by Excel" on page 561.

This article is an excerpt from Power Excel With MrExcel

Title photo by Andersen Jensen on Unsplash

Bill Jelen is the author / co-author of:
MrExcel 2021 – Unmasking Excel

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.