Useful Keyboard Shortcuts

---

1. 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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.

7. 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.

8. 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.

9. Alt+= for AutoSum

   Press Alt+= to invoke the AutoSum function.

10. 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 

11. 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. 

12. 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! 

13. 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.

14. 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.

15. 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.

16. 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.

17. 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.

18. 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.

19. 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.

20. Ctrl+Shift+4 to Apply Currency Formatting

    Ctrl+Shift+4 or Ctrl+$ applies a currency format with two decimal places.

21. Ctrl+Shift+5 for Percent Format

    Ctrl+Shift+5 or Ctrl+% applies a percentage format with 0 decimal places.

22. 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.

23. 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.

24. 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!

25. Ctrl+F1 to hide or show the Ribbon

    To toggle the Ribbon between Pinned and Hidden, use Ctrl+F1.

26. 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.

27. 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.

28. 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.

29. 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.

30. 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.

31. 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.

    
    

32. 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.

33. 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.

34. Alt+T, M, S to Change Macro Security.

    This shortut is really useful now that the settings are buried deep in Excel options.

35. Alt+T, I to Activate Add-ins

    Alt+T, I is faster than File, Options, Add-Ins, Manage Excel Add-ins, Go.

36. 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.

37. 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.

38. 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.

39. 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.

40. 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.

41. 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.

42. 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.

43. 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.

44. 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.

45. 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.

46. 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.

47. Ctrl+C to Copy

48. Ctrl+V to Paste

49. Ctrl+X to Cut

50. Ctrl+B for Bold

51. Ctrl+I for Italics

52. Ctrl+U for Underline

53. Ctrl+N for New Workbook

54. Ctrl+P to Print

55. Ctrl+T (or Ctrl+L) to Format as Table

56. 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