By Bob Umlas, Excel MVP
Excel has hundreds of keyboard shortcuts. In this blog, I’ll describe some of my favorites
Ctrl+Shift+ numbers 1 thru 7, and 0 (and the tilde key, ~) can be used for formatting. Let’s start with this sample worksheet, and assume A1 thru B2 is already selected and contains 12345:
Ctrl+shift+1 – add comma and 2 decimal positions:
Ctrl+shift+2 – Change to a time format:
Ctrl+Shift+3 – Change to a date format, but Day-Month-Year:
Why October 18? Excel stores dates counting the number of days since Jan 1, 1900 (that’s day #1). So, day #12,345 is October 18, 1933!
Ctrl+Shift+4 – Change to currency (Easy to remember because of the “$” on the 4):
Ctrl+Shift+5 – Percentage notation (Easy to remember because of the “%” on the 5):
Ctrl+Shift+6 – Change to scientific notation (Easy to remember because of the “^” on the 6, meaning raise to the power. That is, 3^2 is 3 squared, or 9):
Ctrl+Shift+~ (tilde – character to the left of the 1)- restore to General formatting:
Cttrl+Shift+7 – Put a border around the selection:
Ctrl+Shift-0 – Remove the border from the selection.
Ctrl+Shift-8 and Ctrl+Shift-9 don’t do anything regarding formatting.
Ctrl+U will underline, Ctrl+B will bold and Ctrl+I will italicize cells.
Ctrl+minus will delete cells. If a whole row is selected (or more than one), it will be deleted. If a whole column is selected, (or more than one), it will be deleted. If less than a whole row or column is selected when you use Ctrl+minus, Excel will give this dialog:
Ctrl+Plus inserts cells, with the same “rules” as deleting cells. The dialog presented if not a whole row or column is selected is:
Ctrl+W will close a workbook (and will prompt for saving if not already saved).
Ctrl+N will create a new workbook.
Ctrl+O will prompt for opening a workbook.
Ctrl+P will print a worksheet.
A reminder that Ctrl+Z, Ctrl+X, and Ctrl+C are undo, cut, and copy.
Ctrl+R will fill a selection to the Right, and Ctrl+D will fill a selection Down.
Ctrl+A selects the current region, Ctrl+A a 2nd time selects the whole sheet.
Ctrl+F brings up the Find dialog, and Ctrl+H brings up the Find and Replace dialog.
Ctrl+; inserts the current date, and Ctrl+: inserts the current time.
Ctrl+arrow (up, down, left, right) will move the cursor to the beginning or end of the next “block” of cells. For example, starting with this sheet (notice the active cell is C1):
Ctrl+down will select C4. From there, Ctrl+right will select F4. Ctrl+Down will select F11. Ctrl+Left will select C11. Ctrl+down from C11 will select C1048576!!
Holding the shift key with the above arrow commands will keep whatever the active cell was still active! For example, if C4 is active when you use Shift-Ctrl+Down, you’d see this:
If you have a shape selected, then Ctrl+D will duplicate the shape. Here’s what that looks like after Ctrl+D:
Shift/F2 (the F2 key) will insert a comment!
Okay, now for a totally different set of keyboard shortcuts. You have access to all the commands in the ribbon from the keyboard. If the first key you hit is either a slash or the Alt key, the ribbon will take on letters. For example, here’s part of the ribbon after pressing a slash:
The letters in the small black squares can be used to access that ribbon tab. For example, if you press “A” at this point (indicating the Data tab) you would then see this (truncated):
Not only are you now on the Data tab, but you have a whole new set of letters you can further select.
So, if you started by pressing “/AT”, meaning Data, then Filter, you might see the filter dropdown arrows (depending on where the selection was):
You can learn or memorize your favorite keystrokes. Each of the above examples works for beginning with slash or Alt.
Also, if you have a command that you use a lot, but you have to keep changing tabs to get to that command first, you can put that command in the QAT (Quick Access Toolbar). Those are the little icons above the ribbon. My QAT is already quite large and looks like this:
The little arrow on the far right side contains “common” commands you can add or delete from your QAT:
The checked items are already being used by me. But let’s say you want to have a quick access to creating a pivot table. You can go to the Insert tab, right click on the Pivot table icon, then choose the command to Add to Quick Access Toolbar.
Then, the right of your new QAT would look like this:
Finally, you can visit the “backstage view” for more options. (That’s what you’re looking at when you click on the File menu). You can either get there from the right-click of a ribbon command:
Or by File/Options/Quick Access Toolbar:
Either will take you here:
The right side has the list of items already in your QAT (in this case, my QAT) Notice the dropdown above this in which you can have separate QATs depending on whether you choose “For all documents (default)”, or only for the current workbook!
The left side lists all the popular commands. You can change this dropdown to many choices:
My favorite is to select “All commands” where you’ll be able to find a QAT for any command in Excel!
You can tell from the small scroll indicator that this is a very long list!
Go make your Excel life easier!
If you are interested in bringing Excel MVP Bob Umlas into your company (either virtually or traditionally) to do Excel consulting and/or training work, contact firstname.lastname@example.org.
About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).