Spreadsheet Grading and Microsoft Excel

Officially, the most important responsibility of a teacher is to fairly and effectively evaluate students’ work, and assign them a grade based on their performance. The administrative aspect of grading can be a bit tedious, but it is such an essential part of the classroom experience that there is virtually no room for error. Regardless of all the words of wisdom you pass on to your students, their primary concern will always be their final grade. It is your job to make sure that the grade they receive is the grade earned. It seems simple enough, yet it can become quite complex to compute grades when you factor in attendance, papers, projects, quizzes, assignments, tests and any other measure of evaluation.

Using Microsoft’s Excel spreadsheet program is an effective way to minimize your stress and workload when recording and computing grades. Excel offers a number of functions that can simplify the grading process, organize your records, and minimize your hassle.

NOTE: It is a good idea to keep a backup copy of your gradebook, either on a floppy disk or in an actual gradebook that can be acquired in the front office. NEVER EVER rely on just one gradebook or you could be asking for serious trouble.

1. GETTING STARTED:

When you import your class roster from your e-mail (see document on Class Rosters), it will appear to be somewhat of a jumbled mess. Excel automatically condenses the information in order to save space. This, however, can make reading the roster quite difficult. The first step then is to "clean up" your roster to suit your preferences. Part of this "clean up" process includes widening some columns to allow space for all of the information (e.g. the names column), as well as deleting any unnecessary information that you do not want on your grade sheet. For those instructors with two sections or separate rosters, you will want to cut and paste your rosters together at this point to consolidate your grading.

• To make more room for each column: Move the cursor to the line dividing the column you want to lengthen and the column directly to its right. You should do this at the very top of the spreadsheet between the letters (e.g. A and B). When the cursor is placed over the vertical dividing line, a figure should appear resembling a vertical line with arrows pointing in either direction. (The same instructions apply to rows.) While depressing the button on the mouse, drag the cursor to the extended length desired and let go of the mouse button. Columns or rows can be increased or decreased in length to suit your needs.
• To delete information: Highlight the individual cell (little boxes) by simply clicking on the cell. A thick black line should circle the cell to mark that it is highlighted. To highlight an entire column or row, click on the gray shaded letter or number respectively. The entire column or row should become highlighted in black, which will let you know that it has been selected. Another option is to click on one cell, hold the mouse button down, and drag the cursor over a span of other cells that you wish to delete. For instance, perhaps you wish to highlight cells B6 through B15 only. Just click on B6 and drag down to B15. This same dragging function can allow you to highlight several rows or columns at the same time by dragging over the gray shaded area. Once the cell(s) that you want to delete are highlighted, go to "Edit" on your toolbar and drag down to "delete." If you mistakenly delete something you didn’t want to, go back to "Edit" and drag down to "undo delete" to bring it back ("undo delete" only works if it is the first command made after clicking on the delete option).
• To insert additional space: The "Insert" function on your toolbar allows you to add rows or columns or individual cells to your spreadsheet. Click on the gray shaded row number or column letter where you want to make the insert. Click on the "Insert" at your toolbar, and drag down to the appropriate item. The "Edit" function is also available if you wish to undo your insert and start again. You can highlight multiple rows or columns if you wish to insert a similar number of rows or columns at one time. To insert an individual cell, highlight the cell and go back to "Insert" on the toolbar, and drag down to "cells." Excel will give you an option menu on how you would like to insert the new cell. Select the appropriate option and click "OK."
• Cutting and pasting spreadsheets together: This process is similar to cutting and pasting on a typed document. Highlight the entire roster by dragging the cursor down the length of the roster. Next, click on the copy icon on the toolbar to the right of the pair of scissors. (HINT: If you place the cursor on any of the icons WITHOUT pressing the button, a yellow label will appear below to let you know what the icon represents. Also, at the bottom of the screen, right above the start button, a brief description is provided as well.) Then go to your other Excel document and at the bottom of the roster, highlight a cell and click on the "paste" icon which is immediately to the right of the "copy" icon. It’s that simple.
• To enter text or numbers: Once things are clean as you would like, you can customize your spreadsheet to meet your specific needs. Columns can be designated by assignment, date, score, or any combination of entries. The important thing is that the organization be set up for whatever works best for you. Highlight the cell or cells in which you wish to enter the text or number, type the text/number, and then click on another cell or press enter. You can edit within these cells by typing over what was previously entered.

2. RECORDING GRADES:

