Excel 2007 vs. 2003: Customize Your Environment

By David H. Ringstrom, CPA

Repeat after me: Either you work Excel, or it works you. Many users complete tasks the hard way because they don’t realize Excel’s potential. Further, many folks don’t realize that a few simple environmental changes in Excel can yield major benefits. In this article I’ll discuss the changes I make on every new computer. I’ll focus first on Excel 2007, but I’ll also discuss Excel 2003 for those who haven’t upgraded yet.

Try it now:  A free 60-day trial of Office 2007 is available for immediate download. You can instruct the installation program to leave your present version of Office intact so that you’re free to work in either version.

Excel 2007 Options

Although you can tailor earlier versions of Excel to meet your needs, Excel 2007 takes customization to a new level. To begin, follow these steps:

1.      Click the Office button in the top left corner of the screen.

2.      Choose Excel options.

3.      In the Popular section, change Include This Many Sheets to 1, instead of the default of 3, as shown in Figure 1. Extraneous blank worksheets require other users to click through only to find that they’re blank.

Figure 1: Keep spreadsheets lean by setting the default number of sheets to 1.

Quick Add: Excel 2007 makes it easy to add new sheets on the fly — simply click the Insert Worksheet tab to the right of your last worksheet. Alternatively, you can press Shift-F11.

4.      In the Save section, change Save Files in This Format to Excel 97-2003 Workbook (*.xls) if you frequently share documents with Excel users who haven’t migrated to Excel 2007.

Compatibility Pack: Microsoft Knowledge Base article 924074 discusses the free Compatibility Pack that Office 2000, XP and 2003 users can install to access files saved in the new Office Open XML format.

5.      In the Save Section, change Default File Location to the location where most of your spreadsheets reside. Many users save files on a network drive, such as H:\ or J:\. You can save several clicks or keystrokes each time you save a file by modifying this setting — otherwise you must repeatedly browse to the network drive to save your file.

6.      In the Advanced section, scroll down and change Show This Number of Recent Documents to any number as high as 50. Doing so provides quick access to dozens of recently used spreadsheets by simply clicking the Office button. Previous versions of Excel limited this setting to 9.

7.      In the Customize Section, browse through the Choose Commands From list and add frequently used shortcuts to the Quick Access Toolbar that typically appears at the very top of your Excel screen. There is no limit to the number of commands that you can add to the Quick Access bar, other than practicality. When you add more icons than will appear on your screen, you have to click a button to expand the Quick Access bar, which thereby somewhat obviates quick access.

8.      This next tip applies only if you regularly use spreadsheets that contain macros: In the Trust Center section, click the Trust Center Settings button. In the Macro Settings, choose one of these options:

o   Disable All Macros With Notification — this option allows you to choose whether to enable macros when you open a spreadsheet that contains macros;

o   Disable All Macros Except Digitally Signed Macros — this option allows you to automatically enable macros in spreadsheets that are digitally signed; or

o   Enable All Macros (Not Recommended; Potentially Dangerous Code Can Run) — this option automatically enables macros in any workbook without notification.

9.      This next tip applies if you link to databases or other spreadsheets: Manage links in the External Content Section of the Trust Center by choosing whether to enable, prompt or disable such connections.

Manage the Excel 2007 Ribbon

Many users complain that the Excel 2007 Ribbon consumes about the same amount of screen space as three rows of traditional toolbars. Fortunately, there’s an easy way to use the Ribbon and see your spreadsheet, too: Right-click anywhere on the Ribbon and choose Minimize the Ribbon, as shown in Figure 2. Once you do so, the ribbon vanishes but appears instantly when you click on any of the ribbon sections, such as Home, Insert, Page Layout, etc. When you click back on your spreadsheet, the Ribbon vanishes again.

Figure 2: The Minimize Ribbon command can maximize the number of viewable rows.

Caveat: In many cases, this method will temporarily obscure the first two and one-half rows of your spreadsheet. You can display the Ribbon again permanently: Right-click on one of the Ribbon section names, and then click Minimize the Ribbon.

Manage the Excel 2007 Status Bar

As shown in Figure 3, the Excel 2007 status bar is much more advanced than in previous versions of Excel — you can choose which shortcuts and indicators to display. To do so, right-click on the status bar and then choose which features you wish to display or hide.

Figure 3: The Excel 2007 status bar can provide numerous indicators or none at all.

Excel 2003 Options

I always make these changes in Excel 2003 and earlier versions:

1.      Choose Tools and then Options.

2.      Click on the General tab, and then make these changes:

·         Change the Recently Used File List to 9 (up from the default of 4).

·         Change the Sheets in New Workbook to 1 (down from default 3).

Insert Worksheets: You can press Shift-F11 to insert a blank worksheet in your workbook, or choose Insert and then Worksheet.

·         Change the Default File Location if needed.

3.      Click OK to close the Options dialog box.

4.      Choose Tools, Customize, and then click the Options tab.

5.      Click Always Show Full Menus, and then click Close.

6.      This next tip applies if you want to eliminate the Office Assistant. To do so:

a.       Choose Help and then Microsoft Excel Help (or press F1).

b.      Right-click on ClippIt (yes, the paper-clip has a name!), and choose Options.

c.       Unclick Use the Office Assistant.

d.      You can always restore ClippIt by choosing Help and then Show the Office Assistance.

7.      This next tip applies if the Clipboard task pane, shown in Figure 4, annoys you. The purpose of this task pane is to enable you to copy multiple items to the clipboard for pasting later. I don’t like losing screen space to the task pane, so I always turn it off; to do so:

Figure 4: The Clipboard task pane is easy to eliminate.

a.       Click Edit and then Office Clipboard.

b.      Click the Options button at the bottom of the task pane, and then clear all of the options.

c.       Close the Clipboard task pane by clicking the x in its upper right-hand corner.

d.      The Clipboard task pane is now permanently hidden, but you can always reverse the process at any time. Choose Edit and then Office Clipboard.

Excel 2003 Reviewing Toolbar

I always want to see as many rows of my spreadsheet as possible, so I minimize the space that toolbars consume. You can reposition any toolbar easily by grabbing the left edge with your left mouse button and moving the toolbar.

However, the Reviewing toolbar presents a challenge — it appears automatically every time you open an e-mail attachment. Normally it’s easy to hide a toolbar: right-click on the toolbar and unclick its name on the list. However, the Reviewing toolbar is different because it automatically reappears every time you open an e-mail attachment, so simply hiding it can be futile. 

Instead, drag the reviewing toolbar to the far-right hand side of the Excel screen, adjacent to the first row of toolbars. Leave only the first icon on the reviewing toolbar visible. This eliminates the need to continually hide the Reviewing toolbar and also maximizes your screen space.

Excel 2003 Additional Icons

The other change that I make is to create my own version of Excel 2007’s Quick Access toolbar. I do this by adding additional icons to the menu — to the right of the Help command. Typically this area is wasted screen space, but you can make the most of this area:

1.      Click Tools and then Customize.

2.      Click the Commands tab.

3.      Drag any icons of your choice up to the toolbar and to the right of Help, as shown in Figure 5. Here are a few that I recommend:

Figure 5: You can add additional icons to the right of the Help command.

a.       Data category: AutoFilter and Show All

b.      Edit category: Paste Values and Paste Formatting

c.       Format category: Merge Across

d.      AutoShapes category: Text Box

e.       Tools category: Protect Sheet and Protect Workbook

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.