Home	Excel	Access
| Feedback | Personal.xls |

Configuring Excel

This page describes aspects of changing the program options, setting defaults using Book.xlt and Sheet.xlt, using the Personal.xls workbook and saving toolbars.

Would you like to learn more?

Step by Step Excel 2003 (with CD)

Where to start?

If the date characters are in the wrong order for your country e.g. MM/DD/YY (for the UK) and measurements are in inches rather than centimetres the problem lies in your PC settings rather than with Microsoft Office. In Windows select Start | Settings | Control Panel | Regional Options. Check that the language is set to English UK (or whatever is appropriate) and that the number, time, date and currency settings are as you wish them to be.

Some of the default setup options within Excel can be modified within Tools | Options | General:

• the default file location. The location of the folder which will contain all of your data files (i.e. spreadsheets). Don't ever save your files in any of the Excel program folders. This will make backing up or copying files extremely difficult. Normally you would create a new folder and name it something like 'C:\My Documents\ExcelData'.
• the number of sheets in a new workbook. Remember you can always Insert more if you need them.
• the default Font and Size e.g. Arial 11

How Excel saves settings

When a new spreadsheet is started you may wish to use a common group of preferred settings such as the font name and size of a title in cell A1, headers and footers and number formats. These items can be stored in a template called Book.xlt which is automatically opened when Excel starts or when you request a new workbook.

• Open an empty workbook and in all of the worksheets, select all cells and change the font and font size to whatever you require (e.g. Arial 11)
• Add headers and / or footers using either File | Page Setup or View | Headers and Footers. You can add fields or text to a footer containing your name or company / department, the date, the filename ... anything. Remember to do this on all of the worksheets.

• Excel places buttons on your default toolbar to apply Styles such as Comma, Currency and Percentage. Some of the default styles may not be configured as you wish and display your numbers in unhelpful patterns and formats. Select a cell, click on one of the formatting style buttons (e.g. Comma) and then select Format | Style | Modify. Change it to something useful (possibly creating a Custom format) and then the new format settings will be retained in Book.xlt. Click here for information about number formatting.
• Each style can have 4 formats separated by a semi-colon. These determine how particular types of values or contents are displayed: Positive; Negative; Zero; Text.
• The underscore _ means that an empty space equivalent to the width of the following character is to be left blank. This allows you to force numbers to be right aligned, taking into account the width of the closing bracket used for negative numbers. The asterisk * means any characters after it will be justified to the right and the padding will be made up by the following character (normally a space). This lets you place characters (such as a currency symbol) to the left of the cell and the remainder aligned to the right. Experiment with these codes (possibly using the Text function) before you start defining them in your Book.xlt.

Once you are happy with your creation select File | Save As and change the settings of File Type to 'Template (*.xlt)' and name it Book.xlt. The folder in which to locate this file may need some exploration. Search through your Microsoft Office or Documents and Settings folders for the location of Book.xlt or the XLStart folder. If it doesn't already exist save it in the /XLstart folder. When Excel is restarted it should find this file and it's settings - if not try specifying the location using Tools | Options | General.

Book.xlt may contain several worksheets. If you save a single worksheet with your new settings as a template with the name Sheet.xlt it will be used whenever you tell Excel to Insert | Worksheet.

Customising toolbars

There are many additional buttons which are not displayed on the 2 default toolbars (standard and formatting). For example a toolbar button to set the print area will mean you don't need to visit File | Print Area | Set Print Area but can simply highlight the required range and click a toolbar button. Buttons to insert a row or column may also be useful. To change a visible toolbar right click on it and choose Customise | Commands. You can then drag additional buttons from the dialog box onto either toolbar or drag current unwanted buttons off the toolbar.

If you should need to reinstall Excel and you want to retain your toolbars, the settings are saved in a workbook called Excel.xlb. If the file cannot be not found, try making a simple alteration to a toolbar, then use the Windows Start menu option to Find Files with the wildcard '*.xlb'.

Personal.xls

If this file already exists, Excel will automatically open it upon startup but will hide it from normal view. The file on your hard disk will probably be located in the same folder as Book.xlt. The Excel menu option Window | Unhide will reveal this file if indeed it has been opened.

Personal.xls can be used to save your own macros or user defined functions which you may want available for general use and which are not specific to (and saved within) a particular spreadsheet. When saving a macro you are given the option of whether to save it in the current workbook or in your Personal.xls file. Remember that if you give someone else a workbook which makes use of a function stored in your Personal.xls, the function won't be available to them and an error will occur in any cells where it has been used.

Even if you can not program in visual basic, Excel allows you to record a macro. You may choose to record the various keystrokes necessary to set your page margins and formatting and to add a header and footer. This print setup macro may be useful when modifying other peoples spreadsheets or imported files.

If you can manage a modest amount of visual basic you may choose to include your own user defined functions. These new keywords will supplement the range of built in functions (such as SUM and OFFSET) and can be selected from the Paste Function button within the User Defined category. You could create a formula to calculate national insurance payments or a function to add / remove the appropriate punctuation within a formatted code.

	A	B		Get this information as a document accompanied by Excel worksheets Click here for details about obtaining this file
1	ABCDE123456	Text in cell A1
2	= AddPunctuation(A1)	The formula
3	ABCD.E-123456	The result