As previously mentioned, Excel offers a variety of means for arriving at the same end. This manual will only cover the basic applications, but these are certainly not the extent of what can be accomplished. Immediately underneath the tool bar is something referred to as the "status bar." The status bar provides you with information about the particular cell that is highlighted. At this point, most of your cells will be rather straightforward. As you begin to enter information about grades, attendance etc. your cells will become more complex, and the status bar is a good way to keep track of how you derived the numbers within each cell. If you are thoroughly confused, hang on....

The way to compute necessary information like averages, standard deviations, sums etc. using Excel is by way of formulae, or specific commands typed into a particular cell. For example, you have just entered several data entries into your spreadsheet, and now you would like to make sense of them all.

Let’s say you wish to find the average and standard deviation of Quiz #1, so you can compare it to that of Quiz #2. In cell F7, you would type: =AVERAGE(F2:F6) That is a formula! Then hit enter, and your average for Quiz #1 will appear in cell F7.

For the standard deviation of Quiz #1, go to cell F8 and type: =STDEV(F2:F6) followed by the enter key. Repeat the same steps for Quiz #2 (make sure you change the cells specified in your formula) and there are your numbers for comparison. If you were to highlight cell F7, the status bar (remember the status bar) will show you the formula used to produce the value of F7. Pretty neat, huh?

There are a number of different commands you can use to find out a variety of information. Some basic commands include:

=A2+A3 add cells A2 and A3
=A2-A3 subtract A3 from A2
=A2*A3 multiply A2 by A3
=A2/A3 divide A2 by A3

You can do any combination of these just like you were doing math on a chalkboard.

Now say that you want to find the sum of a group of cells. Highlight the cell where you would like the sum to appear, click on the summation button located on the tool bar (it looks like this: å ), highlight the cells you would like summed, and press enter. For example, for Homer, you would highlight cell H2, click the summation button, highlight cells B2 through G2 and hit enter. Your sum will appear in H2.

You can also copy a formula from the status bar and have it apply to the rest of your row or column. This is a tremendous time saver with larger classes. To copy formulas to other cells, highlight the cells or cells to be copied, bring down the "Edit" menu from the toolbar and click on "Copy." (The highlighted cell should now flash.) Highlight the cell or cells to which you wish to copy and press enter. For this class, you would highlight cell H2, copy it, then highlight H3-H6 and hit enter.

3. MORE OPTIONS:

• Freezing Windows: While you are entering data, it can be confusing to have to continually scroll back to the early columns to make sure you are inputting the right grades under the right person’s row. This problem is easily resolved by "freezing" the window. Go to the "Window" option above the toolbar and drag down to "Freeze Panes." This command will freeze the upper left hand quadrant of the window you are currently looking at. You can still scroll around your spreadsheet, but that corner of your screen will not move. Usually, you will want to be at the top of your document to allow yourself more flexibility in your viewing. To unfreeze the window, just repeat the same step and the option this time will read "Unfreeze Panes."
• Sorting Rows and Columns: The "Sort" command can be extremely useful in that it allows you to rearrange the data quickly and easily. You can organize it alphabetically or by grade, and you can do it in ascending or descending order. To reorder information in your spreadsheet:
• Select the cells that you would like to organize. NOTE: If you want to sort your roster by grade, you must select all of the cells associated with each student. For example, highlight their names, social security number, grades and all other information. If you only highlight the grades, then the grades will appear in ascending or descending order, but they will no longer coincide with the names. Big Trouble. ALWAYS HAVE A BACKUP GRADEBOOK!
• Click on "Data" from the menu line.
• Click again on "Sort"
• Choose the "Row" or "Column" button, depending on how you would like the data to be sorted.
• In the first key box, type the location of the cell that represents those to be reorganized.
• Select the ascending or descending button.
• Click on OK.
• If you make a mistake, use the "Edit/Undo" function as previously shown.
• Help: Excel offers a wonderful help function that can provide quick and easy answers to specific questions you may have. Click on the "Help" command on the toolbar, and drag down to "Answer Wizard." The Answer Wizard will prompt you to type in a description of whatever it is you are looking for. For example, if you type in: "Standard Deviation," the Answer Wizard will provide you with a list of instructions related to many aspects of the standard deviation. Find which one applies to your question, double click on the text, and a help screen will provide more information. If you do not find what you are looking for, try a more general search by expanding your instructions to the Answer Wizard.

If you have difficulty with any part of the process, retrace your steps to make sure you have completed all of them accurately. If you are still having difficulty, do not hesitate to contact a labster for assistance.

Excel contains many more advanced spreadsheet features that can be used for grading. To learn how to use these advanced techniques, come in to the SRL and we will set you up with a manual and some personalized training.