Productivity software

Customize Microsoft Excel for Power Use

When you get into a car to drive it for the first time, you adjust the seat, the mirrors, and maybe even the steering wheel's position. You want to make sure that it's a comfortable fit before you set off down the road. Yet many people fire up their computer without reviewing any of the default settings that the hardware manufacturer and software vendors may have chosen. When it comes to working with complex Excel spreadsheets, this is a mistake, because one size definitely doesn't fit all. You'll work more efficiently and with less frustration if you make a few adjustments.

Some programs are easier to customize than others, however. Microsoft's Excel 2007 has some obscure attributes that can be extremely helpful for tweaking the program's settings, but many people don't know about them. So let's take your copy of Excel out for a tune-up ride and get familiar with them.

Change the Default Location for Saved Files

The best opportunities for efficient interface tweaks involve tasks that you perform repeatedly. Let's start with what happens when you choose File, Save the first time to save a new file. Operating on its default settings, Excel will offer to save the file in your Documents folder. But it's hardly efficient to throw all of your Office files into a giant, disorganized shoebox. Instead, you should set up a separate folder for work projects, and save your spreadsheets there.

Customize Excel's default file location so you don't have to hunt for your most commonly used folder anymore.
Suppose that most of your spreadsheets belong in a folder named Forecasts. When you create a new worksheet, you want the Forecasts folder to be its default save location. To accomplish this, start by launching Excel 2007 and clicking the Office button in the upper left corner. Click the Excel Options button at the bottom of the window.

In the Excel Options window, choose the Save option from the left-hand column. The top section of the right-hand pane is marked 'Save workbooks', and the last line is labeled 'Default file location'. Delete the contents of that box and type in C:\Forecasts to make the Forecasts folder your default choice. Choose OK to save the setting and return to Excel.

While you're in the Excel Options window, you might want to alter another handy file setting. If you have to exchange Excel workbooks with other users, not all of whom are using Excel 2007, you should save the files in the .xls file format used in versions of the spreadsheet from Excel 97 through Excel 2003, rather than saving them in the .xlsx format introduced in Excel 2007. You could try to remember to save your files in the older format each time you create a new workbook, or you could use the 'Save files in this format:' box to change the default file format to .xls. Of course, you can always click File, Save As after the fact to specify a different format when you need it, but changing the default file format to the more universal .xls can save you from having to go through that step repeatedly.

Reduce the Ribbon

Unlike prior versions of the program, Excel 2007 from includes a Ribbon menu below the command menu across the top of the screen. It's an attractive way to display some of the options available for various menu items, but it takes up a lot of screen real estate, too. If you find that you're turning off the Ribbon menu whenever you start Excel--or if you wish that you could turn it off but don't know how--here's the way to get Excel to start without the Ribbon.

Excel's Quick Access Toolbar displays common features that you can easily customize, such as the Ribbon menu.
First, click the drop-down menu located in the Quick Access Menu next to the Office button at the top left of the window. Select Minimize the Ribbon, the last line of the drop-down list so that a checkmark appears next to it. At once the menu will disappear, as will the Ribbon--and it won't reappear the next time you start Excel.

Making this change will increase the available screen space on your monitor, so you can see more rows of your spreadsheet at one time. When you click a menu item at the top of the Excel screen, its Ribbon icons will appear until you select one of them (or press Escape or click the spreadsheet); then it will disappear once more.

Subscribe to the Daily Downloads Newsletter

Comments