Thursday, November 10, 2011

bench3

Tips For Faster Navigation Around Excel Worksheet

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:

Tips For Faster Navigation Around Excel Worksheet

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: 

Faster Navigation Around Excel Worksheet

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.

Tips For Faster Navigation Around Excel Worksheet_3

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.

Faster Navigation Around ExcelWorksheet

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:

Faster Navigation Excel Worksheet

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.

bench3

About bench3 -

Haja Peer Mohamed H, Software Engineer by profession, Author, Founder and CEO of "bench3" you can connect with me on Twitter , Facebook and also onGoogle+

Subscribe to this Blog via Email :