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: Picking Worksheets Quickly.

Picking Worksheets Quickly

by Allen Wyatt
(last updated December 20, 2016)

12

An Excel workbook can contain almost any number of worksheets, and the tabs for those worksheets are displayed at the bottom of the screen. Everyone knows that if the tabs can't all fit across the bottom of the screen, you can use the navigation buttons in the bottom-left screen corner to scroll through the worksheet tabs.

What you may not know is that Excel provides a cool way to pick a worksheet if you can't see its tab on the screen. (Hmmm; do I scroll left or right? How far do I need to go?) All you need to do is right-click on the navigation buttons. (The navigation buttons appear just to the left of the worksheet tabs.) Excel displays the names of all your worksheets. Just pick the one you want, and off you go—no need to worry about which way to scroll!

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

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

Selective Summing

If you want to add up the contents of a range of cells based on what is contained in a different range of cells, you need the ...

Discover More

Showing Only Added Text with Track Changes

Do you want to change how Track Changes displays the markup in your document? Here's how you can completely hide deleted text ...

Discover More

Adding Shared Files to Drive

When you are working in Drive, the program makes it easy to see the files to which you have access. Normally, shared files ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Using Very Long Worksheet Tab Names

Excel places a limit on how many characters you can use in a worksheet name. This tip discusses that limit and provides some ...

Discover More

Dynamic Worksheet Tab Names

Need a worksheet's name to change based on the contents of a cell? You'll need to rely on a macro to do the changing, but it ...

Discover More

Lotus Grouped Worksheets

Not all spreadsheet programs are created equal; there are some things that can be done in others that can't be done in Excel. ...

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 6 + 0?

2017-06-05 22:12:12

Greg

When you right click on one of the navigation arrows, It shows a menu with a partial list of worksheet (tab) names which is kind of helpful, but not groundbreaking.

If you click on the [More Sheets…] option, you get a different dialog with the full list of worksheet names. Any worksheet name can be selected to activate that worksheet.

Even better, if you know the first letter of the worksheet name, you can type that letter and the selection jumps to a worksheet that starts with the letter.
Each time you type the letter, the selection moves to the next worksheet name that starts with that letter.

If your spreadsheet has a separate worksheet for each of the states, you could type a "K" and jump straight to "Kansas"!




2016-12-20 16:19:44

robert

Roger Barnes:
Store your macro in your Personal Workbook and you can then use it in any workbook you choose to open. No need to open a second workbook.


2016-12-20 13:45:12

mandora

I have used the hyperlink table of contents approach, each sheet being an apartment,and it's very efficient. I add a hyperlink back to the master list. Two clicks takes you to any sheet. Fast and efficient once set up and not difficult or time-consuming to set up even with more than 100 sheets.


2016-12-20 13:38:30

Dan

This is a great tip. Perfect if you have 16 sheets or less. It is a bit of a pain to have to click on more sheets after scanning the displayed ones to find the one you are after is under "more sheets". Especially this is the case if you have a multitude more than 16. I followed the hyperlink tip from Lorenz and I find it is the best tip for workbooks with a large number of worksheets. If you situate your hyperlink worksheet at the beginning, then all you need to do his hit the "beginning" control arrow and immediately click on the "hyperlink worksheet" and there you have the complete list of sheets all before your eyes. Especially if you have 50 or 60 sheets for example, they can be displayed in several columns, in alphabetical order. The other best thing about this method is that you can go to the desired worksheet and be at your desired position in the sheet when you click the hyperlink.Thankyou Lorenz.


2016-12-20 08:26:57

Marilyn

This is not that useful as it still doesn't show all my worksheet tabs and I have to click on More Sheets.


2013-07-06 12:33:15

Tony

Wonderful tip Allen. I never knew about this.

David S your Page Up and Page Down is good too but I think the point here was for workbooks with many worksheets where all tabs might not be visible. If you're at the 10th tab and don't know if the stuff you're looking for is on the 3rd tab or the 22nd tab, you'd be Paging Up or Down, or clicking or scrolling aimlessly.


2013-06-19 10:18:30

Hugh

Thanks Lorenz for the letting me know about the Hyperlink tip. I have used Hyperlink's before but never gave it a thought, I will try this method.
Thank you!!


2013-06-17 09:36:35

David Staudenmeir

Perhaps this has already been covered, since I am new to this forum, and this sort-of relates to the above conversation, but if you're a keyboard oriented person like I am, Ctrl+Page Up or Ctrl+Page Down will move you back one sheet or forward one sheet, as the case may be. It's quick if you have several sheets you are working with.


2013-06-17 08:38:29

Roger Barnes

I created a short macro that creates a list of all the worksheets in the active workbook and let's you choose which one to go to. I store it in my "macro" workbook so my main workbooks remain macro free and I keep only one copy of it. I just need to have both files open at the same time.


2013-06-16 14:21:10

Lorenz

Hi Hugh,
Aside from creating a button and assigning a macro to it, you can also use "Hyperlink" to navigate through your workbook. This will make your workbook "macro-free".
1. In your "Main Menu" sheet, right-click any blank cell, and choose Hyperlink.
The Insert Hyperlink dialog box will appear.
2. On the Link To: panel at the left, click "Place in this Document".
In the middle of the dialog box, scroll up and find "Cell Reference" (the list below this are the tabs/worksheets in your workbook)
3. Select the desired tab where you want to navigate when you click the cell (note: the sheet name automatically appears on the "Text to display:" box. This is the text that will be displayed on the cell. You can replace this, as you desired.
4. In the "Type the cell reference:" box, type the desired cell reference where you want your cursor to "land" on the selected worksheet (default is A1)
5. On the top-right side of the dialog box is the "ScreenTip" button. Clicking this button will display the Set Hyperlink ScreenTip dialog box where you can type the text that you want to be displayed when you hover your mouse on the cell with Hyperlink (the default is a "garbage" text showing the path/address of the hyperlink).
Click OK when done
6. Click Ok to close the Insert Hyperlink dialog box.
7. Repeat the process for all the other sheets


2013-06-15 20:29:14

Hugh

This is a great tip!!! When I have a lot of worksheets I make up a "Main Menu" with buttons and assign a macro to each button. With this tip I can just right click and boom I am there.


2013-06-15 12:43:25

Jerry

If your workbook has more than 16 worksheets, the last entry in the pop-up list is "More Sheets...". When you click on it, an Activate dialog box opens with a scrollable list of all the sheets in the workbook. Double-click the name of the worksheet you want, and it moves into view on the tab bar and opens as the active sheet.


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.