Two tips for faster worksheet navigation

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.

Tip 1: Use a pop-up menu

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.

Tip 2: Create a table of contents

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