Customizing Excel

Options to consider

Although hundreds of Excel options exist, this section provides a quick review of options that might be helpful to you:

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

2. Update your Default Local File Location on 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.

3. 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 allows you to see up to 50 recent workbooks in the Open category of the File menu. You can change this setting by visiting the Display section of the Advanced category.

4. Edit Custom Lists has been moved to the General 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.

5. 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.

6. 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.

7. 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 menu. If you like the old behavior of showing each date, turn off this setting.

8. 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.