Working in Excel can sometimes feel like a “death by a thousand cuts” experience when you aren’t aware of subtle timesavers. In this article I’ll show you a number of ways that you can quickly navigate within an Excel worksheet so that you too don’t end up inefficiently frittering away a day.
Many years ago I coined the phrase: “Either you work Excel, or it works you.” A case in point is a recent video by Hunter Hobbs where he manually scrolls down to the bottom of an Excel spreadsheet by holding down the Down arrow key.
In case you’re wondering, it takes more than nine hours to get to the bottom of an Excel spreadsheet in this fashion. And, yes, Mr. Hobbs is aware that he could have pressed Ctrl-Down to get to the bottom of the worksheet in an instant.
Here’s some more useful shortcuts for you:
Ctrl-Arrow Key: Holding down the Ctrl key along with any arrow key (left, right, up, or down) jettisons your cursor in a given direction within the worksheet. How far you’ll travel is predicated on where you are when you press the keyboard shortcut:
If the starting cell has anything in it, Ctrl-Arrow Key will move you in a given direction until Excel encounters a blank cell. If you press Ctrl-Down within cell A1 of a blank worksheet, your cursor will move to row 65,536 in an Excel 2003-compatible workbook, or row 1,048,576 in a modern Excel workbook.
If the starting cell is empty, Ctrl-Arrow Key will move you in a given direction until Excel encounters a nonblank cell or any edge of worksheet.
An alternative to using Ctrl-Arrow Key is to double-click on the border of any worksheet cell. Double-clicking on the bottom edge of cell A1 in a blank workbook will instantly transport you to the bottom of the worksheet. The normal rules apply here with regard to your cursor moving until it hits a filled cell or worksheet edge, so double-clicking the cell border is simply the mouse equivalent of Ctrl-Arrow Key.
Ctrl-Home: This shortcut always takes you to cell A1 from anywhere within a worksheet.
Ctrl-End: This shortcut takes you to the last used cell within a workbook. Ostensibly, this should be the last cell that has data in it, but sometimes this is subject to interpretation. To Excel, the last used cell in a worksheet is the cell farthest down and to the right that has ever been altered. Typing something in, say, cell Z500 and then erasing it doesn’t make cell A10 suddenly revert to the last used cell; Z500 is the last cell until you reset the worksheet.
You can get more leverage from the aforementioned keyboard shortcuts or mouse action by including the Shift key. For instance, if your cursor is currently in cell A1, Ctrl-Shift-End will select the entire used area of the worksheet. Or, to select a column of data, hold down Ctrl-Shift-Down after placing your cursor at the top of the column.
Many users click on the worksheet frame to select an entire row or column. There’s a downside to this technique if you’re actively working between, say, an Excel 2003-compatible workbook and a modern workbook.
The XLS file format has 256 columns and 65,536 rows. You can easily copy entire rows or columns from this type of workbook into the modern XLSX, XSLM, and XLSB workbook formats.
However you can’t copy 16,384 columns or 1,048,576 rows from a modern workbook back to the older, obsolete workbook format. In such cases the aforementioned keyboard shortcuts can help save your sanity.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.