[Excel] How to enable frequently used operations with a single keystroke (Quick Access Toolbar) : Tech TIPS

This article is for members only. If you register as a member (free), you can see everything.

Table of Contents

Target: Excel 2010/2013/2016

When using Excel, depending on the function, it can be tedious to repeatedly use a function with many steps, such as opening a drop-down list or calling a dialog to execute it. Have you ever wished you had a keyboard shortcut for this feature?

Excel's Quick Access Toolbar allows you to assign keyboard shortcuts to any function in Excel. Commands registered in the Quick Access Toolbar can be easily activated by pressing [Alt] + [Number].

Let's introduce the procedure with an example of registering an Excel operation (this is called a command) to draw a line in the selection. This is one of the most commonly used operations when creating tables in Excel.

There is a function to draw a line in the [Home] tab of the ribbon, but basically it is an operation that requires two clicks to select a pattern from the menu of the line. Also, diagonal lines require 4 clicks including the operation of calling the [Format Cells] dialog, selecting the line tab, and pressing the button, including the operation to close the dialog.

The quick access toolbar is located at the left end of the title bar by default. Click the button on the right end of the Quick Access Toolbar and select [More Commands] from the menu. You can also select [Options] - [Quick Access Toolbar] in the options (backstage view or [File] tab). Both settings give the same result, but using the Quick Access Toolbar menu requires fewer clicks.

Select "[Home] tab" from the "Select command" drop-down list in the upper left of the displayed window (hereafter referred to as the "Quick Access Toolbar Settings" window). This will display (filter) only the commands registered in the [Home] tab of the ribbon in the list below. In the middle of the list there is a command called "Lattice", so find it, select it and press the [Add] button in the center on the right. This time, select the added "lattice" from the list on the right, move it to the top of the list using the up arrow button, and press the [OK] button. A [Grid] icon is added to the top of the Quick Access Toolbar.

If you look at the right end of the list of commands, some commands are marked with horizontal triangles, rectangles, vertical bars, and so on. These are "non-simple" commands on the ribbon, such as displaying a list when clicked. Commands with some kind of mark on the right side of these lists require a selection action or the like to be executed, and are not very suitable for keyboard shortcuts. For keyboard shortcuts, choose commands that perform an immediate function, that is, those without a mark at the right end of the list.

If you select a table in the sheet and press [Alt] + [1], the command registered at the top (leftmost) of the Quick Access Toolbar will be executed. As a result, lines can be drawn in a grid pattern in the selected range.

Copyright© Digital Advantage Corp. All Rights Reserved.