101 Excel 2013 Tips, Tricks and Timesavers (3 page)

In addition, the drop-down list contains an item for every other tab.

Sometimes, you need to do some guessing to find a particular command. For example, if you want to add the command that displays the Excel Options dialog box, you can find it listed as Options, not Excel Options.

Some commands simply aren’t available. For example, I’d like the Quick Access toolbar to display the command to toggle the “dashed line” page break display on a worksheet. The only way to issue that command is to display the Advanced tab of the Excel Options dialog box and then scroll down until you find the Show Page Breaks checkbox. No command for doing so can be added to the Quick Access toolbar.

To add an item to your Quick Access toolbar, select it from the list on the left and click Add. If you add a macro to your Quick Access toolbar, you can click the Modify button to change the text and choose a different icon for the macro.

Notice the drop-down control above the list on the right. This lets you create a Quick Access toolbar that’s specific to a particular workbook, which is most useful when you add workbook-specific macro commands to the Quick Access toolbar. Most of the time, you’ll use the setting labeled For All Documents (Default).

The only time you ever need to use the Quick Access Toolbar tab of the Excel Options dialog box is when you want to add a command that’s not on the Ribbon or add a command that executes a macro. In all other situations, it’s much easier to locate the command on the Ribbon, right-click the command, and choose Add to Quick Access Toolbar.

Only you can decide which commands to put on your Quick Access toolbar. In general, if you find that you use a particular command frequently, it should probably be on your Quick Access toolbar.

Performing other Quick Access toolbar actions

Here are some other things you can do with your Quick Access toolbar:

→
Rearrange the Quick Access toolbar icons.
If you want to change the order of your Quick Access toolbar icons, you can do so on the Customization tab of the Excel Options dialog box. Select the command and then use the up- and down-arrow buttons on the right to move the icon.

→
Display the Quick Access toolbar below the ribbon.
To change the position of the Quick Access toolbar, choose the down-pointing arrow control and select Show below the Ribbon.

→
Remove Quick Access toolbar icons.
The easiest way to remove an icon from your Quick Access toolbar is to right-click the icon and choose Remove from Quick Access Toolbar. You can also use the Quick Access Toolbar tab of the Excel Options dialog box. Just select the command in the list on the right and click the Remove button.

→
Share your Quick Access toolbar.
Use the Import/Export button to save a file that contains your Quick Access toolbar customization. You can then share this file with others. Unfortunately, this file also contains any Ribbon customizations that you might have made (see Tip 3). In other words, you can’t share your Quick Access toolbar without also sharing your Ribbon customizations.

→
Reset the Quick Access toolbar.
If you want to return the Quick Access toolbar to its default state, display the Quick Access Toolbar tab in the Excel Options dialog box and click the Reset button and choose Reset Only Quick Access Toolbar. All your customizations disappear, and the Quick Access toolbar then displays its three default commands.

Tip 3: Customizing the Ribbon

Tip 2 describes how to customize the Quick Access toolbar by adding Ribbon commands, but some users prefer to make some changes to the Ribbon itself.

You can customize the Ribbon in these ways:

→ Add a new tab.

→ Add a new group to tab.

→ Add commands to a group.

→ Remove groups from a tab.

→ Remove commands from custom groups.

→ Change the order of the tabs.

→ Change the order of the groups within a tab.

→ Change the name of a tab.

→ Change the name of a group.

→ Reset the Ribbon to remove all customizations.

That’s a fairly comprehensive list of customization options, but you
cannot
do some actions:

→ You cannot remove built-in tabs — but you
can
hide them.

→ You cannot add commands to built-in groups.

→ You cannot remove commands from built-in groups.

→ You cannot change the order of commands in a built-in group.

Unfortunately, you can’t customize the Ribbon (or Quick Access toolbar) by using VBA macros. However, developers can write RibbonX code and store it in workbook files. When the file is open, the Ribbon is modified to display new commands. Writing RibbonX is relatively complicated and is the subject of several complete books.

How to customize the Ribbon

You customize the Ribbon in the Customize Ribbon tab of the Excel Options dialog box (see Figure 3-1). The quickest way to display this dialog box is to right-click anywhere in the Ribbon and choose Customize the Ribbon.

Figure 3-1:
The Customize Ribbon tab of the Excel Options dialog box.

Customizing the Ribbon is very similar to customizing the Quick Access toolbar, which is described in Tip 2. The only difference is that you need to decide where to put the command within the Ribbon. Here’s the general procedure:

1.
Right-click any part of the Ribbon, and choose Customize the Ribbon. Excel displays the Customize Ribbon tab of the Excel Options dialog box.

2.
Use the drop-down list on the left (labeled Choose Command From) to display various groups of commands.

3.
Locate the command you want in the list box on the left and select it.

4.
Use the drop-down list on the right (labeled Customize the Ribbon) to choose a group of tabs.

Main Tabs refers to the tabs that are always visible; Tool Tabs refers to the context tabs that appear when a particular object is selected.

5.
In the list box on the right, select the tab and the group where you want to put the command.

You must click the “plus sign” controls to expand the hierarchical lists. Remember that you cannot add commands to built-in groups, so you may need to use the New Tab or New Group buttons to add a tab or group.

6.
Click the Add button to add the selected command from the left to the group on the right.

When you are finished making your Ribbon changes, click OK to close the Excel Options dialog box.

New tabs and groups are given generic names, so you’ll probably want to give them more meaningful names. Use the Rename button to rename the selected tab or group. You can also rename built-in tabs and groups.

Although you cannot remove a built-in tab, you can hide the tab by unchecking the check box next to its name.

Figure 3-2 shows a part of a customized Ribbon. In this case, I added a group to the View tab. The new Text To Speech group has five commands. I inserted this new group between the Zoom and the Window groups.

Figure 3-2:
The View tab, with a new group added.

Tip 4: Understanding Protected View

There’s an excellent chance that you’ve already encountered an Excel feature known as Protected View. Although it may seem like Excel is trying to keep you from opening your own files, Protected View is all about protecting you from malware.

Malware
refers to something that can harm your system. Hackers have figured out several ways to manipulate Excel files so that harmful code can execute. Protected View essentially prevents these types of attacks by opening a file in a protected environment (sometimes called a
sandbox
).

If you open an Excel workbook that you downloaded from the web, you’ll see a colorful message above the Formula bar (see Figure 4-1). In addition, Excel’s title bar displays the text
[Protected View]
.

Figure 4-1:
This message tells you the workbook was opened in Protected View.

If you’re certain that the file is safe, click Enable Editing. If you don’t enable editing, you’ll be able to view the contents of the workbook, but you won’t be able to make any changes to it.

If the workbook contains macros, you’ll see another message after you enable editing:
Security Warning. Macros Have Been Disabled
. If you’re sure that the macros are harmless, click Enable Content.

What causes Protected View?

Protected View kicks in for the following:

→ Files downloaded from the Internet

→ Attachments opened from Outlook

→ Files that open from potentially unsafe locations, such as your Temporary Internet Files folder

→ Files that are blocked by File Block Policy (a feature that allows administrators to define potentially dangerous files)

→ Files that were digitally signed, but the signature has expired

You have some control over how Protected View works. To change the settings, choose File⇒Options and click Trust Center. Then click the Trust Center Settings button and click the Protected View tab in the Trust Center dialog box. Figure 4-2 shows the options. By default, all three options are checked.