Excel Sheet Selector With No Macros

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.

hyperlinknavigation05

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.

hyperlinknavigation03

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.

hyperlinknavigation02

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.

hyperlinknavigation08

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".

hyperlinknavigation07

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.

hyperlinknavigation01

_______________

4 comments to Excel Sheet Selector With No Macros

  • Brian Canes

    There is a workaround to list the sheet names without VBA. Using a defined name sheets= get.workbook(1)
    Then in a worksheet cell =index(sheets,row(A1)) and fill down.
    Now to get rid of the get.worksheet, copy paste values the sheet name list. Then delete the defined name.
    For a workbook with very many sheets, this is a big help
    Regards
    Brisn

  • Brian Canes

    Note:
    The Get will return [bookname]Sheetname
    So the following formula will extract just the Sheetname
    =IFERROR(TRIM(RIGHT(SUBSTITUTE(INDEX(sheets,ROW(A1)),"]",REPT(" ",33)),33)),"")
    and fill down
    Note:
    The Hyperlink will not work on a new workbook, say book2, before it it saved.
    Note:
    The get.workbook is not volatile, even if you include a now() function. So to update the list if you add, delete or rename a worksheet, you must F9 to recalculate. If this does not update then it will be necessary to select the first cell in the first formula and F2+Enter then fill down the First formula again.
    Note:
    The Get.Workbook is an old Excel4 Macro, which if you don't want to get rid of, an excel file extension should not be xlsx, but xlsm, xlsb or xls.
    Regards
    Brian (not Brisn)

  • Brian Canes

    Note:
    However, if you include a now() in the defined name like
    =GET.WORKBOOK(1)&REPT(NOW(),0)
    Then when renaming or deleting a sheet, the list of names will automatically update. However, when adding sheets, the formula needs to be filled down again.
    Note:
    After hyperlinking, to get back, use Alt← (Alt Left Arrow)
    Regards
    Brian

  • Doni

    there any way to work for spreadsheet chart?

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>