Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Jumping to Alphabetic Worksheets.

Jumping to Alphabetic Worksheets

by Allen Wyatt
(last updated October 13, 2016)

8

If you have a workbook containing quite a few worksheets, you may be looking for an easier way to jump to a specific group of worksheets, rather than use the scrolling arrows near the worksheet tabs. For instance, you might want to enter a single letter and have Excel display the first worksheet that begins with that letter. There is a way to do this directly within Excel. Simply follow these two steps if you are using Excel 2007 or Excel 2010:

  1. Right-click on the scrolling arrows at the left side of the tabs. Excel displays a Context menu that includes many of the worksheet tab names.
  2. Click on More Sheets option. Excel displays the Activate dialog box.

If you are using Excel 2013, the process of displaying the Activate dialog box is much simpler: Just right-click the scrolling areas and the Activate dialog box pops into view, containing a list of all your worksheets. (See Figure 1.)

Figure 1. The Activate dialog box.

To use the Activate dialog box, just press the first letter of the worksheet name you want. The first worksheet that begins with the letter is selected. If you continue pressing the same letter, then the next worksheet beginning with that letter is selected. When the desired worksheet name is selected, just press Enter and that worksheet is displayed.

The interesting thing about this approach is that you don't need to have the worksheets in alphabetical order to use it. Each time you press a letter, Excel selects the next worksheet that begins with that letter.

While this approach is pretty fast to use, some people may object because it involves the use of both the mouse and the keyboard. Some people prefer to strictly use the keyboard. In this case, it is best if you sort your worksheets alphabetically (as covered in other issues of ExcelTips) and then use a macro to pull up the desired worksheet area. The following macro will do the trick:

Sub GoToSheet()
    Dim iTemp As Integer
    Dim sSheet As String
    Dim sThisOne As String

    sSheet = InputBox("Enter first letter of sheet", _
      "Go to sheet", Left(ActiveSheet.Name, 1))
    If sSheet = "" Then Exit Sub
    sSheet = UCase(Left(sSheet, 1))
    iTemp = 0
    For i = 1 To ThisWorkbook.Sheets.Count
        sThisOne = UCase(Left(ThisWorkbook.Sheets(i).Name, 1))
        If sThisOne = sSheet Then
            iTemp = i
            Exit For
        End If
    Next i
    If iTemp > 0 Then
        ThisWorkbook.Sheets(iTemp).Activate
    End If
End Sub

Now, assign a shortcut key to the macro, such as Ctrl+G. From now on, you can simply press Ctrl+G, type a letter, and then press Enter. The first worksheet that starts with the letter you specified is selected.

A final solution is to create your own "index" or "TOC" to your worksheets. Insert a blank worksheet at the beginning of the workbook, then add hyperlinks to the various other worksheets in your workbook. Someone could click on the hyperlink, which would then display the worksheet referenced by the hyperlink.

Setting up hyperlinks in this manner is definitely more work, but it does have advantages not offered by the other methods described so far. First, users don't need to know the worksheet name at all. Second, you can use multiple "keywords" as links, each leading to the same worksheet. In this way the overall workbook becomes more accessible to different users. Finally, the sheets can be in any order desired, instead of putting them in alphabetical order.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12569) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Jumping to Alphabetic Worksheets.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Protecting Tracked Changes

Track Changes is a great tool for editors and collaborators to use when creating documents. An author, seeking changes from ...

Discover More

Understanding the Drawing Canvas

Need to keep your drawing shapes together in one place? The drawing canvas may be exactly what you are looking for.

Discover More

Numbering Only Certain Lines

Need to add line numbers to a document? Word provides an easy way to add them, but some of the controls that configure how ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Naming Tabs for Weeks

Need to set up a workbook that includes a worksheet for each week of the year? Here's a couple of quick macros that can do ...

Discover More

Switching Headers in a Frozen Row

Excel allows you to "freeze" rows in your worksheet. What if you want the rows that are frozen to change as you scroll ...

Discover More

Viewing Two Worksheets At Once

