Creating an Excel Menu Worksheet

By Joe Duggan, IR Decision Support Specialist, Edmonds Community College

This is a fairly simple setup that will allow you to create a menu of links to other worksheets in a workbook and give descriptive names to the menu items. This is particularly useful if you have multiple worksheets in your workbook.

Steps

Using a generic workbook with three worksheets:

1. Rename Sheet1 as Menu.

2. Rename Sheet2 as DashboardTemplate.

3. In Sheet2 (now DashboardTemplate) enter the text Back to Menu in cell A1.

4. Right-click in cell A1 – and select Hyperlink from the options listed.

This opens the Insert Hyperlink box, as shown below:



5. Change the selection under Link to: to Place in This Document.

6. In the box Or select a place in this document, select Menu (originally Sheet1) and click OK.



7. Cell A1 on the DashboardTemplate worksheet is now linked and will go to the Menu worksheet when clicked.

8. Duplicate the DashboardTemplate – create a copy for each menu selection you want to include. You may rename these sheets however you prefer. This example uses DB1, DB2, etc. However, worksheet names won’t matter too much, as the Menu worksheet will show descriptive titles.

9. Go to the Menu worksheet and enter a descriptive title for each worksheet to be linked from the Menu. As shown below, enter each title in a separate cell.


10. To link a Menu entry, select the cell containing the text you wish to link, right-click, and choose Hyperlink.

11. When the Insert Hyperlink dialog box opens, Place in This Document should already be selected (from Step 6). Select the appropriate worksheet to be linked to the text (in this case I chose DB1) and click OK.


12. Repeat steps 10 and 11 as necessary until all menu items are linked to the appropriate worksheets.Repeat steps 10 and 11 as necessary until all menu items are linked to the appropriate worksheets.

13. Items on the Menu sheet are now linked and will go directly to the selected worksheet when you click on them.Items on the Menu sheet are now linked and will go directly to the selected worksheet when you click on them.  You now have a menu system for navigating the worksheets.

Other suggestions:

  • This technique can be applied to link to external sources (such as folders, other workbooks or documents, or web pages). Go to Excel File/Options/Advanced and uncheck Show Sheet Tab for a more streamlined look.

  • Click on Insert/Shapes and drag a shape onto the menu sheet and attach a hyperlink to the shape instead of plain text.

  • The screenshot below uses the two suggestions above as well as minimizing the ribbon, selecting unused rows and columns then hiding them, and then unchecking gridlines and headings (under View in the Ribbon).

 

Access the interactive workbook

(Select Save As to save to your computer and view.)

 

 
 

 Comments

 
To add a comment, Sign In
Total Comments: 3
 
Terry posted on 8/13/2015 2:28 PM
Great little trick, sometimes we get so caught up in the data that we forget that our audience needs direction.
Lee posted on 8/13/2015 2:30 PM
Thanks Joe, for an interesting and useful Tech Tip! This will definitely make it easier to navigate through large Excel files with multiple worksheets.
Marlene posted on 8/17/2015 8:54 AM
This is very helpful! I just inherited a workbook that uses hyperlinks so this is a very timely tip to help me edit them.