Excel Practice Activity #2

Note: When you see terms enclosed with brackets (e.g., <return>) it is asking you to strike that key.

Items in bold are highlighted to show what is to be typed into a cell. They do not need to be typed in bold.

Opening the Program

PC Look for the Excel Program Icon in the Quick Start menu (Windows 98 and above), in the Start menu (Programs), or in the C drive under Program Files: Microsoft Office

Mac Either look for the Excel Program Icon in the launcher, or in the Applications folder of the Hard Drive, or in the Microsoft Office Folder on the Hard Drive

Entering Data

1. Cell A1 should be highlighted, type: Last Name <return>

2. Fill in the last names as listed below and pressing <return> after each one.

3. Click on cell B1 and type: First Name <return>

4. Fill in the last names as listed below and pressing <return> after each one.

5. Follow the same procedure to fill in Test 1, Test 2, and Final Scores as listed below.

Last Name First Name Test 1 Test 2 Final

Roe Rachel 86 83 83

Doe John 72 80 76

Moe Noah 66 58 68

Coe Karen 90 97 97

Calculate average score for each student

1. Click on F1 and type: Average <return>

2. In F2, type: = AVERAGE(C2:E2) <return> (Don't forget = sign!) The number 84 should appear.

3. Click on F2 again and drag down to highlight the column down through F5.

4. From Edit menu, select Fill then Down and release. All the students' scores are now averaged.

Change a score (and the average).

1. For example, you notice that Karen Coe's score on Test 2 should be 94, not 97.

2. Click on that cell and type 94. click away (note that her average is automatically adjusted).

Round off decimals.

1. Highlight all of Column F by clicking on the letter F.

2. From Format menu, click on Cells.

3. From the Format menu select Cells. In the conversation box that appears, select 0.00 then OK. (On a PC, first select the Custom category from under the Number heading.) The numbers are now rounded to two places.

Create a space between column headings and names and scores

1. Highlight all of row 2 by clicking on the number 2

2. From Insert menu, select rows. Release.

Adjust width of columns

1. Move the mouse to the border of C and D. The mouse changes to an arrow. Click and drag to left so that there is only room for the words Test 1.

2. Do the same for all columns so that they are evenly proportioned.

Calculate the Standard Deviation for Each Student

1. In cell B9 type: Stan. Dev

2. In cell C9, type: =STDEV(C3:C6) <return>

3. Highlight the four cells, go to Edit then Fill right.

Give final exam twice as much weight as the other tests

1. Click on Cell F3.

2. Type: = (C3+D3+E3+E3)/4 <return>

3. Click on F3 again and drag down to highlight column through F6.

4. From Edit, select Fill and Down.

Center data entries in cells

1. Highlight the test scores from C3 through F9.

2. From Format menu, select Cells.

3. When Dialog Box appears, click on Alignment.

4. When that Dialog Box appears, under the heading horizontal, click on Center, then OK.

Sort by alphabet

1. Highlight from cell A3 through F6.

2. From Data menu, select Sort.

3. In the first Sort Dialog Box, type: Column A and click on Ascending and then OK.

(Your grade book is now arranged by alphabet.)

Note: If you wanted to arrange the final averages from high to low for the purpose of assigning grades, perform Steps 1 and 2 above, then in the Column A highlighted box, type: Column F. Then click on Descending. Then OK. (Now the students' scores are arranged in order of their average scores from high to low. Try it.

Save Your Spreadsheet

1. Under the file menu select Save As.

2. Type in a name for your spreadsheet and choose a location to save to (disk, hard drive).

3. Click OK

Quitting the Program

1. After saving your work, select Quit from the File menu.