Customize Microsoft Excel for Power Use

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.

To start up Excel with your own custom templates, put them in the XLSTART folder.
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 ClearType 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.

ClearType is intended to make text appear smoother on the screen, but can actually give it a slightly fuzzy appearance.
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, password-protect it with this easy tip from PC World blogger Rick Broida.

Subscribe to the Daily Downloads Newsletter

Comments