You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
If your workbook contains a lot of worksheets, it can be a hassle to navigate to just the right sheet. For one thing, you often can't see all the sheet names at the bottom of the workbook:
Sure, you can move around by using the four navigation buttons to the left of the worksheet tabs, or by pressing Ctrl + Page Up or Ctrl + Page Down, but there are better ways. Read on to learn two of my favorite navigation techniques for large workbooks.
Did you know Excel has a pop-up menu that you can use for navigation? This menu is brilliant, yet it's hard to discover unless someone tells you about it. (I first learned of its existence from Jean Philippe, who is one of my favorite coworkers and a huge champion of Excel.)
To make the menu appear, right-click anywhere in the sheet navigation area, outlined in red below:
Now, if you don't see the worksheet menu, you may be right-clicking just above or below the sheet navigation buttons. You need to pretty much right-click ON the buttons themselves.
If your workbook has more than 15 sheets, click More Sheets and you'll see the full list.
This second navigation technique is more involved, but it's very effective. In a nutshell, you add a new worksheet to your workbook, list all your worksheet names in a column, and then link each name to its corresponding worksheet, kind of like a table of contents.
To do this, create a new worksheet and call it "Cover Sheet" or something similar. On this sheet, type the names of all the worksheets in your workbook. Then, select a cell with a sheet name, press Ctrl+K to open the Insert Hyperlink dialog box, click the Place in This Document button, and then link to the sheet. Lather, rinse, and repeat until you are through adding the links.
This process is quite manual, but it's okay if you have a relatively small number of worksheets. If you have a whole bunch, you may want to use a macro to generate the links automatically. A macro is a little code snippet that can help you automate boring and repetitive tasks in Excel. Don't panic at the thought of creating a macro—the post I linked to earlier comes with sample code and step-by-step instructions for adding one to your workbook. Listen, I'm no VBA whiz kid, so if I can get this to work, anyone can!
Extra credit: Add a Home button on each sheet that goes back to the cover sheet. One easy way to do this is to add a shape (Insert tab, Illustrations group), format it the way you want, select the shape, and then add a hyperlink that jumps back to the cover sheet. Here's what that might look like:
If you have other techniques to share, leave a comment. I'd love to hear them.
-- Anneliese Wirth
Comments: (26) Collapse
I didn't think worksheet nav could get any better than CTRL + Page Up. Thx for tip #1.
Instead of using VBA to generate the links, you can use the hyperlink function to quickly generate lots of links as well. Assuming you have cells with strings containing the various sheet names in your workbook, you can use a formula of the form =HYPERLINK("[current file name]sheetname!A1","sheetname") to create a hyperlink to another sheet in the filename workbook. Just replace the text "current file name" with the name of the workbook you're creating the table of contents in (remember to leave the square brackets), and reference a cell with the sheet name you want to switch to in each spot in says sheetname. Voila - you shoul dhave a quick table of contents.
For more information about how to use the hyperlink function to link to cells in the same workbook, see this article: office.microsoft.com/.../hyperlink-function-HP010062412.aspx
Larry Waldman
Program Manager
Microsoft Office
Hi Lianna -- You are so welcome. Thanks for stopping by.
Larry, that's an excellent tip, and thank you for taking the time to explain how to use the HYPERLINK function to create the links.
Excel MVP Bill Jelen goes into more depth about the HYPERLINK function in the following blog post, for those who are interested:
blogs.office.com/.../hyperlinks-in-excel-hot-or-not.aspx
- Anneliese
Excellent tips. The video by Mr.Excel on creating hyperlinks shows how very simple it is. Thanks for the hint on how NOT to select a hyperlink as well, Anneliese- I'm forever making that mistake!
s.mitchell: I'm happy to hear that you found the videos useful. I have to let you in on a little secret: I still activate links accidentally, even though I should know better. What is it they say about old dogs and new tricks? ;-)
With sheets, I always needed a function to get a name of the nearby sheet. For example, if I'm in Sheet2 =SHEETNAME(-1), would return me "Sheet1", =SHEETNAME(1), would return me "Sheet3", =SHEETNAME(0) would return the name of the current sheet.
So I could make an =INDIRECT(ADDRESS(1;1;1;1;SHEETNAME(-1)). This way I could get the value of whatever is in the sheet on the left.
This would very much help when you have 12 sheets for month, and copy stuff from previous month.
Can we call this menu from VBA? When needing to choose a sheet interactively, I have been setting up a Userform.Listbox by enumerating Sheet (S) (S= 1 to Sheets.Count)), is there a cleaner way?
Hello, you can sort of call this from VBA, with something like this:
Sub bar()
Application.CommandBars("Workbook Tabs").ShowPopup 1, 700
End Sub
This is pretty clumsy, at best - as written. I've hardcoded the pixels, etc... Personally, I'd stick with the UserForm. They're a pain to set up, but once you have them going, you have full control of the UI. The way you're doing it sounds just about right to me, it's pretty clean, it runs really fast on my machine. I have a small example file set up, if interested.
Best, Nate
Two great tips, that I've implemented on a sheet I'm using right now. I find that if you can use the 'tip' straight away. I tend to remember it and reuse them again and again and again. For the extra credit - I've created the home button too. Is there a way to copy and paste the Illustration from one sheet to another? The normal copy and paste doesn't seem to copy it across. I guess you could create another macro to do it for you.
Hello,
I can offer one more tip for fast worksheet navigation:
If you are not going to use named ranges in you workbook, then you can name each cell (A1 for example) in each sheet with the sheet name. Then you can easily navigate among sheets easily by using drop-down menu for named ranges in formula bar.
BR, Anar
A normal copy and paste should recreate the Shape with assigned code, with something generic, like this:
Application.Goto Worksheets(1).Range("A1")
It should come across. But if it doesn't, and it should (it does in my tests), it's a matter of right-clicking on the Shape and assigning a Macro. Do I get extra credit? :)
I.e., the same Macro, no need for two.
See also this tip:
www.contextures.com/xlToolbar01b.html
Here's a little example I whipped up, it uses ActiveX, Shapes and VBA. So use it at your own risk:
www.box.net/.../af60byd09kaysdg8xgqf
It's not as sophisticated as Deb's, but it will get you a list and a way to return to the main page. All Shapes call the same VBA procedure.