Excel 2007: Five ways to streamline Excel’s user interface

By David H. Ringstrom, CPA


It's easy to get caught up in the day-to-day use of Excel 2007 and overlook minor tweaks that can make a significant difference in your use of Excel. In this article I'll review five of my favorite Excel 2007 tricks. Most involve customizing the user interface so that I can speed through my work without hitting unnecessary bumps:

1. Extend your recently used file list
By default Excel shows the 17 most recent files that you've opened, but as shown in Figure 1, you can expand this to a maximum of 50:

 

  • a. Click the Office button, and then click Excel Options.
  • b. Choose Advanced, and then scroll down to the Display section.
  • c. Change the Show This Number of Recent Documents from 17 to a maximum of 50.

Figure 1: You can expand the number of recent documents that appear when click the Office button.

 

Expert tip: You can pin files permanently to the Recent Documents menu: Click the pushpin that appears to the right of the file name. Your document may shift further down the list over time, but will always remain on the Recent Documents menu.

2. Utilize the Places Bar

As shown in Figure 2, the Places Bar is the list of icons on the left-hand side of the Office dialog boxes. You can place shortcuts to frequently used folders here:

 

  • a. Click once on the desired folder, so that the folder name becomes highlighted. Be sure not to drill down inside the folder, but rather just click once on its name.
  • b. Right-click in the Places Bar and choose Add.

You can right-click on an item in the Places bar and move it up or down one position at a time. This menu also allows you to remove items you've added to the Places Bar, but you cannot remove the default locations like My Recent Documents, My Documents, and so on.

Figure 2: The Places Bar can give you once-click access to widely-scattered folders.

3. Disable the Paste/Insert Icons

You may have noticed the little clipboard icon, shown in Figure 3, that appears when you paste data into a worksheet. This little icon makes a Paste Options menu available, from which you can make changes to how your data pasted on the worksheet. Some folks may find this helpful, but many find it annoying. Fortunately it's easy to eliminate these prompts:

 

  • a. Click the Office button, and then choose Excel Options.
  • b. As shown in Figure 4, choose Advanced, and then clear the check boxes for Show Paste Options Buttons and Show Insert Options Buttons in the Cut, Copy, and Paste section.

Figure 3: The Paste Options button can be disabled.

Figure 4: Clear the checkboxes shown to disable the Paste and Insert Options buttons.

4. Eliminate the Zoom Slider

As shown in Figure 5, the Zoom Slider appears in the lower right-hand corner of your Excel screen. It's easy to accidentally hit this when you're scrolling up and down or right and left. If you click the Zoom Slider, then suddenly your Excel spreadsheet may zoom in or out dramatically, leaving you to reset it again. The Zoom section of the View tab makes it easy to change the size of your text, so you may wish to turn the zoom slider off:

 

  • a. Right-click anywhere on the Status bar at the bottom of your screen.
  • b. Clear the checkbox for Zoom Slider.

Figure 5: Convenience aside, the Zoom Slider makes it easy to inadvertently resize your screen view.

5. Eliminate extraneous worksheets

By default Excel 2007 workbook includes three worksheets, but you often only need one or two tabs. Further, Excel 2007 includes a new Insert Worksheet tab, shown in Figure 6, which allows you to add a new worksheet with just one click. Thus it's helpful to always start with a single worksheet and then add new sheets as you need them. Here's how to change the default from 3 to 1:

 

  • a. Click the Office button, and then choose Excel Options.
  • b. As shown in Figure 7, change the Include This Many Sheets setting to 1.

Figure 6: The Insert Worksheet button makes it easy to add new sheets on the fly.

Figure 7: You can change the default number of worksheets from 3 to 1.

 
A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link