Friday, August 24, 2007

Customizing Excel with Book.XLT and PERSONAL.xls

It is actually quite straightforward to customize Excel, but Excel attempts to confuse users for reasons unknown. I'll attempt to clear it up.

There are two files that are important. Book.xlt, and Personal.xls.

Book.xlt is the template for the default excel file that opens every time you start excel. If you want a title on the upper left, or the zoomed changed by default, you can put these into book.xlt, save it, and then excel will start up with exactly that look every time.

Personal.xls is the file that contains all macros that you want to use across multiple excel files. It's opened every time you run excel, but it is hidden by default. You can unhide it by clicking on Window -> Unhide (Alt+W, U). When you add/modify macros, you want to do it on the Personal.xls file, so that they are available whenever you are using excel.

Both these files for me (XP professional) are located at
c:\Documents and Settings\USERNAME\Application Data\Microsoft\Excel\XLSTART

If you want to replace your personal.xls, it's probably better to copy over it in the directory itself. If you want to modify personal.xls, it's better to Window->Unhide it, modify it, then save it and hide it again. Excel seems to like that better.

No comments:

Friday, August 24, 2007