If you need to work on two worksheets in the same workbook at the same time, Excel makes this rather easy to do. All you need ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is one more than 9?

2016-10-15 16:08:00

Denis Gibbs

My fast alternative is
> Control+PgUp or
> Control+PgDn

Give it a try.


2016-10-14 10:52:09

Surendera M. Bhanot

Peter you can use the "Table" command on the 'Tables' Group in the 'Insert' Tab. That is a great utility to track things down in the large worksheet!! You can easily convert your sheet into Table by selecting it and clicking table command!!

You can read more about tables at http://www.excel-easy.com/data-analysis/tables.html


2016-10-14 10:40:00

Surendera M. Bhanot

How can we bring the "Activate dialog box" on the 'quick access bar' ??

That way it will be easier to access that box with just one click!!


2016-10-13 18:54:01

Neil

Peter- I would think the scroll bar on the right side of the page would make finding the correct spot in an alphabetized list of only 500 not too hard. Another option is to activate the filter function on your list. Then you can use the text filter for "begins with" or "contains" to only show book titles starting with a certain letter, letter combination, or word. You could also use the find function (Cntl+F) to jump to a book title containing a certain non-common word.


2016-10-13 09:00:14

Paul

Quick macro to create a hyperlinked list of worksheet in a workbook:
Sub SheetListerHyper()

' Generates a hyperlinked list of sheet names on a new sheet at
' the beginning of the workbook

Dim i As Integer

' Adds a new sheet at the beginning of the workbook
Sheets.Add Before := Sheets(1)
Sheets(1).Name = "Hyperlinked Sheet List"

' Creates a hyperlinked list of all sheet names on new first
' sheet of workbook
For i = 1 to Sheets.Count
ActiveSheet.Hyperlinks.Add _
Anchor := Cells(i, 1), _
Address := "", _
SubAddress := "'" & Sheets(i).Name & "'!A1", _
TextToDisplay := Sheets(i).Name
Next i

End Sub


2016-10-13 07:40:05

gerdami

You could also consider the free "SheetTools" by MVP Jan Karel Pieterse available at http://www.jkp-ads.com/download.asp


2014-06-01 20:33:16

Peter

Hello, Mr. Wyatt.

Thank you for all your tips; I have been getting your newsletters for over a year, and, though I still consider myself a beginner, I have learned many useful things. I have come to respect your Excel knowledge, and I hope you can help me find a solution to a problem. In some ways, the solution I seek seems to be a microcosmic version of Tip 12569 shown above. (The word "workbook" above becomes worksheet, and the word "worksheet" above becomes row.)

I have single-worksheet database of information about books, in which each row represents one book. The (over 500 and climbing) rows are sorted A-to-Z by the titles listed in Column B (starting with Row 3 because the first two rows are headers).

I'm constantly updating the worksheet, adding new titles and changing data that goes with existing titles. Now that there are so many rows to deal with, I would like to be able to jump to a title without having to scroll and scroll. I have theoretical ideas, such as a floating box, something like what is shown here (apologies if it comes from a competitor):
https://www.youtube.com/watch?v=VwgnZt-IslI
The box could contain links (A, B, C, etc.) that jump me up and down the list of titles. If that's not possible, it could contain two columns--one side with incremental row-numbers (50, 100, 150, etc.), the other side would have to auto-update to show the corresponding titles listed in Column B. I could then reference that and type the nearest row number into the Name Box.

Of course, I'm a relative newbie, with very little knowledge of macros and VBA, so I have no practical idea how to make those ideas work. Please help if you can.

If the ideas I mentioned are not feasible, perhaps you know another (or a better) way to allow me to navigate more quickly up and down my worksheet.

With respect and thanks,
Peter


2013-05-09 08:34:18

Rafael

Hello, when you right-click on a worksheet tab you see the context menu of the tab (insert, delete, change name, hide, protect sheet, etc.).
Is it possible to show this menu just with the keyboard?, this is while going throught the tabs with Ctrl+Next page or Ctrl+Prev. page, you stop on the sheet you want, and you open the context menu without mouse.

Thank you very much in advance


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.