Excel Settings and Options

Workbook defaults
There are various options in Excel that you can modify to make setting up your workbooks quicker and easier according to your preferences.

3_settings.htm 3_personalizing.htm
Workbook defaults
There are various options in Excel that you can modify to make setting up your workbooks quicker and easier according to your preferences.

You will find many of these options in the dialog box under Tools > Options. The most useful ones are described below:

Setting the default number of worksheets
As you have already seen, when you create a new workbook, 3 worksheets are inserted automatically. If you only ever use 1 worksheet and always delete the remaining 2, then change the number of default worksheets to 1. Similarly, if you often use, say, 6 worksheets, then set the default to 6. You can set a maximum of 255 worksheets.

Go to Tools > Options > General
Change ‘Sheets in new workbook’ to required value between 1 and 255

Setting the default font
You can also set the default font type and size.

Go to Tools > Options > General
Change Standard Font settings

Setting the default file location
When you go to Open or Save an Excel workbook you can determine which folder on your computer you want Excel to open. If, for example, you keep all of your spreadsheets in a subfolder of My Documents called “My spreadsheets”, then you can set this as your default file location.

Go to Tools > Options > General
Change ‘Default file location’ to read “C:Documents and Settings/user/My Documents/My spreadsheets”, where ‘user’ is your user account (login) name

Setting the default language
When performing a spell check on your computer, you need to ensure that the language setting is correct. When Microsoft software is installed, the default language is ‘English (U.S.)’. This can be changed to ‘English (Australia)’.

Go to Tools > Options > Spelling
Change ‘Dictionary language’

Modifying workbooks
In the previous sections you learnt how to change the default settings for workbooks. However, you still may need to make manual changes to some of your workbooks. You can add, remove or re-order worksheets.

Inserting worksheets
To insert a new worksheet into a workbook, go to Insert > Worksheet. This will place a new worksheet to the left of the active worksheet. To insert multiple worksheets into a workbook, first select that number of current worksheets, then go to Insert > Worksheet. You will then see the new worksheets to the left of the active range of worksheets. Try This:

Open a new workbook
Hold down the Ctrl key, then click on 3 adjacent current worksheet tabs to select them.
Release the Ctrl key
Go to Insert > Worksheet

Moving worksheets
You can change the order the worksheets appear in the workbook by dragging the sheet’s tab to the required position. Try This:

Hold your mouse over the tab labelled ‘Sheet1′
Hold down the left mouse button and drag the sheet tab to the left of ‘Sheet4′.
Release the left mouse button

Copying worksheets
Entire worksheets can be copied within the same workbook in a similar way to how we moved a worksheet in the previous exercise. By dragging the sheet tab whilst holding down the Ctrl key, the worksheet is copied to that new location. Try This:

Hold your mouse over the tab labelled ‘Sheet1′
Hold down the Ctrl key
Hold down the left mouse button and drag the sheet tab to the right of sheet tab ‘Sheet3′.
Release the left mouse button then release the Ctrl key

Note how the worksheet has been named ‘Sheet1 (2)’. Worksheets, within the same workbook, cannot have the same name, so Excel automatically numbers the sheets when copied.

Naming worksheets
You can rename worksheets to make them more meaningful and to help you better organise your data.

To name a worksheet, double click on the tab at the bottom of the worksheet and type the new name. Certain characters cannot be used in a worksheet name, such as / * [ ]

Deleting worksheets
To delete an entire worksheet, go to Edit > Delete Sheet. If there is data on the worksheet a warning message will appear to check you wish to permanently delete the worksheet.

Modifying worksheets
As well as making changes to whole workbook settings – as seen in the previous section – you can also change individual worksheet settings to enable viewing and working with your worksheets easier and more personalised.

Inserting rows and columns
At times you may need to insert data in between existing data in your worksheet. Inserting rows and columns into a worksheet does not create an additional row or column (beyond Excel’s limits) but shifts the existing rows down or columns to the right, removing the same number of rows or columns from the end of the worksheet.

Deleting rows and columns
You can also remove rows or columns from your worksheet. This is done in a similar way to inserting rows or columns. First select the row headers or column headers that require deleting, then go to Edit > Delete.

Hiding rows and columns
There may be data on your worksheet that you do not want to see or print, but you do not want to delete. To hide a row or column, right click on the row or column header then select Hide. To unhide a row or column, you must first select the rows or columns wither side of those that are hidden, then right click and select Unhide.

Zooming in and out
You can change the magnification to display more or less of your worksheet.
From the View menu, select Zoom. This will open a dialog box from which you can select a different magnification. The default is 100%.

Freezing panes and splitting windows
The Freeze Panes and Split commands in the Window menu allow you to divide your worksheet into sections so you can scroll independently within different parts of the screen.

The Freeze Pane option freezes the area at the top and/or to the left of the screen to keep any headings in place whilst allowing you to scroll the remainder of the screen. Click in the cell to the right and below the rows and columns you want to freeze then go to Window > Freeze panes.
The Split option actually splits the window into a maximum of 4 separate windows with independent scrolling capability in each.

Article written by:

Penny Buckwell
Owner, EasyPC Training
Brisbane, Australia

Provider of Microsoft and MYOB computer software training.


Article Source: http://EzineArticles.com/?expert=Penny_Buckwell

Leave a Reply