The problem with a list of hundreds of shortcut keys is that it is overwhelming. You cannot possibly absorb 233 new shortcut keys and start using them. The following sections cover some of my favorite shortcut keys. Try to incorporate one new shortcut key every week into your Excel routine.
1) Quickly move between worksheets
Ctrl+Page Down jumps to the next worksheet. Ctrl+Page Up jumps to the previous worksheet. Say that your workbook has 12 worksheets named Jan, Feb, Mar, . . . Dec. If you are currently on the Jan worksheet, hold down Ctrl and press Page Down five times to move to Jun.
2) Jump to the bottom of data with Ctrl+Arrow
Provided there are no blank cells in your data, press Ctrl+Down Arrow to move to the last row in the data set. Use Ctrl+Up Arrow to move to the first row in the data set.
Add the Shift key to select from the current cell to the bottom. If you have data in A2:J987654 and your cursor is in A2, you can hold down Ctrl+Shift while pressing the down arrow and then the right arrow to select all the data rows but exclude the headings in row 1.
3) Select the current region with Ctrl+*
Press Ctrl+* to select the current range. The current range is the whole dataset, in all directions from the current cell until Excel hits the edge of the worksheet or a completely blank row and column. On a desktop computer, pressing Ctrl and the asterisk on the numeric keypad does the trick.
4) Jump to the next corner of a selection
You’ve just selected A2:J987654 but you are staring at the bottom-right corner of your data. Press Ctrl+Period to move to the next corner of your data. Because you are at the bottom-right corner, it takes two presses of Ctrl+Period to move to the top-left corner. Although this moves the active cell, it does not undo your selection. Although I always use Ctrl+Period twice, I should probably learn Ctrl+Backspace to bring the active cell back into view. That will be my new trick for next week.
5) Pop open the right-click menu using Shift+F10
When I do my seminars, people always ask why I don’t use the right-click menus. I don’t use them because my hand is not on the mouse! Pressing Shift+F10 opens the right-click menu. Use the up/down arrow keys to move to various menu choices and the right arrow key to open a fly-out menu. When you get to the item you want, press Enter to select it.
6) Cross tasks off your list with Ctrl+5
I love to make lists, and I love to cross stuff off my list. It makes me feel like I’ve gotten stuff done. Select a cell and press Ctrl+5 to apply strikethrough to the cell.
7) Date-stamp or time-stamp using Ctrl+; or Ctrl+:
Here is an easy way to remember this shortcut. What time is it right now? It is 11:21 here. There is a colon in the time. Press Ctrl+Colon to enter the current time in the active cell.
Need the current date? Same keystroke, minus the Shift key. Pressing Ctrl+Semicolon enters the current time.
Note that this is not the same as using =NOW() or =TODAY(). Those functions change over time. These shortcuts mark the time or date that you pressed the key and the value does not change.
8) Repeat the last task with F4
Say that you just selected a cell and did Home, Delete, Delete Cells, Delete Entire Row, OK. You need to delete 24 more rows in various spots throughout your data set.
Select a cell in the next row to delete and press F4, which repeats the last command but on the currently selected cell.
Select a cell in the next row to delete and press F4. Before you know it, all the 24 rows are deleted without you having to click on Home, Delete, Delete Cells, Delete Entire Row, OK 24 times.
The F4 key works with 92% of the commands you will use. Try it. You’ll love it. It’ll be obvious when you try to use one of the unusual commands that cannot be redone with F4.
9) Add dollar signs to a reference with F4
That’s right — two of my favorites in a row use F4. When you are entering a formula and you need to change A1 to $A$1, click F4 while the insertion point is touching A1. You can press F4 again to freeze only the row with A$1. Press F4 again to freeze the column with $A1. Press again to toggle back to A1.
10) Find the one thing that takes you too much time
The shortcuts in this article are the ones I learned over the course of 20 years. They were all for tasks that I had to do repeatedly. In your job, watch for any tasks you are doing over and over, especially things that take several mouse clicks. When you identify one, try to find a shortcut key that will save you time.
When you perform commands with the mouse, do all the steps except the last one. Hover over the command until the tooltip appears. Many times, the tooltip tells you of the keyboard shortcut.
.
