Excel is an electronic spread sheet program that can be used for storing, organizing and manipulating data.
Important! You know that Excel is great at calculating numbers, but did you know that Excel can also perform calculations on text as well? Read more: Join Text, Date, Currency and Numbers In Excel.
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 as you can see in the following screen shot:
Worksheet tabs at bottom of Excel window
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. (However, i learned it by myself when i tried that by a guess when i need to navigate something faster.)
To make the menu appear, right-click anywhere in the sheet navigation area, outlined in red below:
Worksheet navigation area
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.
Pop-up list of worksheets in workbook
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.
Creating a table of contents
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:
Home button that links back to cover sheet
Here is one quick 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.
If you have other techniques to share, leave a comment. I'd love to hear them.
