Return to
   Instructor's
   Homepage

Home
Syllabus

Lesson 1
Lesson 2
Lesson 3
Lesson 4
Lesson 5

Lesson 6
Lesson 7
Lesson 8
Lesson 9
Lesson 10

Lesson 11
Lesson 12
Lesson 13
Lesson 14
Lesson 15

Schedule

email Denvy

1. Open Microsoft Excel
2. From the Project Gallery, choose Blank Document
3. Click the cursor in cell 1A. Type "Student Names" and press the return key
4. In Cell 1B, type "Pre-Assessment %"
5. In Cell 1C, type "Post-Assessment %"
6. In Cell 1D, type "(Net) Learning Gain %"
7. Click on the number 1 on the left column to highlight the entire row.
8. From the Format menu, choose Cells
9. Click on the Font tab
10. Change the font style to bold
11. Click OK
12. Click on the line between the letters at the top of the columns and drag to adjust the column width. (or double click to automatically set correct width)
13. In Column A, type the names of your students (first names only)
14. In Column B, type the pre-test scores (in percentages, or actual scores)
15. Click and drag the mouse to highlight all student names and pretest scores (cell 2A-?B)
16. From the Data menu, choose Sort
17. Change the Sort By category to Pre-Assessment (click the arrow and drag down)
18. Change to Descending order
19. Click ok
20. Divide your scores into four "clusters" from high to low. Clusters do not need to be even-sized. Choose logical break-points between groups of scores. (You must have at least two students in each cluster.)
21. Click on the number in the left column next to the 1st name in your 2nd cluster, to highlight the row.
22. From the Insert menu, select Cells. This will insert a row of empty cells between Cluster 1 and Cluster 2.
23. Click on the number in the left column next to the 1st name in your 3rd cluster. Repeat step 22.
24. Click on the number in the left column next to the 1st name in your 4th cluster. Repeat step 22.
25. In the 1st blank cell in column A, type "Cluster 1 Average."
26. In the 2nd blank cell, type "Cluster 2 Average."
27. In the 3rd blank cell, type "Cluster 3 Average."
28. In the cell after the last student name, type "Cluster 4 Average."
29. In Column C, type in the appropriate Post Test Scores for each student.
30. In Cell D2, type: =C2-B2 and hit Return.
31. Click on Cell D2 to select it.
32. Click and hold on the small blue handle in the lower right corner of the selection box.
33. Drag the selection box down the column. This will copy the formula down the column, correcting for location in the column)
34. In Column D, highlight the 0 in each Cluster Average Row and press the delete key to clear it.
35. In the 1st blank cell in column B, type: =average(
36. Click and drag the mouse from the first score in the cluster to the last score in the cluster. This will enter the range of cells into the parentheses.
37. Then type ) to close the parentheses, and press the return key.
38. A number will appear in the blank cell which represents the average of the Cluster 1 scores.
39. To round the number to the nearest hundredth, click on the number to highlight it.
40. From the Format menu, choose Cells
41. Click on the Number tab
42. In the Category box, choose Number
43. Make sure Decimal Places is set at 2
44. Click OK
45. Click on the cluster average cell that you just formatted to select it
46. Click and hold on the small blue handle in the lower right corner of the selection box
47. Drag the selection box across the row. This will copy the formula across the row, correcting for location in the row)
48. Repeat steps 35--47 for your Cluster 2, 3 and 4 averages.
49. Click on the number in the left column next to Cluster 1 Average to highlight the row
50. From the Font menu on the Formatting Palette, click on the Bold icon next to Font Style
51. Repeat steps 49-50 for your Cluster 2, 3, and 4 Averages

1. Click mouse on cell A1 and drag to the last filled cell in column D, to highlight all information.
2. Click on the Chart Wizard button on the toolbar at the top of the screen.
3. Under Chart type, choose Bar
4. Under Chart sub-type, select the first option
5. Click Next (make no changes)
6. Click Next again
7. In the Chart title box, type a title (Student Learning Gains)
8. Click Next
9. Select As a New Sheet
10. Click Finish
11. Your chart will appear on a new page. You may click on it to select it and then resize as with any graphic. (Note: if the chart is too small, some of the student names may be omitted. Simply drag the chart longer to correct this.)
12. To make changes to margins or page orientation, from the File menu, choose Page Layout
13. To make changes to the chart itself, from the Chart menu, choose Chart Options
14. Click on the Sheet 1 tab at the bottom of the screen to return to the Table view.

1. Separate out the Cluster average information as explained in the following steps.
2. Click on the number next to Cluster Average 1 to highlight the entire row.
3. From the Edit menu, choose Copy
4. Click on the number of a row several below your last student's name.
5. From the Edit menu, choose Paste.
6. This will paste the entire Cluster Average row below BUT the numbers will be errors.
7. Repeat Steps 2-5 for Cluster Averages 2-4.
8. Click on the first "number" in the Cluster Average 1 row on your new table.
9. In the text box at the top of the page, delete all text
10. While still in the text box, type =, then the cell coordinates for your Cluster 1 Average (for example, =B5) (or simply click on cell B5 to copy that formula into the Cluster 1 average cell in your new table)
11. Click Return and the correct number should appear after Cluster 1 Average at the bottom of the page.
12. Check your answer. Both Cluster 1 Averages should be the same.
13. Repeat Steps 8-12 for Cluster Averages 2-4 on your new table.
14. Highlight all the numbers in columns B on your new table.
15. Click and hold on the small blue handle on the lower right corner of the selection box.
16. Drag the selection box to the right across all three columns.
17. When you let go, the formulas should have filled across the rows.
18. Check to see that all numbers match the Cluster Average numbers in the first chart.
19. Click on the number 1 to highlight the row of column headers.
20. From the Edit menu, choose Copy
21. Click on the number of the row just above your lower Cluster Average 1 to highlight it.
22. From the Edit menu, choose Paste.
23. Change the words Student Names (that you just pasted) to read Cluster Averages.
24. Click on the cell with the words Cluster Averages and drag to highlight all five rows and four columns.
25. Click on the Chart Wizard button on the toolbar at the top of the screen.
26. Follow steps 3-8 in the previous section to make a chart.
27. Change step 9 above to select Save as Object in Sheet 1
28. Click finish
29. Your chart will be floating on top of your table. Click it to select it and drag it to the proper location at the bottom of the page, below the table. Resize if necessary.
30. To view the page breaks, from the View menu, choose Page Break View. (Note: you will not find this option if the chart has been selected. Click on the table data to reset the View menu)
31. Make sure that the chart is within the margins of the page. If not, resize or move the chart to correct.

1. Click anywhere on the page outside the chart. (i.e. be sure the chart is not selected.)
2. From the File menu, choose Print Preview to see what your page will look like.
3. If the chart is cut off, return to the regular view and move the chart so it fits within the margins.
4. To make other changes to the look of the printout, from the File menu, choose Page Setup
5. Click on the Sheet tab to select elements to print, such as gridlines or column headers.
6. When satisfied, from the File menu, choose Print.