In Mac parlance, Options are called Preferences, and like any other Mac application, you can find the Preferences menu item under the application menu (the one with the application name, just to the right of the Apple menu).
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.
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 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.
The default file location is important and specifies the name of the folder on your PC (or network) which will contain all of your data files (i.e. Don't ever save your files in any of the Excel program folders. This will make backing up or copying files extremely difficult. You should create a new folder and name it something like 'C: Users localname Documents Excel'.
The number of worksheets in each new workbook. By default this is three and you can Insert more at any time if you need them. To set the default Font and Size e.g. Arial 11, select File Options General. If you intend to write any macros or add visual basic code to a spreadsheet the Developer ribbon must be activated.
Select File Options Customize Ribbon. In the Main Tabs box, make sure that the Developer check box is selected. B 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. A folder for startup files 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. (machine folder) C: Program Files (x86) Microsoft Office Office14 XLSTART. (personal user folder) C: Documents and Settings 'user name' Application Data Microsoft Excel XLStart or C: Users 'user name' AppData Roaming Microsoft Excel XLSTART 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:?
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'. Customising ribbons and buttons 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. Modify a formatting style 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. A default workbook template 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. 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:.
create the new workbook by typing +. copy your book template into your personal templates folder (e.g. 'C: Users (your username) AppData Roaming Microsoft Templates'). You can then use use File New My Templates Create. 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. 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. 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. 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. 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. Function AddPunctuation(MyCode as String) as String ' Add punctuation into an unformatted code If Len(MyCode) = 11 Then AddPunctuation = Left(MyCode,4) & '.'
& Mid(MyCode,5,1) & '-' & Right(MyCode,6) Else AddPunctuation = MyCode Endif End Function A B Get this information as a document accompanied by Excel worksheets for details about obtaining this file. It has been rewritten for Excel 2010. 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.
Tip: By default, Excel automatically opens files located in the /Applications/Microsoft Office 2011/Office/Startup/Excel folder. To specify an alternate startup folder, on the Excel menu, click Preferences, click General, and then in the At startup, open all files in box, type the location of the files that you want Excel to open automatically. Open all the workbooks in a folder. In the Finder, move all workbooks that you want to use to /Applications/Microsoft Office 2011/Office/Startup/Excel.
If the workbooks that you want to use are stored on a network drive, or if you don't want to move the workbooks from their current location, create an alias for these files. For help with creating aliases, see Apple Help. Tip: By default, Excel automatically opens files located in the /Applications/Microsoft Office 2011/Office/Startup/Excel folder. To specify an alternate startup folder, on the Excel menu, click Preferences, click General, and then in the At startup, open all files in box, type the location of the files that you want Excel to open automatically.
Stop a workbook from opening automatically Excel automatically opens files that are stored in either the Excel Startup folder or an alternate startup folder. To prevent a file from opening each time you open Excel, move the workbook from either the /Applications/Microsoft Office 2011/Office/Startup/Excel folder or the alternate startup folder to a different folder. Tip: By default, Excel automatically opens files located in the /Applications/Microsoft Office 2011/Office/Startup/Excel folder. To specify an alternate startup folder, on the Excel menu, click Preferences, click General, and then in the At startup, open all files in box, type the location of the files that you want Excel to open automatically. Hide the Excel Workbook Gallery. On the Excel menu, click Preferences.
Under Authoring, click General. Clear the Open Excel Workbook Gallery when application opens check box. The next time that you open Excel, it opens a blank workbook.