Microsoft Excel – Working With Workbooks and Worksheets

Workbook defaults

There are various options in Microsoft 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 > GeneralChange ‘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 > GeneralChange 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 > GeneralChange ‘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 > SpellingChange ‘Dictionary language’

how to merge cells in excel

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.

1. 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 workbookHold down the Ctrl key, then click on 3 adjacent current worksheet tabs to select them.Release the Ctrl keyGo to Insert > Worksheet

2. 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

3. 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 keyHold 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.

4. 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.

1. 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.

2. 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.

3. 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.

3. 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%.

4. 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.