Utilizing Excel Custom Toolbars & Menus

Last Updated on December 29, 2016

If you’ve ever used Microsoft Excel, you’ve noticed that there are tons of buttons on toolbars that you can use at different times on different document types in certain contexts. These buttons issue all sorts of handy, powerful commands. Sometimes, the exact command you want can be hard to find if you dive off into the deep end of Excel and start using some of its more powerful capabilities.

Excel custom toolbars and menus can be a huge help because they save you time the you would normally spend searching for them. Let’s spend some time exploring how you can create your own toolbars and command groups and manipulate existing toolbars to make you more efficient with Excel.

Quick Access Toolbar

Let’s begin with the Quick Access Toolbar. The Quick Access Toolbar is located in the upper left-hand side of the Excel window. Some of the commands included on this toolbar are Save, Save As, Print, etc. If you click the down arrow on the right-hand side of the quick access toolbar, you can quickly add commonly used commands from a list that drops down. This will in effect pin the command to the Quick Access Toolbar so that it is always visible and can be executed quickly.

You can also add commands to the list if you don’t see them in the drop down. You might have a command that you need to execute so often that you want to place it where you can easily find it. To do this, simply right-click anywhere on the toolbars at the top of the screen and select the “Customize the Quick Access Toolbar” option from the context menu that pops up.

This brings up the “Excel Options Dialog”, which will let you add commands from any of the predefined command groups in Excel’s extensive list. You just have to find the specific group you are looking for, find the specific command you want, and then move that command over to the available list by selecting it and then clicking the “Add” button. The command will appear in the list and you can select it to now be pinned to the Quick Access Toolbar.

If you make a mistake and add commands that you don’t want or need or that are redundant, you can simply open the Options dialog backup and click the Reset button. You have the option to only reset the changes you have made on the Quick Access Toolbar or all of the customizations that you have made for Excel. Be careful here that you don’t reset more than you want and lose something that you were using.

You can also import a toolbar setup from an exportedUI file. You might like the setup that your coworker has. Then can export their command setup and then you can import it. You can also export your setup to be imported on another machine. This is a very useful way to share custom commands that someone may have written in VBA script on their version of Excel.

Ribbon Toolbar

The second type of toolbar is the ribbon toolbar. To customize the ribbon, you can open the Excel Options dialog in the same way. Just right-click anywhere on the ribbon and select “Customize the Ribbon”. When the Excel Options Dialog comes up, this time, it will be a little different than when we customized the Quick Access Toolbar. You will have new options available to you that only pertain to the ribbon.

The ribbon toolbar is tabbed so that the commands are categorized and they are only a click or so away instead of having to go and create custom toolbars and add them as in previous versions of Excel. The main tabs that are turned on by default are listed in the box on the right side and they will have a check to the left if they are currently visible. You can add commands to any of the existing tabs by selecting a command on the left and clicking the Add button. If you add the wrong command, just select it and click the Remove button.

You can also change the list of available commands by selecting a new category from the drop-down list at the top of the available commands box. Also, you can create your own tabs that will show up in the ribbon and you can also create new groups of shortcuts to be on the tabs. You can make your customized tab as granular as you want and construct your own ribbon toolbar from scratch. You can also hide any of the tabs that you don’t want to see by unchecking them in the list of currently visible tabs.

The customization of toolbars is handled all in one place in the latest versions of Excel, making it very easy to customize your experience. After you get everything just the way you like it, you can then export your command setup and import it on another machine or copy it to a different location to backup up your customizations. This is very useful for sharing or if you just want to keep things consistent across multiple computers. The new Excel Options dialog gives you almost endless flexibility in how you can customize your Excel user experience.

To learn more about utilizing custom toolbars and menus for Excel, contact ONLC for more information on Excel classes and certifications.