Excel 2020: Favorite Keyboard Shortcuts
December 28, 2020 - by Bill Jelen
As I started polling readers about their favorite Excel tips, a large number of them were keyboard shortcuts.
Following keyboard shortcuts are presented in order of popularity. If a lot of readers suggested a tip, it is at the top. After the first eight or so, they are then sorted by my subjective sequence.
Ctrl+1 to Format a Selection
Ctrl+1 (the number one) works to format whatever is selected. Whether it is a cell, SmartArt, a picture, a shape, or the March data point in a column chart, press Ctrl+1.
Ctrl[+Shift]+Arrow to Navigate or Select
Your cell pointer is sitting at the top of 50K rows of data, and you need to get to the bottom. If you have a column with no blanks, press Ctrl+Down Arrow to jump to the end of the data set.
In the following figure, Ctrl+Down Arrow will jump to K545. Ctrl+Left Arrow will jump to A1. Ctrl+Right Arrow will jump the gap of empty cells and land on N1.
Add the Shift key in order to select from the active cell to the landing cell. Starting from A1 in the above figure, press Ctrl+Shift+Down Arrow to select A1:A545. While still holding down Ctrl+Shift, press the Right Arrow Key to select A1:K545. If it seems awkward at first, try it for a few days until you get the hang of it.
Ctrl+. to Jump to Next Corner
While you have a large range selected, press Ctrl+. to move to the next corner of the selection. If the selection is rectangular, you move in a clockwise fashion. From the bottom-right corner, press Ctrl+. twice to move to the top left.
Ctrl+5 for Strikethrough
This is great for crossing things off your to-do list. Why 5? If you are making hash marks, the fifth hash mark crosses out the first four.
Ctrl+* to Select Current Region
This one is easier if you have a number keypad so you don’t have to press Shift to get to an asterisk. If I could slow down enough to stop pressing Ctrl+Shift+Down Arrow followed by Ctrl+Shift+Right Arrow, I would realize that Ctrl+* is much shorter and does not get tripped up by blank cells. It is really superior in every way to keyboard tip #2. But my muscle memory still prefers tip #2.
Ctrl+Enter to Copy Formula into Entire Selection
Ken Puls, who is the king of Power Query, says, “You would think my favorite Excel tip would be Unpivot with Power Query, but my favorite all-time is Ctrl+Enter.” Say that you want to enter a formula into 400 cells. Select the 400 cells. Type the formula in the first cell. Press Ctrl+Enter, and Excel enters a similar formula in all cells of the selection.
Gavin White points out another use. You enter a formula in G2. You need to copy the formula down but not the formatting. Select G2:G20. Press the F2 key to put the cell in Edit mode. When you press Ctrl+Enter, the formula is copied, but no formatting is copied.
Ctrl(+Shift)+; to Time or Date Stamp
Press Ctrl+Shift+: to enter the current time. Press Ctrl+; for the current date. Note the shortcut enters the current time, not a formula. To put both the date and time in one cell, type either keystroke, a space, then the other keystroke. Excel will interpret it as the proper date and time.
Ctrl+Backspace to Bring the Active Cell into View
This is a great trick that I never knew. Say that C1 is the active cell. You’ve used the scrollbars, and now you are looking at ZZ999. To bring the window back to encompass the active cell, press Ctrl+Backspace.
Alt+= for AutoSum
Press Alt+= to invoke the AutoSum function.
Ctrl+Page Down and Ctrl+Page Up to Jump to Next Worksheet
If you need to move from Sheet1 to Sheet5, press Ctrl+Page Down four times. If you are at Sheet9 and need to move to Sheet3, press Ctrl+Page Up six times.
Ctrl+Click to Select Noncontiguous Cells
If you have to select two regions, select the first one, then hold down Ctrl while clicking on other cells or regions.
Tab to AutoComplete
This one is maddening. You type
=VLto start VLOOKUP. The AutoComplete shows that there is only one function that starts with VL. But if you press Enter, you get a
The correct way to choose VLOOKUP is to press Tab!
Shift+F8 to Add to Selection
Select the first range. Press Shift+F8, and you are in Add to Selection mode. Scroll anywhere. Select the next range. Then select another range. And so on, without ever touching Ctrl. To return to normal, press Esc.
A bonus tip from Bill Hazlett: if you select A1, press F8, then click in S20, you will select from A1:S20. Press Esc to exit the Extend Selection mode. Watch for the indicator in the status bar:
Ctrl+Spacebar and Shift+Spacebar to Select an Entire Column or Row
Ctrl+Spacebar selects a whole column. Shift+Spacebar selects a whole row. How can you remember which is which? The “C” in Ctrl stands for the “C” in column. Also, the “S” in Shift is adjacent in the alphabet to the “R” in row. Another way to remember which is which: The Shift key is much longer (like a row!) than Ctrl.
Ctrl+` to See All Formulas
Many folks in the United States think this is Ctrl+~, but it is actually the grave accent to toggle into and out of Show Formulas mode.
F3 to Paste a Name into a Formula
I am not a huge fan of this, since you can start typing the name and then choose from AutoComplete. But I know the trick has its fans, including Mike Girvin and Johan van den Brink.
Ctrl+Shift+1 to apply Number Formatting
I had never memorized these, but I am going to start using some of them. Ctrl+Shift+1 (also known as Ctrl+!), will apply a number format, 2 decimals, thousands separator, and negatives shown with a minus sign. The other five make some reasonable sense, as described below.
Ctrl+Shift+2 to Apply Time Formatting
Ctrl+Shift+2 or Ctrl+@ applies a time formatting. Say that you want to meet for dinner @ 5 o'click. Long before it became associated with e-mail addresses, the @ inferred time.
Ctrl+Shift+3 to Apply Date Formatting
Ctrl+Shift+3 or Ctrl+# applies a date formatting. The # symbol looks a bit like a calendar, if you lived in an alternate universe with three weeks per month and three days per week.
Ctrl+Shift+4 to Apply Currency Formatting
Ctrl+Shift+4 or Ctrl+$ applies a currency format with two decimal places.
Ctrl+Shift+5 for Percent Format
Ctrl+Shift+5 or Ctrl+% applies a percentage format with 0 decimal places.
Ctrl+Shift+6 for Scientific Format
Ctrl+Shift+6 or Ctrl+Shift+^ applies scientific notation. 1.23E+07 infers an exponent. A carat(^) is used to enter exponents in Excel.
Alt+Enter to Control Word Wrap
To move to a new row in the current cell, press Alt+Enter. Isn’t this the same as turning on Word Wrap? Sort of, but Alt+Enter lets you control where the words wrap.
Ctrl+[ to Jump to Linked Cell
You are in a cell that points to Sheet99!Z1000. Press Ctrl+[ to jump to that cell. This works if you have links between workbooks, even if the other workbook is closed!
Ctrl+F1 to hide or show the Ribbon
To toggle the Ribbon between Pinned and Hidden, use Ctrl+F1.
Alt+F1 to Chart the Selected Data
Select some data. Press Alt+F1. You get a chart of the data. You might remember F11 doing the same thing. But F11 creates the chart as a chart sheet. Alt+F1 embeds the chart in the current sheet.
Shift+F11 to Insert a Worksheet
I never knew this one, but it makes sense as a corollary to F11. If F11 inserts a chart sheet, then Shift+F11 inserts a new worksheet. You can also use Alt+I, W to insert a worksheet, Alt+I, R to insert a row, or Alt+I, C to insert a column.
Alt+E, S, V to Paste Values
I can do Alt+E, S, V, Enter with my eyes closed. Alt+E opened the Excel 2003 Edit menu. S chose Paste Special. V chose Values. Enter selected OK.
Alt+E, S, T to Paste Formats
Alt+E, S, T, Enter pastes formats. Why t instead of f? Because Alt+E, S, F already was in use to paste formulas.
Alt+E, S, F to Paste Formulas
Alt+E, S, F, Enter pastes formulas without copying the cell formatting. This is handy to prevent cell borders from copying along with the formula.
Alt+E, S, W to Paste Column Widths
Alt+E, S, W pastes column widths. This is great to use with a block of columns. In the following figure. select A1:H1, copy, then select J1 and Alt+E, S, V, Enter to copy all 8 column widths.
Alt+E, S, D, V to Paste Special Add
Alt+E, S, D, V does a Paste Special Add, but does not screw up the formatting.
Alt+E, S, E to Turn Data Sideways
Alt+E, S, E does a Transpose. To see all the possibilities, press Alt+E, S and then look for the underlined letters.
Alt+T, M, S to Change Macro Security.
This shortut is really useful now that the settings are buried deep in Excel options.
Alt+T, I to Activate Add-ins
Alt+T, I is faster than File, Options, Add-Ins, Manage Excel Add-ins, Go.
Ctrl+Shift+L to Enable the Filter Dropdowns
Toggle the filters on or off with Ctrl+Shift+L. In Excel 2013 and earlier, pressing Ctrl+Shift+L would scroll your screen to the end of the data set. Press Ctrl+Backspace to bring the active cell back in to view. Or, press and release Alt, A, T.
Hold Down Alt to Snap to Grid
If you are drawing any shape, Alt will cause that shape to exactly line up with the borders of cells.
Ctrl+W to Close a Workbook but Leave Excel Open
If you have one workbook open and you click the “X” in the top-right corner, you close Excel. Ctrl+W closes that workbook but leaves Excel open.
F5 to Sneak into a Hidden Cell
You’ve hidden column D, but you need to see what is in D2. Press Ctrl+G or F5 to open the Go To dialog. Type D2 and press Enter. The cell pointer moves to the hidden cell D2, and you can see the value in the formula bar. You can now use the Down Arrow key to move within the hidden column D, and you can always see the value in the formula bar.
Alt+D, E, F to Convert Numbers Stored as Text to Numbers
Select a whole column and press Alt+D, E, F. The text numbers are converted to numbers. You are actually doing a default Text to Columns with this shortcut.
Alt+O, C, A to AutoFit a Column
Select some cells and press Alt+O, C, A to make the column wide enough for the longest value in the selection.
Ctrl+’ to Copy the Exact Formula Down (aka Ditto)
You have to sum in D10 and average in D11. Create the AutoSum in D10. When you press Enter, you are in D11. Press Ctrl+’ to bring the exact formula down without changing the cell reference. If D10 is
=SUM(D2:D9), the formula in D11 will also be
From there, you can press F2, Home, Right, AVERAGE, Delete, Delete, Delete, Enter. It sounds crazy, but the engineers at General Electric in Cleveland swear by it.
Ctrl+Shift+" to Copy the Cell Value from Above
Use Ctrl+Shift+” to bring the value from above into the current cell, eliminating any formula.
Hold down Alt while launching Excel to force it into a second instance
You might want each Excel workbook to have separate Undo stacks. This is one way.
Press F2 to toggle EDIT or ENTER while editing a formula in any dialog
Say you are typing
=VLOCKUP(in the conditional formatting dialog. You press the Left Arrow key to go back to fix the typo, but Excel inserts a cell reference. Press F2 to change the lower left corner of the status bar from ENTER to EDIT and you can use the arrow keys to move through the formula.
Alt+W, F, F to Freeze Panes
There are hundreds more shortcuts like the ones above which you can easily learn. Press and release Alt in Excel to see key tips for each tab in the Ribbon (plus numbered key tips for the Quick Access Toolbar. Press the letter corresponding to a Ribbon tab to see the key tips for all of the commands on that tab. In this particular case, clicking Alt, W, F reveals a third level of key tips, and Alt, W, F, F completes the command.
Ctrl+C to Copy
Ctrl+V to Paste
Ctrl+X to Cut
Ctrl+B for Bold
Ctrl+I for Italics
Ctrl+U for Underline
Ctrl+N for New Workbook
Ctrl+P to Print
Ctrl+T (or Ctrl+L) to Format as Table
Never Forget to Right-click
Many timesavers that are linked to the right mouse button that are often forgotten. Thanks to Colin Foster. Hey! This is not a keyboard shortcut! But wait…it is. You can open that right-click menu by using the Application key on the bottom-right side of your keyboard or Shift+F10. For more on the Application key, see Excel 2020: Quickly Convert Formulas to Values. Release the Application key and then press any underlined letter to invoke the command.
Title Photo: Juan Gomez at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.