• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Zip about big Excel workbooks: 3 tips

Get PDF file

by on
in Excel Training,Office Technology

Your organization has probably amassed large, unwieldy Excel workbooks. You might have 52 work sheets, one for each week, or 12, one for each month. Maybe the company supports 50 or 100 offices, departments or accounts, each with its own work sheet. Benefits abound for keeping all the data in one workbook, but moving around can get tedious. Here are three tips for zooming around your big workbooks with ease and precision.

1. Work sheet navigation

Scrolling back and forth through work sheets can take precious time and wear out nerves and muscles in the hand. Right-clicking the arrow on the lower left side of the Excel screen brings up a work sheet list. Simply click the one you want to work on. And, there you are!

You may see a More worksheets selection if your workbooks are exceptionally large. Clicking this option allows you to select the work sheet you want from a scrollable list. Shortcut: Type the first letter in the work sheet’s name to jump directly to it, even though the list may not be in alphabetical order. Hit Ctrl+PgUp or Ctrl+PgDn to jump from one work sheet to the next or the previous one.

2. Named ranges

You may be familiar with the term named ranges as a tool for look-ups or advanced formulas. However, you can also use named ranges as bookmarks. Highlight one or more cells, then click in the name box. Name your range. You will not be able to use spaces or any special characters with the exception of the underscore ( _ ).

Example: If your calculation results in net profit, you could name that cell Net Profit. Then, anytime you want to quickly get back to that number in the workbook, just click the name box and select Net Profit. Ctrl+G, which brings up the Go To dialog box, is a useful shortcut to access your named range, as well as any cell in the workbook.

3. Trace precedents

Many people use the Trace Precedents tool to reverse engineer or troubleshoot a workbook. However, it’s also handy when navigating to a cell used in a formula on another work sheet. On the Formulas tab, in the Formula Auditing group, choose Trace Precedents. You will see a little diagram with a work sheet icon and a dashed line ending in an arrowhead. Double-clicking the arrowhead brings up the Go To dialog box, which shows all the formula components. Using the Ctrl+G shortcut brings up the same ­dialog box.

Leave a Comment