Excel 2020: Favorite Keyboard Shortcuts

December 28, 2020 - by Bill Jelen

Favorite Keyboard Shortcuts. Photo Credit: Juan Gomez at Unsplash.com

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.

Thanks to Mitja Bezenšek, Alexa Gardner, Andrej Lapajne, Schmuel Oluwa, Jon Peltier, @ExcelNewss, and @JulianExcelTips.
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.

Thanks to Captain Excel, @Cintellis, José de Diego, Mike Girvin, Elchin Khalilov, Crystal Long, Paul Sasur, and @XLStudioWorks.
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.

Thanks to Crystal Long, and Steve McCready.
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.

Thanks to @Excelforo.
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.

Thanks to Crystal Long, Schmuel Oluwa, Ken Puls, Peter Raiff, Sven Simon, and Gavin Whyte.
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.

Thanks to Olga Kryuchkova, Roger Govier and Tim O’Mara.
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.

Thanks to Olga Kryuchkova and Schmuel Oluwa.
Alt+= for AutoSum

Press Alt+= to invoke the AutoSum function.

Thanks to Dawn Bjork Buzbee and Olga Kryuchkova.
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.

Thanks to Jeneta Hot.
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.

Thanks to Thomas Fries
Tab to AutoComplete

This one is maddening. You type =VL to start VLOOKUP. The AutoComplete shows that there is only one function that starts with VL. But if you press Enter, you get a #NAME? error.

The correct way to choose VLOOKUP is to press Tab!

Thanks to Ashish Agarwal.
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.

Thanks to Neil Charles.

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.

Thanks to Michael Byrne, Jeneta Hot, and Bob Umlas.
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.

Thanks to Matthew Bernath.
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.

Thanks to Olga Kryuchkova.
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!

Thanks to @Heffa100 and Bob Umlas.
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.

Thanks to Olga Kryuchkova.
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.

Thanks to Matthew Bernath and Laura Lewis.
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.

Thanks to Ron de Bruin.
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.

Thanks to David Hager and Andrew Walker.
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.

Thanks to Rickard Wärnelid.
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.

Thanks to Dave Marriott.
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 =SUM(D2:D9).

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.

Thanks to Bradford Myers.
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.