Share this content

Keyboard and Mouse Shortcuts to Use Within Excel Worksheets

Mar 2nd 2017
Share this content
Spreadsheets and graphs on a desk
xfgiro/istock

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:
  1. 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.
  2. 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.
  3. 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.

Replies (2)

Please login or register to join the discussion.

avatar
By Erich Niemand
Mar 31st 2017 08:04

Another alternative to using Ctrl-Arrow Key or Ctrl-End is to press the END key release it and then press the Up, Down, Left or Right Arrow key to move to the last cell filled with data in a row or column.

Thanks (0)
David Ringstrom
By David Ringstrom
Apr 4th 2017 13:35

Erich,

Thank you for the additional shortcuts. I purposely omitted the End/Arrow combinations because I've found in teaching Excel webinars that the dizzying array of keyboard shortcuts in Excel become bewildering, so I've settled on mostly teaching the Ctrl key combinations. End/Arrow certainly works--I've used it endlessly myself.

David

Thanks (0)