Guide: Navigation

Learning to navigate around a spreadsheet can reduce your time spent doing tasks significantly. If you grab the scrollbar to navigate up and down long documents or if you use the mouse to drag up and down documents while selecting cells then this guide is for you!

There are a number of shortcuts that can be used individually or combined to do repetitive tasks much more quickly. We'll look at each of these individually below.

Shorcut	Usage
Control	Jump
Page Up	Jump
Page Down	Jump
End	Modifier
Home	Modifier
Shift	Selection
Space	Selection
Tab	Selection
Enter	Selection

Control
The control key helps the user move around the document by jumping from place to place. It jumps around in a very specific, predictable way. Pressed by itself, it will do absolutely nothing; instead it must be combined with an arrow key or one of the modifiers (end or home). When jumping, the active cell selection will travel in the direction of the arrow key pressed and it will land on the last cell with data or the first cell with data if passing over empty cells.

For example, if you have a row of data that spans from A1 to A15 and your currently selected cell is A1 and you press the down arrow while holding control, it will jump to A15, which is right before a blank cell. If A1 to A14 was empty and A15 had data in it, it will jump through all of the empty cells and land in A15. This works with any of the arrow keys. Control behaves slightly different when combined with the End and Home key, though...we'll cover that a bit later.

Page Up/Down
Page up and down don't do anything spectacular but they do make it easy to skip through large chunks of data instead of jumping to the end of it or scrolling through all of it. The size of the jump up or down is dependent on your screen size and zoom level. If 30 rows are visible on the screen, it will jump up or down 30 cells. Not much more can be said about this aside from it can be combined with shift to select all the cells between the jumps.

End/Home
The End and Home keys function in a very similar way with opposite results. When you press End by itself, it turns on "End Mode" which will stay active until you press a key. If you press an arrow key in end mode, it will function exactly like pressing Control and an arrow key. This aspect of the Home key doesn't function much like the End key but when you press the Home key by itself, it will jump you to the first column in the row (back to A).

The functionality of these two keys becomes significantly more useful when combined with the Control key. Pressing Control and then End (while still holding Control, which will be abbreviated as Control + End from this point on) will jump you to the last cell in the spreadsheet. This may not be exactly where you expected it to be because Excel considers the furthest cell used, even if the contents have been cleared out since, the last cell until you have saved the sheet.

In normal usage, pressing Control and either the End or Home keys will cause Excel to jump to the last cell in a sheet or back to the beginning (A1). The last cell is a combination of the furthest column and the lowest row such that contents in G4 and A10 would cause it to jump to G10.

You can see this in action by entering some data in cell D5 of a new, blank sheet and pressing Control + End and Control + Home a few times (you don't need to release the Control key between strokes of End and Home). The active cell will jump back and forth between D5 and A1. Now put some data in G10 and repeat the key strokes, it'll now jump back and forth between G10 and A1. Last step: click in G10 and press delete to remove the contents of the cell and repeat the keystrokes. It will still jump back and forth between G10 and A1! Save the workbook as anything you would like and then repeat the keystrokes, it'll resume jumping back and forth between D5 and A1. So remember this, anytime you delete the furthest contents in a spreadsheet, Control + End will still jump to it as if it is still there until you save the sheet.

Shift
The shift key helps the user make selections, especially in combination with the jump/modifier keys above. If you had been holding Shift in the exercise above, you would have not only jumped between A1 and D5/G10 but you'd also have selected all the cells in a box between them. Anytime you are holding down shift, you will make a selection from the current cell to the target cell. Holding shift while pressing the arrow keys will make selections of adjacent cells. This is pretty straight-forward, play around with it to get a feel for how it works.

Space
The Spacebar has a pretty nifty function within Excel when combined with Shift or Control. If you want to select an entire column (columns are at the top and run vertically), then simply press Control and the Spacebar. It will select the column or columns of all active selections. If you click and drag from F5 to I5 and then press Control + Space, it will select columns F through I. If, however, you click F5, G5, H5, and I5 while holding Control, you are making individual selections and only the last selections column will be selected (in this case only I is selected). The same concept applies to rows if you press Shift and the Spacebar. The same restrictions apply as well. There is a very easy way to remember which highlights which: the Shift key is longer horizontally and thus coorelates to the rows or horizontal selection while the Control key is shorter than the Shift key and thus coorelates to the columns or vertical selection.

Tab/Enter
The last navigation keys to touch on are Tab and Enter. They don't do a whole lot but they do have interesting functionality that can be taken advantage of in your everyday use of Excel. Pressing them normally will cause you to travel either horizontally from column to column (Tab) or vertically from row to row (Enter); combining them with Shift will cause the direction to be reversed. Shift + Tab goes back to the left and Shift + Enter goes to the cell above.

The interesting behavior comes into play when you make individual cell selections using mouse clicks combined with Control. I have set up a spreadsheet to illustrate the point:

I have selected each individual cell in sequence starting with 1 and ending with 11. When I press Tab or Enter, Excel will jump from cell-to-cell in the same sequence that I selected. The idea is interesting but there is very limited usage for it; however, when you select things using Shift and click-and-drag a box, it becomes significantly more useful.

In this example, I have highlighted the columns in which I will enter data (Name, Age, and Sex). By doing this, I am able to simply press Tab each time I want to move to the next cell and after entering data in the Sex column, it will wrap back around to the Name. This allows for speedy entry by limiting the number of times you have to manually return to the beginning row of your data. If I wanted to enter Lloyd, who is a 52 year old male, I would make the following keystrokes:

Lloyd
[Tab]
52
[Tab]
M
[Tab]

There is no need to press Enter after each entry, Tab will automatically skip you to the next cell over.

There have been a lot of topics covered here and I know it can be overwhelming to someone used to using the mouse and scrollbars to navigate spreadsheets; however, if you begin using these keystrokes, as unnatural as they may feel in the beginning, you will be zipping around your spreadsheets faster than you even realized was possible!