Affiliate Disclosure: By buying the products we recommend, you help keep the lights on at MakeUseOf. Read more.
Full proficiency in Microsoft Office demands the use of shortcuts, and we’ve shown you how to find every keyboard shortcut you could ever need. If you’ve exhausted that list and still found some functionality that doesn’t have a corresponding keyboard shortcut, you might be wondering if you’re out of luck.
The good news is that you’re not out of luck! Excel supports custom shortcuts, and we’ll show you how to create your own today. Get ready to take your shortcut-fu to the next level!
A Note on Default Keyboard Shortcuts
Unfortunately, it’s not all good news in the custom shortcut realm. While Microsoft Word lets you customize your own keyboard shortcuts, Excel doesn’t offer the functionality to override standard shortcuts. There are three main types of keyboard shortcuts:
- Standard shortcuts, like CTRL + I for italics, which you cannot override in Excel.
- Alt shortcuts, where you press the ALT key to activate shortcuts to Ribbon items. For example, pressing ALT > N > T will select the Insert tab on the ribbon, followed by the Table option. Notably, you can use these types of shortcuts to navigate your computer with just keyboard shortcuts.
- Macro shortcuts, which are customized. We’ll discuss these in a bit.
So, while you can’t manipulate any of the default shortcuts, you can still access quite a bit of functionality on the Ribbon and using macros; thus we’ll use those options to make custom shortcuts. Even though they’re not custom, you should still take some time to review the top everyday shortcuts for Excel, too.
1. The Quick Access Toolbar
The Quick Access Toolbar (QAT) is a super helpful strip of commands that always stays at the top of your screen. By default it only includes a few commands such as save, undo, and redo (which can be removed since they already have accessible shortcuts), but you can add many more commands to the QAT.
The best part? Remember that Alt codes let you access anything on the Ribbon. If you press ALT, you should notice the pop-up keys also appear near the QAT, giving you a one-step shortcut to any Excel command you wish!
To get the bar set up to your liking, click the drop-down arrow to the far right of the current icons and choose More commands… to open the customization window. Here, you’ll see a list of available commands in the left box and your current QAT commands on the right.
If you’d like, you can click any command on the right and click the Remove button to toss it, or use the arrow buttons to the right of the box to re-order the current items. Once you’ve got that sorted, check out the left box to find commands you want to add.
By default, the drop-down box above will only be showing the Popular Commands, but you can change it to Commands Not in the Ribbon if you want to avoid duplicating what’s already on the Ribbon. You can also show All Commands, but be warned there’s quite a list.
Have a look through the list and select any functions that you want to have instant access to. The QAT allows for lots of commands, so don’t be afraid to pick several of your favorites or even find some new features. If you want to create groupings of similar functions, there’s an option called <Separator> that lets you add a divider between icons.
If you want to preserve your work, you can use the Import/Export button on the page to export your customizations to a file. Once you’ve got everything in order, just click OK to return to Excel with your new and improved QAT. When you want to access one of its functions, press ALT followed by the number of the command to execute it right away.
This is a lot faster and way less annoying than hunting through menus to find the right command!
Macros are an extremely useful, but often overlooked feature of Office that allow you to record a series of actions and automatically play them back. If you have trouble remembering complex formulas, for example, you could create a macro to automatically apply the formulas to specific cells.
Macros are really just Visual Basic code running in the background to make Excel perform actions, but you don’t have to know how to program to take advantage of macros. To get started with using them, enable the Developer Ribbon tab by going to File > Options and choose Customize Ribbon on the left pane. On the right side, make sure Developer is checked, then press OK to return.
Now we can record our first macro. Head to the Developer tab on the Ribbon and choose Record Macro. Give it a name to remember it by (it can’t have spaces) and then assign it a key that works for you. The box shows CTRL + another key, but you can also add Shift in there by holding it while you select a key. Bear in mind that you can override standard keyboard shortcuts here, so if you make your macro run on CTRL + Z you won’t be able to use that to Undo.
Under Store macro in: select Personal Macro Workbook. This is a file that allows you to share macros between Excel workbooks, which is perfect for defining shortcuts. If we wanted to run macros on only one workbook, we would change this setting. Give your macro a brief description if you like, then click OK.
Important: Now, everything you do is being recorded by the macro. Any cell you select, font changes you apply, or characters you type are recorded by the macro. So you could, for example, make a macro that selects cell B3, types “test,” and formats it as bold. Once you’ve performed exactly the action you want to record, select Stop Recording on the Developer tab to end the macro.
After this, you can access it by choosing Macros from the left side of the Developer tab. This shows you a list of your macros, and you can press Edit to view the Visual Basic code for the macro — this is a good way to make sure you didn’t record any extra steps, even if you don’t fully understand the code. Once you’re satisfied, you can press the key combo you assigned at any time to run your macro.
— Alyssa Spurling (@AlyssaSpurling) February 9, 2016
Bonus tip: You can combine macros and the QAT! Once you’ve saved a macro, open up the QAT menu again and change the Choose commands from box to Macros. Look for the name of the one you like, and you can add it to the QAT just like any other action! Thus, if you have trouble remembering your macro shortcut combos, you can set them to ALT and a number in the QAT!
What you do with macros is up to you, but there are tons of possibilities. Think of anything you do in Excel on a regular basis that could be automated, and try making a macro for it. Automating these tedious tasks could save you hours, if you started using them regularly!
What Are Your Top Shortcuts?
Now you don’t have to waste time pecking out repetitive tasks or hunting through Excel menus to find that one function. Shortcuts take a while to set up and will differ for everyone, but they’re essential for Excel efficiency. Macros really aren’t scary once you’ve done them, and there’s no reason not to use the QAT. Shortcuts rule!
Don’t let your shortcut reign now! Check out the top keyboard shortcuts for Word, Excel, and PowerPoint and if you use a Mac, take a look at these custom keyboard shortcuts you can use on macOS.
What shortcuts have you created with these methods? I’m looking forward to hearing about your best macros in the comments!
Image Credit: holding sticky note by GooDween123 via Shutterstock