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.
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 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.
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.
Use Special Templates to Switch Fonts
In Excel, as in other Office apps, you can accomplish many tasks in more than one way. The next tweaks we'll consider involve a feature in Excel that you can use to make a wide range of custom adjustments.
Two important characteristics of any new Excel workbook are its typeface and its font size. Excel 2007 defaults to the Calibri font set in 11-point size. If you prefer a different typeface--or if your company has a standard font that you're expected to use--you could change the font every time you open a new workbook file, or you could save a workbook template containing the settings you want and then make a copy of that file whenever you want to create a new workbook.
But there's another way to handle templating. Most Office users know that Word 2007 uses templates where they can store formatting and other settings until the next time they want to create a new document. But not many people realize that Excel has a similar template feature; and even fewer realize that Excel 2007 permits users to create two special "global" templates that incorporate customized special properties.
The two templates are Book.xltx and Sheet.xltx. Excel uses the first of these whenever you create a new workbook, and it uses the second whenever you add a new worksheet to an existing workbook. For the tweaks discussed here, we'll stick to the Book.xltx template, but you should be aware that Sheet.xltx works in much the same way.
These template files work automatically only if you included them in a special folder, named XLSTART. Unfortunately you're likely to have more than one XLSTART folder on your hard drive. To locate the right one, click the Excel Options button to open the Excel Options window, and choose Trust Center from the left-hand column. Then click the Trust Center Options button to open the Trust Center window. Choose Trusted Locations from the left-hand column.
In the Description column, look for an entry labeled 'Excel 2007 default location: User StartUp'. This identifies the folder where you want to place the Book.xltx and Sheet.xltx templates so that they'll load automatically when you start Excel. Click Cancel twice to return to Excel.
Now press Ctrl-A to select the entire worksheet, and change the typeface and font size to the settings you want. For example, you might change the typeface to Times Roman New, and the point size to 12. Repeat these steps for any additional worksheets in the workbook. Then click File, Save As to save the workbook with the file name "Book" in the Excel template format (*.xltx); use the Excel macro-enabled template format (*.xltm) if the workbook includes macros. Make sure that you change the file location to the XLSTART folder that you identified in the previous step.
Next, close Excel and then restart it. The app will use the Book.xltx template as the model for your new workbook, and the default typeface and font size that you specified in Book.xltx will appear in the new file.
Get More Formatting Options
Control over typeface settings is just the tip of the iceberg. You can customize all sorts of settings in Book.xltx that Excel will subsequently incorporate into new workbooks, saving you lots of time.
For example, suppose that most of the worksheets you create work with currency figures. Since the normal Excel default formatting for numeric values in cells is the General setting, you must reformat the cells to use the currency formatting that you prefer, such as inclusion of a $ symbol, commas between thousands, red type and parentheses for negative values, and two decimal places. To adjust the workbook template accordingly, open the Book.xltx template file, select all of its cells by pressing Ctrl-A, and then format the cells as you like. (Remember to repeat the formatting for any additional worksheets in the workbook.) Then save the template.
The next time you open Excel, it will create a new, empty workbook whose cells have the same numeric formatting that you specified in the Book.xltx template file.
You can also use the template to create models of the individual worksheets you want. For example, suppose that the workbooks you most often create have four worksheets--one each for the east, south, west, and north sales regions. You can create a template for each of those worksheets and label their tabs in the Book.xltx workbook template file; and subsequently they will appear whenever you instruct Excel to create a new workbook. You can fill in cells with header information or other content, set up the page for landscape printing, define headers and footers, and establish a wide range of other time-saving settings.
These tweaks can automate many of the specifications that you may currently be entering by hand each time you create a new workbook. Try a few of them, and watch your time savings grow.
Clear ClearType to Soothe Sore Eyes
By default, Excel uses on fonts in Office applications. ClearType is an antialiasing technology that smooths out text on the screen by filling the curves and angled lines with lighter shades. Some people like this feature, but others find that not having a crisp image to focus their eyes on is actually more tiring to read. If you turn off ClearType, you'll get much crisper text on screen--especially at smaller font sizes.
Start by clicking the Excel Options button to open the Excel Options window. When the window opens, stay in the Popular tab (which should already be selected from the list on the left side of the window). In the third checkbox from the top in the right-hand section of the window, uncheck the box marked Always use ClearType. A message box will tell you that the change won't take effect until you close and restart all Office applications. Click OK in the message box, and then OK again for the Excel Options window to save your change. Close Excel and restart it, and you'll see how text looks without ClearType.
If you find that you prefer the ClearType to be enabled after all, repeat the preceding steps but recheck the box next to Always use ClearType in the Excel Options window.
Bonus Tip: To protect your business data from prying eyes, from PC World blogger Rick Broida.