Q: It’s nice that the newer version of Excel has more than a million rows, but paging down all of those rows takes an eternity. We are now using Excel to analyze more than 300,000 rows of data, but navigating with the Page Down button takes forever. Is there a better way to move around Excel?
A: There are several alternatives available to you for navigating larger Excel worksheets, a few of which are mentioned below.
The scroll wheel method. Pressing the scroll wheel button on your mouse will display a multidirectional arrow on your Excel screen (see screenshot below). Thereafter, as you move your mouse pointer away from that multidirectional arrow, the Excel worksheet will scroll increasingly faster in the direction of your mouse pointer. Using this method, I was able to travel 16,000 columns in about two seconds, and 50,000 rows in about three seconds.
The Go To method. Pressing the F5 key in Excel will display the Go To dialog box, which will enable you to type in a specific cell reference and jump directly to that cell. For example, in the screen on the left pictured below, I am navigating to cell AF324568.
As an advanced alternative, you could define names throughout your workbook, and select those defined names from the Go To dialog box, an example of which is pictured at the bottom right of the previous column. To define a name in Excel, select the cell (or cells) where you want to define a name, then from the Formulas tab, select Define Name, type a name into the Name box, and click OK (see screenshot below).
Home key method. As a rule, I always enable Excel’s Transition navigation keys so that when I press the Home key, the cursor moves to cell A1 of the current worksheet. Here’s how:
- Excel 2010 and 2007. From the File tab (or Office Start button) select Options (or Excel Options), Advanced, and check the Transition navigation keys box under the section labeled Lotus compatibility, and click OK.
- Excel 2003. From the Tools menu, select Options. From the Transition tab, check the Transition navigation keys box, and click OK.
Navigational hyperlinks method. Another approach is to create a table of contents in Excel and use hyperlinks to link the table of contents to the appropriate locations in your workbook. For example, cell B7 (see screenshot below) includes a hyperlink to cell A1 in the worksheet labeled “Cash Flow.” Clicking the hyperlink moves the cursor to the appropriate worksheet. Of course, the Cash Flow worksheet should also include a link back to the table of contents worksheet.
To create a hyperlink to a specific location within Excel, highlight the cell containing the text you want to hyperlink, and press Ctrl+K. In the Insert Hyperlink dialog box, select the option Place in This Document and type the worksheet name, encased in apostrophes and followed by an exclamation point and the cell reference in the Type the cell reference box (for example: 'Cash Flow'!A1), then click OK. ( Note: Using defined names will make this process easier.)
Keyboard methods. Pressing the End key followed by the up, down, left or right arrows will move the cursor to the cell just before the next blank row or column in that direction. Pressing End, Home will move the cursor to the bottom-right corner of the active area of the worksheet.
More from the JofA: