Microsoft Excel for Windows Keyboard Shortcuts and Tips
Is it a keyboard shortcut or a hotkey? What do I know? I use them interchangeably. Most websites call them keyboard shortcuts, but I use both to make it easier for people to find on the internets (j/k).
Not all of these tips work in Excel for the Mac. Unfortunately, the Mac version of Excel is a big step backwards from the Windows version.
I love keyboard shortcuts. If I can keep my hands on the keyboard and off the mouse, I work much faster.
You may notice that my keyboard shortcuts (hotkeys) show an upper-case letter, like Ctrl+O for Open File. This does not mean Ctrl+Shift+O. Keyboard shortcuts are case sensitive, so to avoid confusion, I will always indicate "Shift" when the Shift key is required. For instance, in my setup, I have a hotkey assigned to Ctrl+Shift+H. This is because I have a printing macro that is assigned to Ctrl+Shift+H, but Ctrl+H is Find and Replace.
The Most Import Excel Keyboard Shortcut
- Ctrl+Y – Redo (honorable mention)
Open, Save, Print, Etc. Keyboard Shortcuts
- Ctrl+O – Open file
- Ctrl+S – Save file
- F12 – Save As
- Ctrl+F4 – Close file. It will ask you to save if the open spreadsheet has been modified since it was last saved.
- Ctrl+F6 – Switches to the next open workbook window when more than one workbook is open.
- Ctrl+Shift+F6 – Same as Ctrl+F6, but it cycles through the open workbooks in reverse order.
- Ctrl+P or Ctrl+F2– Print Preview. I recommend using Ctrl+P, because it is a pretty universal keyboard shortcut for printing in Windows.
Navigation and Selection Keyboard Shortcuts
- F5 – Go to a specific cell or named range
- Ctrl+Arrow key – Move to next section or block of text
- Shift+Arrow Key – Extend the selection of cells by one cell.
- Ctrl+Shift+Arrow Key – Extend the selection of cells to the end of the section (or first non-blank cell)
- F8 – Turns on extend mode for selecting cells. Use arrow keys or Ctrl+Arrow Keys to extend. Press F8 again to turn it off.
- Shift+F8 – This is really cool. It allows you to leave your current selection and go start a new one without deselecting the first selection. Try it by starting with a selection of cells, press F8, use the arrow keys to move to a new location, and then Shift+Arrow Keys to start an additional selection. Just key pressing Shift+F8 to start again.
- Ctrl+Shift+8 (Ctrl+*) – Select current region
- F2 – Edit selected cell
Keyboard Shortcuts for Formulas
- Ctrl-F3 - Opens the Name Manager dialog box
- Ctrl+Shift+F3 – Create Names from Selection
- Ctrl+` – Show Formulas
- F9 – Calculate Now
- Shift+F9 – Calculate Sheet
Clean Up Your Spreadsheet
- Ctrl+F – Find
- Ctrl+H – Find and Replace
- F7 – Spell Check
- Shift+F7 – Thesaurus
Faster access to the Open Dialag Box
I don't like clicking on the File Tab, Open, and then Browse just to pull up the Open Dial Box. Even pressing Ctrl+O takes me to the same place. To fix this, go to the File Tab and select Options. Then select Save from the sidebar. About the fifth line down, put a check in the box next to "Don't show the Backstage when opening or saving files." Now, Ctrl+O and F12 jump straight to the dialog box and skip all of the Backstage clicking.
Customize the Quick Access Toolbar
Unfortunately, the days of changing your Microsoft Excel keyboard shortcuts are gone. To create a keyboard shortcut, you basically have two options:
- Create a Macro with an Assigned keyboard shortcut
- Customize the Quick Access Toolbar and use Alt+0 through Alt+9 to access the commands.
The Quick Access Toolbar is very easy to use. It is the upper left toolbar above the Ribbon. The rightmost icon allows you to customize the toolbar by clicking on "More Commands..." You can also Right-Click on the Quick Access Toolbar and choose "Customize the Quick Access Toolbar."
The right-hand list is the items currently in the Quick Access Toolbar. It is often simply Save, Undo and Redo.
The left-hand list box is a list of the commands that can be added. Above the list is a drop-down box that allows you to choose either a subset of the available commands or all of them. My commands are:
- Alt+1 – Paste Values
- Alt+2 – Undo (also Ctr+Z)
- Alt+3 – Redo (also Ctrl+Y)
- Alt+4 – Insert Sheet Rows
- Alt+5 – Autofit Column Width
- Alt+6 – Set Print Area
- Alt+7 – Delete Sheet Rows
- Alt+8 – Print Preview and Print
- Alt+9 – Comma Style
My Alt+2 and Alt+3 might seen unnecessary since you also could use Ctrl+Z and Ctrl+Y. I have an explanation for that. Originally, my Alt+1 was Save. I simply added 4-9. When I wanted to add Paste Values, I replaced Alt+1 Save so that my other 4-9 keyboard shortcuts would remain in order. Eventually, I will probably add two more commands and replace Alt+2 and Alt+3, since I am already in the habit of using Ctrl+Z and Ctrl+Y for Undo and Redo. One advantange to having Alt+2 and Alt+3 as Undo and Redo is t give you a pull-down menu so that you can see a history of actions to Undo or Redo.
Remember that every time you add or delete a command, you are potentially re-numbering the Alt+key combinations.
As crazy as this sounds, you can have 44 buttons on the quick access toolbar. To reach them, use
- Alt+0 thru Alt+9 – First 9 commands
- Alt+0+9 thru Alt+0+1 – Commands 10-18
- Alt+0+A thru Alt+0+Z – Commands 19-44
As of Excel version 16.59 on the Mac, you cannot use hot keys. For some reason, Microsoft likes to have the Mac version of its products inferior to its Windows version.
Backing up and Restoring the Quick Access Toolbar
To back up and restore the Quick Access Toolbar, follow these steps:
- Click the File tab.
- Click Options
- Click Quick Access Toolbar
- Down in the lower-right-hand corner, click the Import/Export button. Use Export to back up all of your customizations to a file and import to restore the customizations. You can also use this to transfer your customizations to another computer or login.
Unforunately, as of Excel version 16.59 on the Mac, you cannot use import/export.
Microsoft Excel Print Macro
Ctrl+P is a handy keyboard shortcut for bringing up the Print Preview screen. You can choose your printer and change many settings like orientation, page size, margins, and scaling.
However, there are sometimes that I have all the settings exactly the way I want them, with the exception of choosing a printer. My company uses imaging software. Let's call it ImageHooey . I often print one copy to mail, plus another copy into ImageHooey.
This little macro lets you change the printer and then print using in the same dialog box. Make sure you assign a hotkey. I chose Ctrl+Shift+H. You simply press Ctrl+Shift+H, then the first letter of the printer, like "H" for "HP OfficeJet," and then the Enter key. With three keystrokes, you changed the printer and printed – much faster than using the mouse. You can then use the the macro again, but press "I" for "ImageHooey Printer." Then there is a copy loaded into ImageHooey. It only took six keystrokes to do all of your printing.
I clearly could have made one macro that prints a copy to an external printer and another copy to ImageHooey. That would have meant even fewer keystrokes. However, I use this macro at times when I am only printing to one location. I also don't always print the physical copy to the same printer.
The first line of the macro brings up the Printer Setup Dialog box. You can use the first letter of the printer name to change printer selection.
You can click on the Setup button to change some settings.
With the dialog box open and your printer chosen, press Enter to print one copy of the page. Press Esc to exit out and not print.
I put this macro in my hidden PERSONAL.xlsb workbook. It is available every time I use Microsoft Excel.
' PrintToJL Macro
' Hotkey Ctrl+Shift+H
ans = Application.Dialogs(xlDialogPrinterSetup).Show
If ans = True Then
Copies:=1, Collate:=True, _
Else: Exit Sub