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.
Written by Allen Wyatt (last updated November 5, 2020)
This tip applies to Excel 2007, 2010, and 2013
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:
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:
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:
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.
Note:
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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
You can hide a bunch of worksheets at the same time, but Excel makes it much more difficult (depending on your version of ...
Discover MoreMicrosoft added a new feature to Excel that causes a "lock icon" to appear at the left of a worksheet tab if the ...
Discover MoreNeed to move quickly through the worksheets in a workbook? Learn the keyboard shortcuts and you can make short work of ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments