Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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 a Specific Worksheet.
Written by Allen Wyatt (last updated June 21, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
If you have a huge number of worksheets in a workbook, you may be looking for a way to jump to a specific sheet rather easily. There are a number of ways you can approach this task, and their applicability to your situation depends on how many worksheets you actually have in the workbook.
One option that works well if you have a limited number of worksheets (say, 30-40 sheets or less) is to right-click the sheet navigation buttons at the left of the sheet tabs. Doing so will pull up a list of worksheet names, and you can select which one you want to jump to. If there are more worksheets than can comfortably fit in the list, then one of the options is "More Sheets." Select that option, and you end up with a dialog box that lists all the worksheets and you can make your selection.
Another option that many people employ is to create a "table of contents" for your workbook. In the first worksheet, enter a bunch of hyperlinks that jump to the various worksheets in your workbook. That way you can display the TOC, click a link, and you are on your way.
If you know the name of the worksheet you want to jump to, you can also use the Go To capabilities of Excel. Follow these steps:
Another option is to create a macro to prompt for either the name or number of the worksheet you want to display. The following macro could be assigned to a shortcut key, and then you can use it to jump to whatever sheet is desired.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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 sSheet As String
sSheet = InputBox( _
Prompt:="Sheet name or number?", _
Title:="Input Sheet")
On Error Resume Next
If Val(sSheet) > 0 Then
Worksheets(Val(sSheet)).Activate
Else
Worksheets(sSheet).Activate
End If
End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7094) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Jumping to a Specific Worksheet.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
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 ...
Discover MoreNeed to set up a workbook that includes a worksheet for each week of the year? Here's a couple of quick macros that can ...
Discover MoreIf you have a lot of worksheets in workbook, finding the exact one you want can be a bit tricky. This tip looks at ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-06-22 12:41:17
J. Woolley
My Excel Toolbox includes the following dynamic array function:
=ListSheets([SkipHidden])
This function returns one row with sheet names in subsequent columns. To return the list in one column with several rows, use this:
=TRANSPOSE(ListSheets([SkipHidden]))
In older versions of Excel that do not support dynamic arrays, you can use ListSheets with the SpillArray function like this:
=SpillArray(TRANSPOSE(ListSheets([SkipHidden])))
SpillArray will determine and populate the spill range for its array expression argument, simulating a dynamic array.
After populating a column with sheet names, you can create a hyperlink to each sheet by adding a formula to the next column; however, there are certain issues when a hyperlink is created using the HYPERLINK function:
+ A hyperlink cannot reference a worksheet without identifying a cell, range, or name on that sheet
+ Activating such a hyperlink will change the worksheet’s previously selected cell or range
+ A hyperlink cannot reference a chart sheet because a chart sheet has no cells
+ A hyperlink referencing a hidden sheet will fail silently when activated
These issues are resolved by the following My Excel Toolbox function:
=SheetNameLink(Sheet_Name, [Friendly_Name], [Screen_Tip])
where Sheet_Name is like [file]'sheet' or [file]#'sheet' or 'sheet' or #'sheet'. For example, if cells A1:A20 contain a list of sheets created by the ListSheets function described above, this formula in cell B2 would create a hyperlink to the worksheet or chart sheet listed in cell A2:
=SheetNameLink(A2)
The hyperlink does not address a cell; therefore, the sheet's previous selection is not altered.
My Excel Toolbox also includes the following function:
=SheetListUpdateLink([Friendly_Name],[Screen_Tip])
This function uses SuperLink to create a hyperlink that will list a workbook's sheets (including hidden sheets) in subsequent rows. Each sheet in the list includes a hyperlink to hide or activate (unhide) the sheet.
Finally, My Excel Toolbox's SheetsDialog macro displays a sheet activation list at the bottom-left corner of Excel's window (similar to right-clicking a scroll button at the left side of sheet tabs). Here is an abbreviated version:
Sub SheetsDialog()
Dim X, Y
Const PxPerPt = 96 / 72
With Application
X = .Left * PxPerPt
Y = (.Top + .Height) * PxPerPt
.CommandBars("Workbook tabs").ShowPopup X, Y
End With
End Sub
See https://sites.google.com/view/MyExcelToolbox/
2022-06-21 08:01:09
Dave Roberts
And if you're going to create a TOC, putting a Home button on each page is a quick way to get to the TOC. Add a shape to another sheet. Add a link to the TOC. Copy the shape to all the other pages.
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 © 2023 Sharon Parq Associates, Inc.
Comments