Home	Excel	Access
| Feedback | Personal.xls | Excel site Index | Excel 2010 screen |

Configuring Excel

This page describes aspects of changing the program options, setting defaults for worksheets, workbooks and toolbars. There are separate sections for Excel 2010 and Excel 2003.

Excel 2010 - current versions of Windows

Where to start?

If the date characters (e.g. MM/DD/YY) are in the wrong order for your country and measurements are in inches rather than centimetres the problem lies in your PC settings rather than with Microsoft Office. From the Windows Start menu, select | Control Panel | Clock, Language and Region |. Check that the language is set to English UK (or whatever is appropriate for your location) 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 by selecting [File] Options.

• 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:\Users\local_name\Documents\Excel'.
• the number of worksheets in a new workbook. By default this is three and you can Insert more at any time if you need them.
• the default Font and Size e.g. Arial 11

Personal.xlsb

Personal.xlsb 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. Microsoft Office is wary of the security risks associated with macros and therefore makes it difficult to create these workbooks. Note that this file must be saved with the 'Excel Binary Workbook (xlsb)' file type.

For detailed help on creating a new personal file, type 'personal.xlsb' in Excel help.

In order to add visual basic code to a spreadsheet the [Developer] tab must be activated.  Select [File] Options | Customize Ribbon |. In the Main Tabs box, make sure that the Developer check box is selected.

In the VBA window, Insert a new Module into Personal.xlsb (and not into any other open workbook such as Book1).

The Personal.xlsb should normally be hidden. Therefore despite always being open and having it's macros available for use, it does not clutter up the work area. Select [View] Window | Hide |. 

When recording and saving a macro you are given the option of whether to save it in the current workbook or in your Personal file. Remember that if you give someone else a workbook which makes use of a function stored in your Personal file, the function won't be available to them and an error will occur in any cells where it has been used. To determine whether or not Excel has opened your Personal file, select  [View] Window | Hide |. It will be listed in the dialog box if it has been loaded.

Even if you can not write programs 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 files imported from other programs.

If you can write some visual basic code you may 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.

A folder for startup files

Within the Excel visual basic editing window, ensure that you can View the Immediate Window.

Type the following code in the Immediate Window and then press <Enter>:  ? application.StartupPath

The result should be similar to: C:\Users\local_name\AppData\Roaming\Microsoft\Excel\XLSTART

This folder should be used to store your personal.xlsb file and any other templates.

Customising ribbons and buttons

Excel displays ribbons containing what it beleives to be an appropriate range of buttons for the task you are currently performing. If you find that frequently used buttons are not readily accessible you can modify the selection of buttons.

It may be best to avoid customising the main ribbons until you are familiar with the program and have determined which buttons are simply located in unfamiliar places. It is also possible to customise the Quick Access Toolbar which is located in the top left corner of the screen. Use the dropdown arrow at the righthand end of the toolbar to add a handful of your most commonly used options. In the example (left) buttons have been added for inserting rows and columns, setting the print area and clearing formats.

The same dropdown arrow on the Quick Toolbar will also allow you to edit and customise the main ribbons.

A default workbook template

New spreadsheets can be based on a user defined template called called Book.xltx. It can contain various preferred settings such as the font name and size or a title to be placed in a particular cell (e.g. A1). Page headers and footers and number formats can also be defined.

To change the default font and font size (e.g. Arial 11) select [File] Options | General |. This is a program setting and not part of the Book template.

• Excel places buttons on your Home ribbon 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 [Home] Style | Cell Styles |. Towards the bottom of the dialog are the Number Format style names. Right click on one to change it. The Styles dialog box contains a [Format] button which allows you to modify the number format (possibly creating a Custom format). The new format settings will be retained in Book.xltx. 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.xltx.

Once you are happy with your creation select [File] Save, change the  File Type to 'Template (*.xltx)' and name it Book.xltx. It must be saved in the XLStart folder (see above).

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

Would you like to learn more?

Step by Step Excel 2010

Excel 2003 - older versions of Windows

Where to start? (2003)

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 (2003)

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 (2003)

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 (2003)

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 (2003 only) 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