Advanced Excel 2010 – Ch:01:E – Moving around a Worksheet – Keyboard Tricks
While you may find it easier to use the Mouse to navigate, but with more than 17 billion cells in a worksheet, it’s not that easy just to use mouse alone to navigate. Excel provides a number of handy key combinations to navigate around the worksheet faster than the mouse could. However, using keyboard requires just a bit of getting used to.
You can use the standard navigational keys on your keyboard to move around a worksheet. These keys work just as you’d expect:
- The Up & Down arrows move the active cell one row Up or down respectively.
- The Right & Left arrows move the active cell one column to the right or left respectively.
- Page-Up and Page-Down moves the active cell up or down one full window.
- Alt+Page-Up and Alt+Page-Down moves the active cell one screen Left or Right.
Note: While using the Page-Up or Page-Down keys in the above scenario, it’s not possible to speak this in terms of actual number of rows, since it depends on the number of rows displayed in the window.
With Scroll Lock ON, the Arrow keys changes its functionality. Instead of moving the active cell, they scroll the screen.
Note: With the Scroll lock on, the active cell doesn’t change its position. Only the screen scrolls.
- Up & Down arrow scrolls the screen one row Up or Down respectively.
- Right & Left arrow scrolls the screen one column Right or Left.
If you have scrolled the screen and the active cell is not visible, you can use Ctrl + Backspace to scroll the screen to display the active cell.
One important shortcut key that you need to keep in mind is the Home key combinations, which bring you back to the beginning of a row or to the first Cell which is the A1.
Let me explain in a bit detail. While you are in any part of the worksheet, the Home Key will move the active cell to the first cell of the current row, which is column A. While using the Ctrl + Home key combination will move you the first cell on the first row, which is the A1 cell.
Like the Home key, you can also use the End key in combination with Ctrl key. This combination acts a bit different than the Ctrl + Home combination. And for a kind of flip side to Ctrl+Home, there’s Ctrl + End, a slightly trickier move. In a blank worksheet, tapping Ctrl + End will not move anywhere. Let us add some data into cells M5 and J20. Tapping Ctrl + End now moves the active cell to the last cell in the worksheet that intersects the last row & last column containing data. In our example the last row containing the data is 20 row (cell J20) and the last column containing the data is M (cell M5), hence we will end up at row M20, which is the cell that intersects for column M and row 20.
However, there is one problem here. Assuming that you delete the data in one of the cells that you have mentioned above, or if you move it to some other column or row prior to these cells. Say deleting the data in M5 column and moving it to L1 column or moving the data from the row J20 to J22 and then deleting the data itself from the new cell all together. Even in such a case, if you use the Ctrl + End combination, it will still take you to the M22 cell, since the last row that contained data was 22 (J22) and column was M (M5).
If you want the excel to take you to the proper destination, make sure you save the file and try again. Now you will reach the proper cell that marks the last cell that intersects the row and column in the worksheet. Since we only have one cell containing data (L5), when we tap Ctrl + End, we will be directly taken to that cell.
Excel also lets you jump a range of cells in a single bound using a Ctrl + Arrow key combination. These key combinations jump to the edges of your data cells.
Let me explain this with an example, assuming that you have a few cell ranges or cells containing data in your worksheet, with these cells and cell ranges all separated by a few or a large number of empty cells. The first or last cell that contains data in these ranges is what I am referring to as the edge cells.
Let me give a brief about these key combinations and their results:
- When the active cell is inside a cell range containing data, Ctrl + Arrow will move to the edge cell that is the first or last cell in the column or row of the cell range.
- Ctrl + Left Arrow will move to the first column in the cell range.
- Ctrl + Right Arrow will move the active cell to the last column in the cell range.
- Ctrl + Up Arrow will move to the first row in the cell range,
- Ctrl + Down Arrow will move to the last row in the cell range.
- Once you are at the edge cell, and press the same combination again, they you will skip over all the nearby blank cells and land in the next cell in the arrows direction that has information in it. If there aren’t any more cells with data in that direction, you will jump to the cell at the very edge of your worksheet.
This comes in handy when you have two or more tables of data, all separated by a large number of empty rows. If you are at the first table, and the other ones are to the right of it, you can jump to each of them by simply using Ctrl + Right Arrow twice. This will jump the active cell from one table to another, and you can use the Ctrl + Left Arrow twice to return back.
One important key on the keyboard that we use often is the Enter key. This key by default moves the active cell by one row at a time. However this can be configured to move in any direction we may want, but only by a single cell.
By clicking on the File Tab, Select the menu item Options and then select the option Advanced. Under the section Editing options, you can actually see the item “After pressing Enter, move selection” with a dropdown box named Direction below it. By default the value that is selected will be Down, you can change it to any other option you like and click Ok which will then move the Active Cell in that direction. However for some reason, if you don’t want to move the cell in any direction, you can always Un-Check the check box, which will leave the active cell unchanged even after pressing the Enter key.
Another important Key is the F5, which opens up the Go To dialog box. But, you can use this feature only if you remember the Cells address. While all the keys mentioned earlier did not require you to know the cells address, F5 needs an exact address. If you know exactly where you need to go, you can use the Go To feature to directly jump to the it. Go To moves the active cell to the address you specify.
If you are working on a large worksheet, where just scrolling through the worksheet is not feasible enough, Go To feature comes in very handy and avoids all the hassles of scrolling through the worksheet. You can either press the F5 key or Ctrl + G key combination to open the Go To dialog box. Type in the Cell address and click ok or press Enter. Voila! Excel jumps directly to the address that you have specified.
This feature becomes a lot easier and useful as you use it, since the Go To window maintains a list of all the most recent cell addresses that you’ve used. In addition, every time you open the Go To window, Excel automatically adds the current cell to the list.
This feature makes it easy to use go to any location and return back by selecting to the previous location by selecting the last entry in the list. This feature not only jumps to any cell in the current worksheet, but it can move anywhere in your workbook. In other words you can move across to any cell in other worksheets of the workbook.
The Name box also allows you to jump to any cell, except that it does not provide you with the list of previously visited addresses, thereby making the Go To window a lot easier to use.
There are a lot of other key combinations that can achieve lot of things that I have mentioned till now, which I will cover as we continue with the rest of this tutorial.
You can find a comprehensive list of Excel 2010 Keyboard Shortcuts at our site. Though these shortcuts are for Excel 2010, most of them work even on earlier versions of Excel 2010.