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

box in the Data section on the Advanced tab of the Excel Options dialog box.

       Laying on the Lotus 1-2-3 compatibility

      The last two sections on the Advanced tab, Lotus Compatibility and Lotus Compatibility Settings For, are only of interest to Lotus 1-2-3 users who are just now coming to use Microsoft Excel as their spreadsheet program.

       If you’re a dyed-in-the-wool 1-2-3 user, you’ll definitely want to put a check mark in all three check boxes, Transition Navigation Keys, Transition Formula Evaluation, and Transition Formula Entry, in both the Lotus Compatibility and Lotus Compatibility Settings For sections. That way, you’ll be able to start formulas with built-in functions with the @ symbol – which Excel dutifully converts to an equal sign (=) – as well as use all the keys for navigating the worksheet to which you’ve become so accustomed.

       Keep in mind that you can activate the hot keys on the Excel Ribbon by pressing the forward slash (/) key even when none of the Lotus compatibility options are selected. When I want to use the program’s hot keys to select an Excel command from the Ribbon, I find pressing the forward slash, which activated the pull-down menus in Lotus 1-2-3, to be much easier than pressing the Alt key – this is because / is part of the QWERTY keyboard. This means that whenever you see a keyboard shortcut such as Alt+WP in the book, you can just press /WP (which in this particular case puts the Worksheet display area into Page Layout view).

Customizing the Excel 2016 Ribbon

The options on the Customize Ribbon tab (see Figure 2-9) of the Excel Options dialog box (File ⇒ Options ⇒ Customize Ribbon or Alt+FTC) enable you to modify which tabs appear on the Excel Ribbon and the order in which they appear, as well as to change which groups of command buttons appear on each of these displayed tabs. You can even use its options to create brand-new tabs for the Ribbon as well as create custom groups of command buttons within any of the displayed tabs.

       Figure 2-9: The Customize Ribbon tab options enable you to control which tabs are displayed on the Ribbon and which groups of command buttons they contain.

       Customizing the Ribbon’s tabs

      If you find that the default arrangement of main tabs and groups on the Excel Ribbon is not entirely to your liking, you can simplify or rearrange them to suit the way you routinely work:

      ✔ Hide tabs on the Ribbon by deselecting their check boxes in the Main Tabs list box on the right side of the Excel Options dialog box. (To later redisplay a hidden tab, you simply select its check box.)

      ✔ Modify tab order on the Ribbon by selecting the tab to move and then click either the Move Up button (with the triangle pointing up) or Move Down button (the triangle pointing down) until the name of the tab appears in the desired position in the list shown in the Main Tabs list box.

      ✔ Modify group order on a tab by first expanding the tab to display the groups by clicking the Expand button (with the plus sign) in front of the tab name in the Main Tabs list box. Next click the name of the group you want to reposition and click either the Move Up or Move Down button until it appears in the desired position in the list.

      ✔ Remove a group from a tab by selecting its name in the expanded Main Tabs list and then clicking the Remove command button (under the Add button between the two list boxes that now appear in the main section of the Excel Options dialog box).

      In addition to the main tabs of the Ribbon, you can control which groups of command buttons appear on its various contextual tabs (such as the Drawing Tools or Chart Tools contextual tabs that automatically appear when you’re working on an Excel table of data or chart):

      ✔ Display the groups to be modified on a contextual tab by clicking the Tool Tabs option on the Customize the Ribbon drop-down list and then clicking the Expand button in front of the contextual tab whose groups you want to modify.

      ✔ Modify the group order on a contextual tab by clicking the group name and then clicking the Move Up or Move Down buttons to move it into its new position.

      ✔ Remove a group from a contextual tab by clicking its group name and then clicking the Remove command button.

       To restore the original groups to a particular tab you’ve modified, select the tab in the Customize the Ribbon list box on the right side of the Excel Options dialog box and then click the Reset drop-down button beneath this list box before you select the Reset Only Selected Ribbon Tab option.

       If you want to restore all the tabs and groups on the Ribbon to their original default arrangement, you can click the Reset drop-down button and then select the Reset All Customizations option from its drop-down list. Just be aware that selecting this option not only restores the Ribbon’s default settings but also negates all changes you’ve made to the Quick Access toolbar at the same time. If you don’t want this to happen, restore the tabs of the Ribbon individually by using the Reset Only Selected Ribbon tab option described in the preceding tip.

       Adding custom tabs to the Ribbon

      The Customize Ribbon tab of the Excel Options dialog box not only lets you customize the existing Ribbon tabs but also lets you add ones of your own. This is great news for you if you want Ribbon access to Excel commands you routinely rely on that didn’t make it to the default Ribbon.

      To add a brand-new tab to the Ribbon, follow these steps:

      1. Open the Customize Ribbon tab of the Excel Options dialog box (File ⇒ Options ⇒ Customize Ribbon or Alt+FTC).

      Excel opens the Customize Ribbon tab with the Main Tabs selected in the Customize the Ribbon list box on the right.

      2. Under Main Tabs in this list box, select the tab that you want to come before the new tab you’re about to insert.

      By default, Excel inserts the new tab after the one that’s currently selected in the Customize the Ribbon list box. This means that if you want your new custom tab to precede the Home tab, you must put it ahead of the Home tab with the Move Up button after first creating the new tab behind it.

      3. Click the New Tab command button below Main Tabs in the Customize the Ribbon list box.

      Excel inserts a tab called New Tab (custom) with the single group called New Group (Custom) displayed and selected. This New Tab (Custom) is placed immediately after the currently selected tab.

      4. Add all the commands you want in this group on the custom tab by selecting them in the Choose Commands From list box and then clicking the Add Command button.

      When adding commands, you can select them from any of the categories: Popular Commands, Commands Not in the Ribbon, All Commands, Macros, File Tab, All Tabs, Main Tabs, Tool Tabs, and Custom Tabs and Groups (which lists all custom tabs and groups you’ve previously created).

As you add each command from these categories, Excel displays the button’s icon and name in the list beneath New Group (Custom) in the left-to-right order in which they’ll appear. (See Figure 2-10.) To change the order of these command buttons in the new group on the custom tab, click the Move Up and/or Move Down buttons.

      5. Rename the new group by clicking the Rename button under the Customize the Ribbon list box and then typing the new name in the Display Name text box of the Rename dialog box before clicking OK.

      6. (Optional) To add other groups to the same custom tab, click the New Group button under the Customize the Ribbon list box and then add all its command buttons before renaming it. (Refer to Steps 4 and 5.)

      To