mod6

Beginning Excel 2007

Module 6

Recording names and student numbers can be a full-time job. Just as soon as you have everyone in the correct order, you have a new student whose last name starts with the letter C. With Excel, sorting the names by last name, or any other order, is quick and easy. Using Excel, you can track information for each student, and then organize it according to importance or category. This module covers the following:

Adding a Student to the Attendance Worksheet

Sorting Data: Selecting the Sort Area

Sorting Data: Using the Shortcut Buttons

Adding a Student to the Attendance Worksheet

Activity 1: Adding a Student to the Attendance Worksheet

1. Open the worksheet "Attendance Sheet " 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 Home Tab and the Insert menu, and choose Insert Sheet 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

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.

On the Home Tab and the Insert menu, click Insert Sheet Rows.

Insert columns

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.

On the Home Tab and the Insert menu, click Insert Sheet 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.

2. 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 A to Z option, Home Tab, Edit Menu button on the toolbar.

Excel prompts you to make sure you want to sort associated data as well:

Choose to expand the selection and click "Sort". Notice that the student numbers remain with their respective names, however the entry "High Score" gets jumbled in and treated as a name. The "Date" Row gets thrown into the sort as well. Let us back up....

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.

WRONG

RIGHT

7. Select the cells to be sorted. (A4 - B23). Notice that Josie Carver is in the correct alphabetical order and that her Student Number is still with her. To sort by Student Number, You must select "Custom Sort" from the Sort Menu. In the "Sort by" dropdown box, choose (Column B) and click the OK button.

Use the Undo button to return your sheet to the Sorted by name version:

Save your work.

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.

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