| | Feedback | Personal.xls | Excel site Index | Excel 2010 screen | | ||
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.
If the date characters (e.g. MM/DD/YY) are in the wrong order for your country and page layout measurements are in the wrong units (i.e. inches or centimeters) 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.
A special file called Personal.xlsb can be used to save any macros or user defined functions which you want to be available for general use and which are not specific to (and saved within) a particular spreadsheet.
The personal workbook is not saved using the normal File | Save options. To create it [a] open Excel and select the ribbon option [Developer] Code | Record Macro. [b] In the Record Macro dialog box, ensure the macro is to be stored in the Personal Macro Workbook. Move the cursor to represent a simple macro command and then Stop Recording. This macro probably does not have any purpose other than to cause Excel to create the Personal.xlsb file. [c] Close all other workbooks and exit from Excel. The program will prompt you to save a new Personal macro workbook.
Thereafter, whenever you open Excel, the Personal macro workbook will open as a hidden file and any saved macros will be available. In order to edit or add a macro, the workbook must be unhidden via: [View] Window | Unhide and then using the [Developer] Code | Visual Basic option. After editing the code, hide the workbook again. When Excel is closed, you will be prompted to save the changes.
If Excel fails to recognise and automatically load the Personal workbook, try moving the file from a personal XLstart folder into the machine XLstart folder (see below).
Whenever you record and save a macro you are given the option of saving it in either the current workbook or in your Personal macro 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.
Even if you can not write programs in visual basic, Excel allows you to record useful macros. You may choose to record the various keystrokes necessary to set your page margins and formatting and to add a header and footer. This page setup macro may be useful when modifying other peoples spreadsheets or files imported from other programs.
When Excel starts it can automatically load various workbooks. It looks for the Personal.xlsb macro workbook and for a Book.xltx template (see below).
Excel will have created one or two special folders on your PC and it checks for files in both of these locations
It is possible to check where Excel expects to find the default XLSTART folder. 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
If you also want to load other specific workbooks whenever Excel starts up and do not want to use either of the XLstart directories you can specify another folder via: [File] Options | Advanced | General - 'At startup open files in'.
Excel displays ribbons containing what it believes 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.
The [Home] ribbon contains buttons linking to Accounting, Percent and Comma number styles. These are a convenient way to change the appearance of numbers but you may wish to amend the default settings. For example, the Comma style can be altered to remove the decimal places and to prevent the left alignment of the minus symbol i.e. from |- 1,234.56 | to | -1,234 | .
Select [Home] Styles | Cell Styles | and then right click on Comma and Modify. Click on the [Format] button and change the custom style to _-* #,##0_-;* -#,##0_-;_-* "-"??_-;_-@_-
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 remaining digits aligned to the right.
New spreadsheets can be based on a user defined template called called Book.xltx. It can contain various preferences such as the font name and size of a title to be placed in a particular cell (e.g. A1). Page headers and footers and number formats can also be defined.
Add headers and / or footers by switching from Normal View to Page Layout View. Select [Insert] Text | Header and Footer |. To edit the footer, scroll down to the bottom of a page (around row 50) and click in the left, centre or right Footer field. A new Header and Footer Design tab will appear and its ribbon contains various buttons for information fields such as filename or date. Remember to do this on all of the worksheets in your template.
Your template can also include any modifications made to the default number styles (e.g. Comma or Currency).
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).
Once the Book.xltx template has been saved, the new settings should appear each time you open up Excel. Strangely the template does not re-appear if you use [File] New | Blank Workbook | Create. Excel ignores Book.xltx and opens a blank book. In order to create a new workbook with your Book template there are two options:
Book.xltx may contain several worksheets. If you also 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.
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:
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.
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.
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'.
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.
Function AddPunctuation(MyCode as String) as String |
| A | B |
|
||||||
| 1 | ABCDE123456 | Text in cell A1 | ||||||
| 2 | = AddPunctuation(A1) | The formula | ||||||
| 3 | ABCD.E-123456 | The result |
| file: xlconfigure.htm | © meadinkent.co.uk 2016 | Last updated Apr15 | CMIDX S3 P8 Y |
