Excel 2016 All-in-One For Dummies. Harvey Greg

settings (also referred to as options) that control any number of program assumptions and basic behaviors.

      ✔ The third place where you can customize Excel is in the world of add-ins, those small, specialized utilities (sometimes called applets) that extend the built-in Excel features by attaching themselves to the main Excel program. Excel add-ins provide a wide variety of functions and are available from a wide variety of sources, including the original Excel 2016 program, the Microsoft Office website, and various and sundry third-party vendors.

      Tailoring the Quick Access Toolbar to Your Tastes

      Excel 2016 enables you to easily make modifications to the Quick Access toolbar, the sole toolbar of the program. When you first launch Excel, this toolbar appears above the Ribbon with the three most commonly used command buttons: Save, Undo, and Redo.

      To add other commonly used commands to the Quick Access toolbar, such as New, Open, Email, Quick Print, and the like, simply click the Customize Quick Access toolbar button and choose this command from the drop-down menu.

       If you use Excel 2016 on a touchscreen device (such as the Microsoft Surface Tablet) the Touch/Mouse Mode button, which enables you to switch in and out of touch mode, is automatically added to the Quick Access toolbar. Touch mode puts more space between the command buttons on each tab of the Excel 2016 Ribbon, thus making it a whole lot easier to select the correct command with either your finger or stylus. Even when running Excel on a computer without any touch capabilities, you can still add the Touch/Mouse Mode button to the Quick Access toolbar and use touch mode to make it easier to select Tab commands with your mouse.

Adding Ribbon commands to the Quick Access toolbar

      Excel 2016 makes it super-easy to add a command from any tab on the Ribbon to the Quick Access toolbar. To add a Ribbon command, simply right-click its command button on the Ribbon and then choose Add to Quick Access Toolbar from its shortcut menu. Excel immediately adds the command button to the very end of the Quick Access toolbar, immediately in front of the Customize Quick Access Toolbar button.

      If you want to move the command button to a new location on the Quick Access toolbar or group it with other buttons on the toolbar, you need to click the Customize Quick Access Toolbar button and then choose More Commands from its drop-down menu.

Excel then opens the Excel Options dialog box with the Quick Access Toolbar tab selected (similar to the one shown in Figure 2-1). Here, Excel shows all the buttons currently added to the Quick Access toolbar in the order in which they appear from left to right on the toolbar corresponding to their top-down order in the list box on the right side of the dialog box.

       Figure 2-1: Use the buttons on the Quick Access Toolbar tab of the Excel Options dialog box to customize the appearance of the Quick Access toolbar.

      To reposition a particular button on the bar, click it in the list box on the right and then click either the Move Up button (the one with the black triangle pointing upward) or the Move Down button (the one with the black triangle pointing downward) until the button is promoted or demoted to the desired position on the toolbar.

       You can add separators to the toolbar to group related buttons. To do this, click the <Separator> selection in the list box on the left and then click the Add button twice to add two. Then, click the Move Up or Move Down button to position one of the two separators at the beginning of the group and the other at the end. Also keep in mind that you can always return the Quick Access toolbar to its default state with its three buttons (Save, Undo, and Redo) by selecting the Reset Only Quick Access Toolbar option from the Reset drop-down list.

       If you’ve added too many buttons to the Quick Access toolbar and can no longer read the workbook name, you can reposition it so that it appears beneath the Ribbon immediately on top of the Formula bar. To do this, click the Customize Quick Access Toolbar button at the end of the toolbar and then choose Show Below the Ribbon from the drop-down menu.

Adding non-Ribbon commands to the Quick Access toolbar

      You can also use the options on the Quick Access Toolbar tab of the Excel Options dialog box (refer to Figure 2-1) to add a button for any Excel command even if it’s not one of those displayed on the tabs of the Ribbon:

      1. Select the type of command you want to add to the Quick Access toolbar from the Choose Commands From drop-down list box.

      The types of commands include the default Popular Commands, Commands Not in the Ribbon, All Commands, and Macros, as well as each of the standard and contextual tabs that can appear on the Ribbon. To display only the commands not displayed on the Ribbon, select Commands Not in the Ribbon near the top of the drop-down list. To display a complete list of all the Excel commands, select All Commands near the bottom of the drop-down list.

      2. Click the command option whose button you want to add to the Quick Access toolbar in the Choose Commands From list box on the left.

      3. Click the Add button to add the command button to the bottom of the list box on the right.

      4. (Optional) To reposition the newly added command button so that it’s not the last one on the toolbar, click the Move Up button until it’s in the desired position.

      5. Click the OK button to close the Excel Options dialog box.

       Adding commands lost from earlier Excel versions to the Quick Access toolbar

      Although certain commands from earlier versions of Excel, such as Data ⇒ Form and Format ⇒ AutoFormat, did not make it to the Ribbon in Excel 2016, this does not mean that they were entirely eliminated from the program. The only way, however, to revive these commands is to add their command buttons to the Quick Access toolbar after selecting the Commands Not in the Ribbon category from the Choose Commands From drop-down list on the Customization tab of the Excel Options dialog box.

Adding macros to the Quick Access toolbar

      If you’ve created favorite macros (see Book VIII, Chapter 1) that you routinely use and want to be able to run directly from the Quick Access toolbar, select Macros from the Choose Commands From drop-down list box and then click the name of the macro to add in the Choose Commands From list box followed by the Add button.

       Excel 2016 then adds a custom macro command button to the end of the Quick Access toolbar whose generic icon displays the branching of a programming diagram. This means that if you add several favorite macros to the Quick Access toolbar, the only way to tell them apart is by their ScreenTips, each of which displays the location and name of the macro attached to the particular custom button when you highlight the button by passing the mouse pointer over it.

      Exercising Your Options

      Each time you open a new workbook, Excel makes a whole bunch of assumptions about how you want the spreadsheet and chart information that you enter into it to appear onscreen and in print. These assumptions may or may not fit the way you work and the kinds of spreadsheets and charts you need to create.

      In the following five sections, you get a quick rundown on how to change the most important default or preference settings in the Excel Options dialog box. This is the biggest dialog box in Excel, with a billion tabs (ten actually). From the Excel Options dialog box, you can see what things appear onscreen and how they appear, as well as when and how Excel 2016 calculates worksheets.

       Nothing discussed in the following five sections is critical to your being able to operate Excel. Just remember