Shortcuts are easy to use and very fast, but who has the time to look them up? Here I want to show some quick, easy shortcuts to navigate through large excel spreadsheets quickly.
For those who are new to keyboard shortcuts, there are two keys that initiate most shortcuts. The “Ctrl” key, which stands for control, and “Alt” stands for alternate or alternative. Along with the “Shift” key, these are called modifier keys. They modify the behavior of other keys on your keyboard, for example, holding down the shift key while pressing the letter “a” changes that letter to an uppercase character. Like with the shift key, these modifiers must be held down to modify the function of the other key that is being pressed. To show how this action is performed, it will be displayed like:
- Shift + a
Let’s begin with some quick movement around a sheet. On the keyboard are four arrows that point up, down, left, and right. Select a cell in the middle of the sheet and press these arrows and you can see the selected cell change. Now hold down the “Ctrl” key and press the down key.
- Ctrl + Down
The selected cell will jump to the bottom of the spreadsheet. For those using Excel 2003 or earlier, that would be row 65,536. Excel 2007 would be row 1,048,576. To go back to the top just hold down “Ctrl” and press the up key.
- Ctrl + Up
Using the “Ctrl” key to move left or right works the same way. However, once data is added to a spreadsheet the selected cell movement changes slightly. The selected cell will stop moving at the beginning or ending of a data range. A data range is a grouping of cells with data in them. Set up a test sheet similar to the below snapshot.
If columns A, B, C, and D have data (a data range), E, F, and G are blank (not a data range), and H, I, J, and K have more data (another data range), then pressing Ctrl + Right while cell A1 is selected will move the cell selection to D1. D1 would be the end of that range. Pressing Ctrl + Right again will move the cell selection to H1, as this is the beginning of a new range. Press once again and we are at cell K1. One more time will then take the cell selector to the end of the spreadsheet, as there is no other data to prevent the selector from moving.
Now let’s do the above exercise again, but we will add another shortcut key, holding down the shift key.
- Shift + Up
- Shift + Down
- Shift + Left
- Shift + Right
This will highlight cells in whatever direction you are moving adding cells to the selection as the arrow is pressed. This gives you access to multiple cells to do functions like copying, formatting, and deleting, to name a few.
- Ctrl + Shift + Up
- Ctrl + Shift + Down
- Ctrl + Shift + Left
- Ctrl + Shift + Right
Control shift will highlight cells in the same way, moving the selection until the cell selection finds the end of a data range. This is very useful for large spreadsheets.
Home and End Keys are interesting shortcuts when combined with “Ctrl”. Ctrl + End will take the cell selection to the last column with data and the last row with data in it. Ctrl + Home will simply take the cell selector to cell A1.
Something else that is interesting about the End key is that it works similar to the Ctrl key when used with the arrow keys. Unlike other modifiers, you do not hold the end button down. Simply press the End key and “End Mode” text will display in the Excel status bar.
Press any arrow key and watch the cell selection jump to the beginning or ending of the data range you are in. This is particularly useful if you have trouble holding Ctrl and Shift at the same time. Instead you could hit the End key and then hold down shift and an arrow key to highlight the range of data you want.
Page Up and Page Down keys are nice if the scroll function on the wheel mouse makes you sea sick. Page Down simple moves the viewable area of your screen down to the unseen rows directly below. For example, if the last fully visible row in my viewable area is row 20, when I press Page Down, the top row will be 21 and the last row will be 40. Page Up works the same way, only your moving up rows, not down.
Alt + Page Up and Alt + Page Down do the same thing, except your moving side to side along the columns. Alt + Page Down move’s the viewable area to the right, where Alt + Page Up move’s to the left.
Ctrl + Page Up and Ctrl + Page Down is a shortcut that allows you to move between sheets in your workbook. Ctrl + Page Down make’s the sheet to the right the active sheet, where Ctrl + Page Up make’s the sheet to the left active.
These are but a few of the shortcuts available in Excel, and are some of the most useful.