Software

A few ways to customize Excel to increase your productivity

Working with Excel's out-of-the-box defaults can waste your time. Change default settings to work more productively and efficiently.

Customizing a new install of Excel can help you work more efficiently from the get-go. How you customize Excel depends on you, but here are a few tweaks that most users will benefit from.

Number of sheets

By default, Excel launches a new workbook with three sheets. There's nothing wrong with that number, but if you routinely work with more or less, change this default, as follows:

  • 2010: Click the File tab and choose Options. Select General on the left. In the When Creating New Workbooks section, choose the appropriate number from the Include This Many Sheets control. Click OK.
  • 2007: Click the Office button and then choose Excel Options. Select Popular in the left pane. In the When Creating New Workbooks section, choose the appropriate number from the Include This Many Sheets control. Click OK.
  • 2003: Choose Options from the Tools menu. Click the General tab and set the number of sheets in the Sheets In New Workbook control. Click OK.

Default file location

Many users don't use the default file folder, My Documents, and they spend a lot of time browsing folders looking for files. Using the instructions above, you can control where Excel saves your workbooks. You can still change that location on the fly, but having Excel default to your most commonly used folder will save a lot of time.

User name

Many IS departments install Excel without customizing it at all. One of the first things you'll want to check is the user name and reset it if necessary (use instructions above to access this property). If everyone's using the same generic user name there's no way to claim ownership. Changing this property in Excel will change it in all of your Office apps.

Display the Developer tab

If you're using Excel 2007 or 2010, you'll probably want to display the Developer tab, which Excel hides by default. You don't have to be a developer to want quick access to recording macros or inserting form controls. To enable this tab, do the following:

  • 2010: From the Quick Access Toolbar dropdown, choose More Commands. In the left pane, choose Customize Ribbon. In the Customize The Ribbon list (to the right), check Developer. Click OK.
  • 2007: Click the Office button and then click Excel Options. Choose Popular in the left pane. In the Top Options For Working With Excel section, check the Show Developer Tab In The Ribbon option.

File format

If you're sharing 2007 or 2010 files with pre-ribbon versions, you might want to save your workbooks in the pre-ribbon format. There are other options, but if you're not making use of features specific to the new ribbon features, doing so might save your co-workers a bit of aggravation. To save files in another format, do the following:

  • 2010: Click the File tab and choose Options. Select Save in the left pane. In the Save Workbooks section, choose Excel 97 - 2003 Workbook (.xls). Click OK.
  • 2007: Click the Office button and then click Excel Options. Select Save in the left pane. In the Save Workbooks section, choose Excel 97 - 2003 Workbook (.xls). Click OK.

AutoCorrect options

These default settings try to help users by correcting entries that Excel finds inappropriate. Unfortunately, these settings probably annoy more users than any others. There are too many to review individually, but knowing you can disable them is a start. To change an AutoCorrect setting, do the following:

  • 2010 and 2007: Click the File tab and choose Options. Then, select Proofing in the left pane. Click the AutoCorrect Options button in the AutoCorrect Options section. Click tabs to review the different options.
  • 2003: Choose AutoCorrect Options from the Tools menu.

What Excel settings have you customized and why?

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks