How To Change Default Settings In Excel 2010

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 get-go?

If the date characters (eastward.g. MM/DD/YY) are in the wrong order for your land and page layout measurements are in the wrong units (i.eastward. inches or centimeters) the trouble lies in your PC settings rather than with Microsoft Role. From the Windows Commencement menu, select | Control Console | Clock, Language and Region |. Check that the language is set up to English United kingdom (or whatever is appropriate for your location) and that the number, time, date and currency settings are as yous wish them to be.

Some of the default setup options within Excel tin be modified by selecting [File] Options.

The default file location is of import and specifies the proper name of the folder on your PC (or network) which will contain all of your data files (i.e. spreadsheets). Don't always save your files in any of the Excel program folders. This volition make backing upwards or copying files extremely difficult. You should create a new binder and proper noun it something like 'C:\Users\local_name\Documents\Excel'.
The number of worksheets in each new workbook. By default this is three and you can Insert more at whatsoever time if you demand them.
To set the default Font and Size due east.g. Arial xi, select [File] Options | General.
If you intend to write any macros or add visual bones code to a spreadsheet the [Programmer] ribbon must be activated. Select [File] Options | Customize Ribbon |. In the Main Tabs box, make sure that the Developer check box is selected.

Personal.xlsb

A special file called Personal.xlsb can exist used to save whatsoever 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 pick [Developer] Lawmaking | Record Macro. [b] In the Record Macro dialog box, ensure the macro is to be stored in the Personal Macro Workbook. Movement the cursor to represent a uncomplicated 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] Shut all other workbooks and exit from Excel. The plan will prompt you to salve a new Personal macro workbook.

Thereafter, whenever you open Excel, the Personal macro workbook will open as a hidden file and any saved macros volition be available. In order to edit or add a macro, the workbook must exist unhidden via: [View] Window | Unhide and and then using the [Developer] Code | Visual Basic option. After editing the lawmaking, hide the workbook again. When Excel is closed, yous 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 binder into the machine XLstart folder (see beneath).

Whenever you lot record and save a macro you are given the option of saving it in either the current workbook or in your Personal macro file. Retrieve that if you requite someone else a workbook which makes use of a function stored in your Personal file, the office 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 bones, Excel allows y'all to record useful macros. Y'all may choose to record the various keystrokes necessary to set your page margins and formatting and to add a header and footer. This folio setup macro may exist useful when modifying other peoples spreadsheets or files imported from other programs.

A folder for startup files

When Excel starts information technology tin automatically load various workbooks. It looks for the Personal.xlsb macro workbook and for a Book.xltx template (run into below).

Excel will have created one or two special folders on your PC and it checks for files in both of these locations

(machine binder) C:\Program Files (x86)\Microsoft Office\Office14\XLSTART
(personal user folder) C:\Documents and Settings\"user proper noun"\Application Data\Microsoft\Excel\XLStart\ or C:\Users\"user name"\AppData\Roaming\Microsoft\Excel\XLSTART

Information technology 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 Firsthand Window. Blazon the post-obit code in the Firsthand Window and and so press <Enter>: ? application.StartupPath

If y'all likewise want to load other specific workbooks whenever Excel starts up and do not want to use either of the XLstart directories you tin specify another binder via: [File] Options | Avant-garde | Full general - 'At startup open up files in'.

Customising ribbons and buttons

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

It may be best to avoid customising the main ribbons until yous are familiar with the plan and accept determined which buttons are merely 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 nearly commonly used options. In the instance (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 volition too allow you to edit and customise the main ribbons.

Modify a formatting fashion

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 manner can be altered to remove the decimal places and to prevent the left alignment of the minus symbol i.east. from |- 1,234.56 | to | -1,234 | .

modify a style

Select [Home] Styles | Cell Styles | and so right click on Comma and Alter. 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 post-obit character is to be left blank. This allows you to forcefulness numbers to exist correct aligned, taking into account the width of the closing subclass used for negative numbers. The asterisk * means whatsoever characters after it will be justified to the right and the padding will be made upwards by the following graphic symbol (ordinarily a space). This lets yous identify characters (such as a currency symbol) to the left of the jail cell and the remaining digits aligned to the right.

A default workbook template

New spreadsheets tin exist based on a user divers template chosen chosen Book.xltx. It can contain various preferences such as the font proper name and size of a title to be placed in a detail jail cell (eastward.g. A1). Page headers and footers and number formats tin can also be defined.

Add together headers and / or footers past 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 folio (around row fifty) and click in the left, middle or right Footer field. A new Header and Footer Blueprint tab will appear and its ribbon contains various buttons for data fields such as filename or date. Remember to practice this on all of the worksheets in your template.

excel header

Your template can also include any modifications made to the default number styles (east.g. Comma or Currency).

Once you are happy with your creation select [File] Salve, change the File Type to 'Template (*.xltx)' and name it Volume.xltx. It must exist saved in the XLStart folder (encounter in a higher place).

Once the Book.xltx template has been saved, the new settings should appear each time you open upward Excel. Strangely the template does not re-appear if y'all utilise [File] New | Bare Workbook | Create. Excel ignores Book.xltx and opens a blank volume. In order to create a new workbook with your Volume template there are ii options:

create the new workbook by typing <Ctrl> + <n>
re-create your book template into your personal templates folder (e.g. 'C:\Users\(your username)\AppData\Roaming\Microsoft\Templates'). You lot tin then use use [File] New | My Templates | Create.

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

Excel 2003 - older versions of Windows

Where to start? (2003)

If the appointment characters are in the incorrect club for your country e.thousand. MM/DD/YY (for the Britain) 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 | Command Panel | Regional Options. Cheque that the language is ready to English UK (or whatever is advisable) and that the number, time, engagement and currency settings are equally yous wish them to exist.

Some of the default setup options within Excel tin exist modified within Tools | Options | General:

the default file location. The location of the folder which will contain all of your information 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. Usually you would create a new binder 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 demand them.
the default Font and Size e.chiliad. Arial 11

How Excel saves settings (2003)

When a new spreadsheet is started you may wish to use a mutual grouping of preferred settings such every bit the font proper 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 yous request a new workbook.

excel styles

Open an empty workbook and in all of the worksheets, select all cells and change the font and font size to whatsoever yous crave (east.g. Arial xi)
Add headers and / or footers using either File | Page Setup or View | Headers and Footers. You tin add fields or text to a footer containing your name or visitor / department, the date, the filename ... anything. Recollect to do this on all of the worksheets.
Excel places buttons on your default toolbar to apply Styles such equally Comma, Currency and Percentage. Some of the default styles may non exist configured as you wish and display your numbers in unhelpful patterns and formats. Select a cell, click on one of the formatting manner buttons (eastward.g. Comma) and then select Format | Style | Modify. Modify it to something useful (possibly creating a Custom format) and and then the new format settings will be retained in Book.xlt. Click here for information about number formatting.
Each fashion tin can have 4 formats separated by a semi-colon. These determine how detail types of values or contents are displayed: Positive; Negative; Cypher; Text.
The underscore _ means that an empty space equivalent to the width of the post-obit character is to be left blank. This allows you to force numbers to be correct aligned, taking into account the width of the closing subclass used for negative numbers. The asterisk * means any characters later on it will be justified to the correct and the padding will be made upwardly by the following character (normally a space). This lets y'all place characters (such as a currency symbol) to the left of the jail cell and the residue aligned to the correct. Experiment with these codes (possibly using the Text function) before you lot kickoff defining them in your Book.xlt.

Once yous are happy with your creation select File | Salvage Equally and modify the settings of File Type to 'Template (*.xlt)' and name it Volume.xlt. The binder in which to locate this file may demand some exploration. Search through your Microsoft Office or Documents and Settings folders for the location of Volume.xlt or the XLStart folder. If it doesn't already exist save information technology in the /XLstart folder. When Excel is restarted information technology should detect this file and information technology's settings - if non try specifying the location using Tools | Options | General.

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

The dialog box for customising toolbars

Customising toolbars (2003)

In that location are many additional buttons which are not displayed on the 2 default toolbars (standard and formatting). For instance a toolbar button to set the print area volition mean you don't demand to visit File | Print Expanse | Ready Print Area simply can but 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 cull 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, effort making a uncomplicated alteration to a toolbar, then employ the Windows Get-go bill of fare option to Find Files with the wildcard '*.xlb'.

Personal.xls (2003)

If this file already exists, Excel will automatically open it upon startup only will hide it from normal view. The file on your hard disk volition probably be located in the aforementioned binder 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 salvage your own macros or user divers functions which yous may want available for general use and which are not specific to (and saved inside) a particular spreadsheet. When saving a macro you are given the selection of whether to relieve it in the electric current workbook or in your Personal.xls file. Recall that if you requite someone else a workbook which makes utilize of a function stored in your Personal.xls, the part won't exist available to them and an error volition occur in any cells where information technology has been used.

Fifty-fifty if you can non program in visual basic, Excel allows you to record a macro. You lot may choose to record the various keystrokes necessary to fix 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 corporeality 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 inside 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 lawmaking.

Office AddPunctuation(MyCode as String) equally String ' Add together punctuation into an unformatted code If Len(MyCode) = 11 And then AddPunctuation = Left(MyCode,4) & "." & Mid(MyCode,5,one) & "-" & Right(MyCode,six) Else AddPunctuation = MyCode Endif End Office

	A	B
1	ABCDE123456	Text in cell A1
2	= AddPunctuation(A1)	The formula
iii	ABCD.E-123456	The consequence

file: xlconfigure.htm

� meadinkent.co.united kingdom of great britain and northern ireland 2022

Last updated Apr15

CMIDX S3 P8 Y