MrExcel.com - Your One Stop for Excel Tips & Solutions

Quickly Create A Hyperlink Menu

April 21, 2021 - by

Quickly Create A Hyperlink Menu

Challenge: You are building a reporting package for people who are not familiar with Excel, and you want to add a menu worksheet to help them navigate through the workbook (see Figure 72). Usually, creating hyperlinks to another place in a document is kind of a pain, and you’d like to create a menu more quickly.

Figure 72. You want to repeat the titles and headings at the top of each printed page.
Figure 72. You want to repeat the titles and headings at the top of each printed page.

Solution: Make sure your workbook is saved and is the only workbook open in Excel.

In Excel 2007, select View, New Window. In Excel 2003 and earlier, select Window, New Window. This will open two views of the same workbook.

In Excel 2007, select View, Arrange All. Then choose Vertical and click OK. In Excel 2003, select Window, New Window, Vertical, OK. This allows you to see one worksheet in the left window and another sheet of the same workbook in the right window.

In the left window, navigate so you can see the menu worksheet. In the right window, press Ctrl+Page Down to move to the first page of the report.

The goal is to take an identifying title from each worksheet of the workbook and build a hyperlink to that worksheet on the menu sheet. In Figure 73, select the A1 title from the right window. Right-click the border of A1, drag to cell B4 in the menu worksheet and select Create Hyperlink Here.

Figure 73. This context menu provides a quick way to create a hyperlink.
Figure 73. This context menu provides a quick way to create a hyperlink.

To repeat this with the next sheet, use Ctrl+Tab to switch to the right window and press Ctrl+Page Down to go to the next sheet. Right-click the border of A1, drag to B5 in the left window and select Create Hyperlink Here. Repeat for each additional sheet.

Additional Details: If you want to also provide a “Return to Menu” hyperlink at the top of each worksheet in the reporting workbook, follow these steps:

  1. In the right window, move to the first report worksheet.
  2. In the left window, type Return to Menu in cell A3 of the menu.
  3. Select cell A3. Right-click the border and drag to cell H1 of the right window. Choose Create Hyperlink Here.
  4. In the right window, put the worksheets in Group mode. With the first worksheet selected, Shift+click the last worksheet.
  5. In Group mode, go to cell H1. Press F2 and then press Enter to copy the words Return to Menu in cell H1 of all the worksheets. (Unfortunately, this by itself does not establish the hyperlink in each worksheet.)
  6. Press Ctrl+C to copy the hyperlink from the first worksheet.
  7. Press Ctrl+Page Down and Ctrl+V to paste the hyperlink on the next worksheet. Repeat this step for each additional worksheet.
  8. Close the second window by clicking the X at the top of the right window.

Summary: Dragging titles as hyperlinks is a fast way to create a hyperlink menu.

Title Photo: Erik Mclean on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.