If you're setting up a workbook for other people to use, they'll appreciate it if you make it easy to move around in the file. You can create a table of contents on the first sheet, and that will get them off to a good start. But then what?
Once they're on one of the other sheets, how can they get back to the table of contents, or go directly to a different sheet? We'll add a drop down list of sheets, to make it easy to go to the one that you need.
Navigate With Shortcuts
To navigate through a large workbook, you can use keyboard or mouse shortcuts:
- Ctrl+Page Up to go to the next sheet
- Ctrl+Page Down to go the previous sheet
- Right-click on the sheet navigation arrows, to see a list of sheets
But how do you really find a sheet most of the time? I usually click the sheet navigation arrows, and look for the sheet name as the sheets fly past. That's not too efficient!
The Finished Sheet Selector
To make it easy to go to a specific worksheet, we'll build a drop down list that shows the sheet names. In the cell below that, a hyperlink that will take you to the selected sheet.
Create a List of Sheets
The first step is to create a list of sheets that people need to go to. You probably don't need to list all the sheets in the file – don't include any Admin sheets where you keep the lookup lists, etc.
Tip: Since this list will be use for selecting a sheet, you could sort the sheet names alphabetically, to make them easier to find in a long list.
In my sample file, I listed four worksheets, and formatted the list as a named Excel table. The table is named tblSheets.
Name the List
Next, I selected the list of sheet names (not the heading), and named the list – SheetList. I'll use that name when creating the drop down list.
Add a Drop Down List of Sheets
To let people choose the sheet they want to go to, you can use a data validation drop down list.
In this example, the drop down list will go in cell A1.
- Select cell A1, and on the Excel Ribbon, click the Data tab
- Click the top of the Data Validation command
- On the Settings tab, from the Allow drop down, choose List
- Click in the Source box, and on the keyboard, press F3, to open the Paste Name window.
- Select the SheetList name, and click OK
- Click OK to create the Data Validation list.
Add a Hyperlink for Selected Sheet
Then, in cell A2, you'll add a HYPERLINK formula to create a link to the selected sheet.
The first argument for the HYPERLINK function is the link location – the place that the link will take you
The second argument is the "Friendly Name" -- the text that will show in the cell.
=HYPERLINK("#'" & A1 & "'!A1","Go to sheet")
- The pound sign (#) at the start of the address indicates that the location is within the current file.
- This link will take you to cell A1 on the sheet that was selected from the drop down list.
- The text in the link will say "Go to sheet".
Test the Link
To test the hyperlink, select a sheet name from the drop down list in cell A1. Then, click the link in cell A2, and you should go to the sheet whose name you selected.
Copy the Link
Once the drop down list and hyperlink are set up and working correctly, you can copy them to all the other sheets. Then, freeze the top 2 rows of each worksheet, so that the navigation cells are always visible.
Download the Sample File
To get the sample file, go to the Excel Sample files page on my website. In the Functions section, look for FN0044 - Hyperlink Formulas for Worksheet Navigation.
The sample file also has formulas to show the sheet name, and hyperlink formulas that will take you to the next or previous sheet.