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.
Want even more? Get them here in our colorful guide: Microsoft Excel: Time-Savers for Every Skill Level
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.
When you learn to master Excel ... really, master it ... your workbooks become so much more manageable and your data is easier to find. Get started with great advice like this in Microsoft Excel: Time-Savers for Every Skill Level. Order your copy here.
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.
Excel should make your job easier, not harder. Now you can discover the tricks that will make you an Excel expert. You’ll get back all the time you’ve wasted by not using Excel to its full potential. And your extra effort and knowledge will be noticed and appreciated.
Remember, there’s absolutely no risk. If you don’t see how you can optimize your efficiency and your time, we’ll refund your entire purchase price – no questions asked – and you’ll have no further obligation.
Your time is important, and you can’t afford to waste a minute. Make Excel work for you. Get your copy now!