3. Customizing Excel

The Excel Options dialog offers dozens of changes you can make in Excel. This chapter walks you through examples of customizing the ribbon and discusses some of the important option settings available in Excel.

Figure 3.1 Decide where the new command should go on the ribbon.

To add the pivot table command to the Data tab, follow these steps:

1. Right-click the ribbon and select Customize the Ribbon.

2. In the right list box, expand the Data tab by clicking the + sign next to Data.

3. Click the Sort & Filter entry in the right list box. The new group will go after this entry.

4. Click the New Group button at the bottom of the right list box. A New Group (Custom) item appears after Sort & Filter, as shown in Figure 3.2.

Figure 3.2 Commands have to be added to a new group.

5. While the New Group is selected, click the Rename button at the bottom of the list box. The Rename dialog appears.

6. The Rename dialog offers to let you choose an icon and specify a name for the group. The icon is shown only when the Excel window is too small to display the whole group. Choose any icon and type a display name of Pivot. Click OK.

7. The left list box shows the popular commands. You could change Popular Commands to All Commands and scroll through 2,400 commands. However, in this case, the commands you want are on the Insert tab. Choose All Tabs from the top-left drop-down. Expand the Insert tab, and then expand Tables. Click PivotTable in the left list box. Click the Add button in the center of the dialog to add PivotTable to the new custom Pivot group on the ribbon. Excel automatically advances to the next icon of Recommended PivotTables. Click Add again.

8. In the drop-down above the left list box, select All Commands. The left list box changes to show an alphabetical list of all commands.

9. Scroll through the left list box until you find PivotTable and PivotChart Wizard. This is the obscure entry point to create Multiple Consolidation Range pivot tables. Select that item in the left list box. Click Add. At this point, the right side of the dialog should look like Figure 3.3.

Figure 3.3 Three new icons have been added to a new custom group on the Data tab.

10. Click OK.

Figure 3.4 shows the new group in the Data tab of the ribbon.

Figure 3.4 The results appear in the ribbon.

1. Right-click the ribbon and select Customize the Ribbon.

2. Click New Tab and rename the tab.

3. Add New Group(s) to the new tab.

4. Add commands to the new groups.

As you go through the steps to add a new ribbon tab, you will discover how absolutely limiting the ribbon customizations are. You have no control over which items appear with large icons and which appear with small icons. This applies even to galleries. If you add the Cell Styles gallery to a group on the ribbon, it always appears as an icon instead of a gallery, even if it is the only thing on the entire ribbon tab (see the left icon in Figure 3.5). The workaround is to add an entire built-in group to the tab. In the right of Figure 3.5, the entire Styles group was added. The Cell Styles gallery is now allowed to appear as a gallery.

Figure 3.5 When added to a custom group, a gallery is reduced to a single icon with a drop-down.

To export the changes, follow these steps:

1. Right-click the ribbon and select Customize the Ribbon.

2. Below the right list box, select Import/Export, Export All Customizations.

3. Browse to a folder and provide a name for the customization file. The file type will be .exportedUI. Click OK.

4. In Windows Explorer, find the .exportedUI file. Copy it to a co-worker’s computer.

5. On the co-worker’s computer, repeat step 1. In step 2, select Import Customization File. Find the file and click OK.

No. The Customize the Ribbon command in Excel 2016 applies to all workbooks.

Can I reset my customizations and go back to the original ribbon?

Right-click the ribbon and select Customize the Ribbon. Below the right list box, select Reset, Reset All Customizations.

How can I get complete control over the ribbon?

Learn RibbonX and write some VBA to build your own ribbon.

For more information on building your own ribbon, see RibbonX: Customizing the Office 2007 Ribbon, by Robert Martin, Ken Puls, and Teresa Hennig (Wiley, ISBN 0470191112).

These ribbon customizations are really lacking. Is there another option that doesn’t require me to write a program?

Yes, a number of third-party ribbon customization programs are available. For example, check out a free one from Excel MVP Andy Pope at www.andypope.info/vba/ribboneditor.htm.

To the Excel team’s credit, they tried to move the top options to the General category. Beyond those 15 settings, though, are hundreds of settings spread throughout 21 categories in the Excel Options and Trust Center. Table 3.1 gives you a top-level view of where to start looking for settings.

Table 3.1 Excel Options Dialog Settings

Figure 3.6 The i button offers an explanation of many settings.

Provide Feedback with Animation enables you to turn off the animations introduced in Excel 2013. It is found in the General category of the Advanced tab.

Enable Data Analysis Add-Ins: PowerPivot, Power View, and Power Map enables you to turn on these three add-ins in a single click instead of using the Add-Ins category. The setting is found in the Data category of the Advanced tab. This setting applies only if you own a version of Excel 2016 that includes the add-ins.

Another painful situation occurs when you do not save changes and then close Excel. Yes, Excel asks if you want to save changes for each open document, but this question usually pops up at 5:00 p.m. when you are in a hurry to get out of the office. If you are thinking about what you need to do after work and not paying attention to which files are still open, you might click No to the first document and then click No again and again without noticing that the fifth open document was one that should have been saved.

Another scenario involves leaving an Excel file open overnight only to discover that Windows Update decided to restart the computer at 3:00 a.m. After being burned a dozen times, you can change the behavior of Windows Update to stop doing this. However, if Windows Update closed Excel without saving your documents, you can lose those AutoRecovered documents.

A setting introduced in Excel 2010 has Excel save the last AutoRecovered version of each open file when you close without saving. This setting is on the Save category of Excel Options and is called Keep the Last AutoSaved Version If I Close Without Saving.

