Spreadsheet

1. Open Microsoft Excel and choose a Blank Document
2. Click the cursor on cell B2, type Student Names and press Return on the keyboard
3. In cell C2, type Pre-Assessment %
4. In cell D2, type Post-Assessment %
5. In cell E2, type (Net) Learning Gain %
   
   
6. Click on the number 2 on the left column to highlight the entire row
7. From the Format menu, choose Cells, then select the Font tab and select Bold and click OK
8. Click on the line between the letter at the top of the columns and drag to adjust the column width.
   
   
9. In column B, type the names of your students
10. In column C, type the pretest scores (in percentage or actual scores)
11. Click and drag the mouse to highlight all the student names and the pre-assessment scores
    
    
12. From the Data menu, select Sort
13. Setup the Sort By as Pre-Assessment in Descending Order
14. Visually divide the scores into four "clusters. Clusters do not need to be even-sized. Choose logical beak-points between groups of scores. (There needs to be at least two students in each cluster.)
    
    
15. Click on the number in the left column next to the first name in your second cluster to highlight the row
16. Insert a new row by clicking on Row in the Insert menu. This will insert a new row between cluster one and cluster two.
17. Repeat this process placing a new empty row between clusters two and three, and between clusters three and four
18. In the first newly generated empty row type in "Cluster 1 Average" in column B. Type "Cluster 2 Average" after in the empty row between clusters two and three and repeat the process for clusters three and four.
    
    
19. In column D type in the appropriate Post-Assessment scores for each student
20. In column E3, type "=D3-C3" and press Return
21. Click on cell E3, then click and hold on the very small blue dot in the lower right corner of that cell and drag down to include the row with the last student's name. This will copy that formula into all the cells.
    
    
22. In the first blank cell in column C (this is the average row), type "=AVERAGE(
23. Click on the first score in the cluster and drag down to the last score in the same cluster. Type a right parenthesis and press Return. This will enter the average of that cluster in this cell.
24. To round this average to the nearest hundredth, click on the cell, select Cells in the Format menu, select the Number tab and then the Number in the column of choices; finally select 2 in the decimal places and follow through.
25. Click on this average cell you just created. Type a $ between the letter and the number of cell identifier, e.g. =AVERAGE(B$2:B$6)
26. Click on this average cell you just created, then click and hold on the very small blue dot in the lower right corner of that cell and drag to the right to include all three columns of scores. This will again copy that formula into all the cells.
27. Repeat this process to get the average of all four clusters.

1. Click here

1. Open a blank spreadsheet in a spreadsheet program
2. Place the labels across the top of the columns: Names, Pre-Assessment, Post-Assessment and Gain
3. Type the names, pre-assessment and post-assessment scores in the appropriate columns and rows
4. In the first row of the gains column type enter the formula to find the difference between the post- and pre-assessment scores (type =, click on post-assessment cell, type "-", click on pre-assessment cell and press return)
5. Sort data by pre-assessment scores by clicking and dragging to highlight all names and scores, but not the labels, and selecting Sort in the Data menu - Setup up Sort to sort by pre-assessment in descending order
6. Looking at the pre-assessment scores, mentally divide the scores into four reasonable clusters of at least two scores per cluster
7. Insert a blank row after each cluster by clicking on the first name of the next cluster and inserting a new row (Insert menu and select row)
8. Type Average in each new row in the Names column and enter the formula "Average" in each column of data for each cluster
9. Format the table to ease of reading and understanding

1. Click mouse on cell B2 and drag to the last filled cell in column E, 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. Click Finish
10. Your chart will appear as you selected. 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.)
11. To make changes to margins or page orientation, from the File menu, choose Page Layout
12. To make changes to the chart itself, choose the Chart option in the menu bar

1. Highlight all the data for the chart including the student names
2. Select the Chart Wizard in the top toolbar
3. Select a Chart type (bar or column recommended) and complete all the options (If the chart doesn't do the job, start over again)
4. Experiement!

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 C 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.