Objectives of this Tutorial

Generally: To change the layout of a worksheet by inserting and deleting rows and columns and to work with multiple sheets.

Specifically: On completion of this lesson, you will be able to:

• Insert and delete rows and columns
• Insert and delete selected cells
• Insert and delete worksheets
• Move and copy a worksheet
• Rename a worksheet
• Change the colour of a worksheet tab

A worksheet can be re-organised by adding or removing columns and rows or cell ranges.

You can restructure a workbook by adding, copying, moving or deleting up to 255 worksheets. To make a sheet easier to identify, you can rename it and change the colour of the sheet tab.

Insert and Delete a Row, Column or Selected Cells

Columns and rows can be inserted at any time. Cell references in all the formulae adjust automatically. Where the insertion falls within a SUM function range the new cells are included in the total.

Where a formula on the sheet refers to a deleted cell the message #REF is displayed. It also appears in the formula where the reference has been erased; for example, =C2+#REF!+C3. If the deletions fall within the range of a SUM function the formula will adjust to the reduced range automatically.

To insert a column or row

• Select the column(s) or row(s) where the new ones are to be inserted
• Right-click the selection and from the shortcut menu, select Insert (SpeedKey: Ctrl + +)

OR

• From the Insert menu, select Columns or Rows

If the selected columns or rows have formatting options applied to them, the Insert Options smart tag is dsplayed. This smart tag offers you ways to format the newly inserted column(s) or row(s).

• Click the down arrow to reveal the smart tag options
• Select a choice as required

Note: The smart tag must be used as the next action after inserting the columns/rows.

To delete a column or row

• Select the column(s) or row(s) to delete
• Right-click the selection and from the shortcut menu, select Delete (SpeedKey: Ctrl + -)

OR

• From the Edit, menu select Delete

To insert or delete selected cells

If a group of cells is selected (as opposed to whole columns/rows), the Insert or Delete options will display the following dialogue boxes.

The options allow you to choose whether you wish to insert or delete only the selected cell(s) by shifting the existing cells or to insert/delete whole columns/rows.

• Select an option
• Click OK

Insert a New Worksheet

A new workbook contains three worksheets named Sheet1, Sheet2, and Sheet3. Additional sheets may be added or existing sheets duplicated up to a total of 255. Sheets not required can be deleted. Sheets can be renamed and colour-coded to identify them clearly. You can move and copy sheets into a different sequence in the workbook.

To add a new worksheet

• From the Insert menu, select Worksheet

A new sheet is inserted before the current sheet, and given the next sheet number in sequence.

OR

• Right-click a Sheet tab and from the shortcut menu, select Insert...

The Insert dialogue box is displayed.

• Select the Worksheet icon
• Click OK

A new sheet is inserted before the current sheet, and given the next sheet number in sequence.

Continued...