Quick Access Toolbar Treasures

The Quick Access Toolbar is a customizable row of icons that you can configure at the top of your screen. In this chapter, I’ll show you how to create a keyboard shortcut for practically any feature in Excel, including certain features that Microsoft has removed from the menu interface known as the Ribbon. Of course, the Quick Access Toolbar isn’t just about keyboard shortcuts if you prefer to use your mouse to carry out tasks.

In this chapter, you’ll learn about the following topics:

• Unhiding the Quick Access Toolbar if needed
• A tricky keyboard shortcut nuance
• Reposition or removing commands from the toolbar
• Time-saving commands
• Accessing commands that aren’t in the Ribbon
• Creating workbook-specific toolbars
• Creating shortcuts for Excel macros
• Migrating Quick Access Toolbar icons between computers
• Restoring legacy features in Excel

As you progress through this chapter, you...

Technical requirements

Everything in this chapter works in Excel for Windows. I will note which features and techniques are not available in Excel for macOS. Additionally, I will demonstrate the new Show Changes feature, which, presently, is only available in Excel for the Web instead of Excel Online but can be used to review changes to workbooks that have been edited in Excel for Windows or macOS. You can download the examples for this chapter from https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures/tree/main/Chapter03.

Exploring the Quick Access Toolbar

The Quick Access Toolbar debuted in Excel 2007 and has been a mainstay of Excel’s Ribbon up until the upcoming visual refresh that Microsoft is slowly rolling out to Microsoft 365 users as of this writing. As shown in Figure 3.1, the Undo and Redo commands have traditionally appeared on the Quick Access Toolbar:

Figure 3.1 – The traditional location of Undo and Redo

As you can see in Figure 3.2, the Undo and Redo buttons are moving to the Home tab of Excel’s Ribbon as part of the update to Excel’s user interface. Oddly, in these new Excel builds, the Quick Access Toolbar is automatically hidden if you haven’t customized it previously. These changes will occur unbidden by you once Microsoft pushes the update out to your computer:

Figure 3.2 – Undo and Redo appear on the Home tab and the Quick Access Toolbar is hidden

Tip

If you’re not sure which version...

Understanding the nuances of Quick Access Toolbar shortcuts

In Excel for Windows, every command on your Quick Access Toolbar is assigned an alphanumeric keyboard shortcut. (Excel for macOS does not offer this feature.) These keyboard shortcuts entail pressing the Alt key and a shortcut code determined by the position of a command in your Quick Access Toolbar. I’ll discuss how to reposition commands on the toolbar in the Repositioning and removing icons section a little later. As shown in Figure 3.6, when you tap the Alt key, onscreen tips will show you the keyboard shortcut associated with each command:

Figure 3.6 – The Quick Access Toolbar shortcut tips

The shortcut naming convention works as follows:

• The first nine shortcuts are assigned from 1 to 9, meaning you press Alt + 1 to access the first command, Alt + 2 for the second, and so on.
• The 10th through 18th shortcuts are assigned from 09 to 01, meaning you press Alt + 09 to access...

Adding Ribbon commands to the toolbar

It might feel redundant to add commands from the Ribbon to your Quick Access Toolbar, but doing so provides two benefits. First, the Quick Access Toolbar is always visible when the Ribbon is displayed, which means you don’t have to activate a specific Ribbon tab. Second, adding commands to the toolbar assigns keyboard shortcuts to commands such as Home | Center, that were deemed not to warrant a built-in shortcut.

Center text

A long-time quibble I’ve had with Excel for Windows is that you can’t press Ctrl + E to center text the way that you can in Microsoft Word, PowerPoint, or Outlook. There wasn’t a shortcut assigned to Ctrl + E for decades until the Flash Fill feature debuted in Excel 2013. with Ctrl + E now activates Flash Fill in Excel for Windows, while in Excel for macOS + E centers text, and Flash Fill doesn't have a shortcut. Fortunately, you can create your own keyboard shortcut for centering text...

Commands Not in the Ribbon

One of my favorite areas to explore in Excel is the Commands Not in the Ribbon listing in the Quick Access Toolbar section of Excel’s Options dialog box. Sometimes, you can find enhanced versions of built-in commands that streamline tasks or enable hidden functionality. In addition, you might be able to unearth and restore commands that Excel has removed from the Ribbon.

Enhanced commands