You should also understand the Discard Editing Data check box. Suppose that you insert an image in your workbook and then crop out part of the photograph. If you do not enable Discard Editing Data, someone else can come along and uncrop your photo. This can be an embarrassing situation—just ask the former TechTV co-host who discovered certain bits of photographs were still hanging around after she cropped them out.

If you only want to view or print the workbook, protected mode works great. One statistic says that 40% of the time, people simply open a document and never make changes to it.

After you click Enable Editing, Excel will skip protected mode the next time you open the file.

If you declare a folder on your hard drive to be a trusted folder, you can open those documents without Excel warning you about the items. Visit File, Options, Trust Center, Trust Center Settings, Trusted Locations to set up a trusted folder.

Starting in Excel 2010, if you open a file from your hard drive and enable the content, Excel automatically enables that content the next time. The inherent problem here is that if you open a file and discover the macros are bad, you will not want those macros to open automatically the next time. There is no way to untrust a single document other than deleting, renaming, or moving it. Instead, you have to go to the Trusted Documents category of the Trust Center where you can choose to clear the entire list of trusted documents.

Save File in This Format in the Save category. If you regularly create macros, choose the Excel Macro-Enabled Workbook as the default format type.

Update your Default File Location in the Save tab. Excel always wants to save new documents in your My Documents folder. However, if you always work in the C:AccountingFiles folder, update the default folder to match your preferred location.

Show This Number of Recent Workbooks has been enhanced dramatically since Excel 2003. Whereas legacy versions of Excel showed up to nine recent workbooks at the bottom of the File menu, Excel 2016 allows you to see up to 50 recent workbooks in the Recent category of the File menu. You can change this setting by visiting the Display section of the Advanced category.

Edit Custom Lists has been moved to the Display section of the Advanced category. Custom lists add functionality to the fill handle, allow custom sort orders, and control how fields are displayed in the label area of a pivot table. Type a list in the correct sequence in a worksheet. Edit Custom Lists and click Import. Excel can now automatically extend items from that list, the same as it can extend January into February, March, and so on.

Make Excel look less like Excel by hiding interface elements in the three Display sections of the Advanced category. You can turn off the formula bar, scrollbars, sheet tabs, row and column headers, and gridlines. You can customize the ribbon to remove all main tabs except the File menu. The point is that if you design a model to be used by someone who never uses Excel, the person can open the model, plug in a few numbers, and get the result without having to see the entire Excel interface.

Show a Zero in Cells That Have Zero Value is in the Display Options for This Worksheet section of the Advanced category. Occasionally people want zeros to be displayed as blanks. Although a custom number format of 0;-0;; will do this, you can change the setting globally by clearing this option.

Group Dates in the AutoFilter Menu is in the Display Options for This Workbook section of the Advanced category. Starting with Excel 2007, date columns show a hierarchical view of years, months, and days in the AutoFilter drop-down. If you like the old behavior of showing each individual date, turn off this setting.

Add a folder on your local hard drive as a trusted location. Files stored in a trusted location automatically have macros enabled and external links updated. If you can trust that you will not write malicious code, then define a folder on your hard drive as a trusted location. From Excel Options, select the Trust Center category and then Trust Center Settings. In the Trust Center, select Trusted Locations, Add New Location.

Adjust the gridline color in the Display section of the Advanced category. If you are tired of gray gridlines, you can get a new outlook with bright red gridlines. I’ve met people who have changed the gridline color and can attest that nothing annoys an old accountant more than seeing bright red gridlines.

Allow negative time by switching to the 1904 date system in the General section of the Advanced category. Excel never allows a time to return a negative time. However, if you are tracking comp time and you allow people to borrow against future comp time, it might be nice to allow negative time. In this case, switch to the 1904 date system to have up to 4 years of negative time. Use caution when changing this setting. All existing dates in the workbook will shift by approximately four years.

Put an end to the green triangles on your account numbers stored as text. Most of the green triangle indicators are useful. However, if you have a column of text account numbers in which most values are numbers, seeing thousands of green triangles can be annoying. In addition, the green triangles can hide other, more serious problems. Clear the Numbers Formatted as Text or Preceded by an Apostrophe in the Error Checking Rules check box in the Formulas category.

Automatically Insert a Decimal Point replicates the antique adding machines that were office fixtures in the 1970s. When working with a manual adding machine, it was frustrating to type decimal points. You could type 123456 and the adding machine would interpret the entry as 1,234.56. If you find that you are doing massive data entry of numbers in dollars and cents, you can have Excel replicate the old adding machine functionality. After enabling this setting, you can indicate how many digits of the number should be interpreted as being after the decimal point. The only hassle is that you need to enter $5 as 500. The old adding machines actually had a 00 key, but those are long since gone.

Change Dwight to Diapers using AutoCorrect Options. If you were a fan of the NBC sitcom The Office, you might remember the 2007 episode in which Jim allegedly put a macro on Dwight’s computer that automatically changed the typed word Dwight to Diapers. However, this doesn’t require a macro. From Excel Options, choose the Proofing Category and then click the AutoCorrect Options button. On the AutoCorrect tab, you can type new correction pairs. In this example, you would type Dwight into the Replace box and Diapers into the With box. The next time someone types Dwight and then a space, the word will automatically change to Diapers. You can also remove correction pairs by selecting the pairs and then pressing Delete. For example, if you hate that Microsoft converts (c) to ©, you can delete that entry from the list.