Adding a Student to the Attendance Worksheet  

Activity 1: Adding a Student to the Attendance Worksheet

1. Open the worksheet "Attendance Sheet.xls" if necessary.

2. Click on cell A22 and type a new student name (Carver, Josie). Press the Tab key to go to cell B22 and type the student number 318. Press Enter.

3. Save your work.

4. Add another student (King, Carole - student number 319) - but this time, we will use the Insert-Row menu command to place Carole between Cindy Jones and Anne Lamb.

• Click on the row header (14) beside Anne Lamb. This selects the entire row.
• Click on the Insert menu, and choose Rows. A new row is inserted just above the row you selected.
• Notice that the rows below have shifted and renumbered to accommodate the new row

5. Enter the new student and student number in row 14.

6. Save your work!

Insert rows

1. To insert a single row, click a cell in the row immediately below where you want the new row. For example, to insert a new row above Row 5, click a cell in Row 5.

To insert multiple rows, select rows immediately below where you want the new rows. Select the same number of rows as you want to insert.

2. On the Insert menu, click Rows.

Insert columns

1. To insert a single column, click a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of Column B, click a cell in Column B.

To insert multiple columns, select columns immediately to the right of where you want to insert the new columns. Select the same number of columns as you want to insert.

2. On the Insert menu, click Columns.

Sorting Data: Selecting the Sort Area

Activity 2: Sorting Data: Selecting the Sort Area

1. To sort data in an Excel worksheet, you first must specify the data cells to be sorted. Excel will sort only the selected cells.

2. As an example, we will sort your Attendance Sheet incorrectly at first. The we will use the Undo button to go back to a point where we can sort correctly.

3. Select the cells A4 - A23. Do this by clicking on cell A4, the dragging down the column through cell A23. Another way to select multiple cells is to click and release the first cell in the range to be selected (A4), hold down the Shift key, and click the last cell in the range (A23). Your Attendance sheet should look like the first picture below.

4. To do a quick sort of the selected data, click on the Sort Ascending button on the toolbar. Your Attendance sheet should now look like the second picture. Notice that column A data is now alphabetical, but Column B data has not been changed. Student numbers no longer match the student names. This is where most people make mistakes when sorting data in an Excel Worksheet. Remember: When sorting a data table in Excel, be sure to select the entire table!

5. Click the Undo button to return the data table to its original.

6. The second picture shows the correct way to select the cells to be sorted.

Rules for selecting text, cells, ranges, rows, and columns

To select

Do this

Text in a cell

If editing in a cell is turned on, select the cell, double-click in it, and then select the text in the cell.

If editing in a cell is turned off, select the cell, and then select the text in the formula bar.

A single cell

Click the cell, or press the arrow keys to move to the cell.

A range of cells

Click the first cell of the range, and then drag to the last cell.

All cells on a worksheet

Click the Select All button.

Nonadjacent cells or cell ranges

Select the first cell or range of cells, and then hold down CTRL and select the other cells or ranges.

A large range of cells

Click the first cell in the range, and then hold down SHIFT and click the last cell in the range. You can scroll to make the last cell visible.

An entire row

Click the row heading.

An entire column

Click the column heading.

Adjacent rows or columns

Drag across the row or column headings. Or select the first row or column; then hold down SHIFT and select the last row or column.

Nonadjacent rows or columns

Select the first row or column, and then hold down CTRL and select the other rows or columns.

More or fewer cells than the active selection

Hold down SHIFT and click the last cell you want to include in the new selection. The rectangular range between the active cell and the cell you click becomes the new selection.

Sorting Data: Using the Data - Sort Menu

Activity 3: Sorting Data: Using the Data - Sort Menu

1. Sort your Attendance sheet by using the Data-Sort command:

• Select A4-B23
• Click on the Data menu and choose Sort
• At the bottom of the Sort dialogue box, click No Header Row
• Click the down arrow in the first Sort by box and choose Column A - Ascending
• In the second Sort by box, choose Column B - Ascending
• Click OK

2. Save your work. Attendance Sheet.xls should look like the picture at the bottom of this page

Sort from the Data-Sort menu

This menu allows you to sort by the data within a specific column, i.e. either student name of student number. If you choose No Header Row, you must specify columns to sort by. Notice that you can sort using two different criteria.

You have now completed this module. Take a few minutes to review and practice what you have learned.