First, let’s look at versions of commands that offer functionality that you can’t accomplish through Excel’s Ribbon. I’ll show you how to add and then use the AutoFilter command in Excel for Windows, which enables you to filter a list based upon a single cell’s contents with one click:

1. Access the Quick Access Toolbar section of the Excel Options dialog box.
2. Click on Choose Commands From | Commands Not in the Ribbon.
3. Click once on AutoFilter in the left-hand column. Then, click on Add (or double-click on AutoFilter...

Workbook-specific toolbars

So far in this chapter, every change we’ve made to the Quick Access Toolbar has been a global change, meaning the commands will be available in every workbook that you open. You can also create toolbars that are specific to individual workbooks, which means that anyone else who opens the workbook will be able to access the custom toolbar. If you add Custom Views in the fashion I described in the previous section, most of the time, you’ll end up with a blank list consuming space on your toolbar. This technique requires Excel for Windows, as it is not possible in Excel for macOS. Here’s how to create a workbook-specific Quick Access Toolbar:

1. Access the Quick Access Toolbar section of the Excel Options dialog box.
2. Choose a workbook name from the Customize Quick Access Toolbar list above the second column, on the right-hand side, as shown in Figure 3.12:

Figure 3.12 – Workbook-specific Quick Access...

Creating shortcuts for Excel macros

Excel macros are programming code written in Visual Basic for Applications (VBA). This is a programming language that can automate repetitive tasks in Excel. I could devote a whole book to exploring the concept of macros, but I only have a small amount of space available. Allow me to share a technique that uses Excel’s Macro Recorder to change the cell color of one or more cells to yellow, which we’ll then tie to a Quick Access Toolbar icon. Let’s begin:

1. Select cell A1 of a blank worksheet.
2. Choose View | Macros | Record Macro….
3. Assign a name, such as YellowHighlight, in the Macro Name field of the Record Macro dialog box, as shown in Figure 3.13:

Figure 3.13 – Macro Recorder

Nuance

You cannot use spaces or punctuation when naming Excel macros. The first character of a macro name must be a letter or underscore. You can use numbers in the second position and beyond....

Transferring your Quick Access Toolbar between computers

You can export your toolbar settings to a special file that you can then import into Excel on another computer. You’re out of luck in this regard if you use Excel for macOS, but with Excel for Windows, users can conduct the following steps:

1. Access the Quick Access Toolbar section of the Excel Options dialog box.
2. Click on Import/Export | Export All Customizations, as shown in Figure 3.15:

Figure 3.15 – Exporting customizations

3. Optionally, specify a location and/or a new filename.
4. Click on Save.
5. Transfer the .exportUI file to your new computer, such as via email, or archive the file into a folder of your choice to create a backup of your current customizations.

Carry out these commands on any other Windows computer where you wish to apply the customizations (or restore the settings on your current computer):

6. Access the Quick Access Toolbar section...

Restoring legacy features

The term legacy feature is an epithet Microsoft uses for features that they consider have outlived their useful life. Microsoft rarely removes a feature outright from the software. Typically, deprecated commands get buried in the Commands Not in the Ribbon list, much like storing things in a basement or cellar. Let’s see how to bring back a couple of features that you might have thought were long gone.

Nuance

Microsoft did remove the Workspace feature in Excel 2013 and later. Workspaces were a collection of two or more workbooks and their onscreen layout. You still saved each workbook individually, but you could open the collection of workbooks in one fell swoop by opening the workspace file, which had a .XLW extension. You can still open workspace files in Excel, but you cannot create new workspaces. The workspace feature was a cousin of the Binder feature, which last appeared in Office 2000, and would allow you to create collections of Office...

Summary

In this chapter, first, you saw how the Quick Access Toolbar is evolving in Microsoft 365 and how to unhide the toolbar if needed. The Quick Access Toolbar allows you to create up to 44 custom keyboard shortcuts in Excel for Windows, but if you’re not aware of the nuance, you might be baffled why, let’s say, a heart or spade appears in your worksheet cell instead of carrying out a command. We always hold down the Ctrl key when executing a shortcut such as Ctrl + Z, but we must use an alternate behavior for the Alt key. Remember to tap the Alt key if you want to your number pad to execute a shortcut. You have complete control over the order of your icons, which enables you to prioritize the keyboard shortcuts.

We explored ways to create shortcuts for Ribbon commands that don’t have shortcuts, and we dug into the Commands Not in the Ribbon area to unearth commands that can streamline repetitive tasks. You can add commands to your default Quick Access...