EXCEL SPREADSHEET NOTES
[Theresa Di's Tip]
ENTERING GRADES
You've given your first quiz/test/homework, and you are about to type in the grades in column H. Three students were absent. If you leave their cell blank, it will be counted as a zero in calculating their grade. (You may want this to show a student how his/her neglect in making something up is affecting his/her grade.) If you don't want it to be counted, you must type abs in the cell.
TOTAL POINTS
Cell G4 (the cell with the border around it) contains the formula that adds up the total possible number of points for all the quizzes/tests/homework. When you place the curser in the cell, the formula bar should read something like this: =SUM(H4:AP4). This formula adds up all the amounts in the range of cells H4 to AP4. The important thing to remember is that your range be large enough for the number of grades you enter. For example, if your range were only H4:J4, the grades you entered in the columns beyond J wouldn't be counted.
FILL DOWN
You've typed in your class list in column B and you've entered the grades for the first quiz in column H. The first kid's grade average is calculated, but not the others. What happened? You have to copy the formulas in columns D, E, F and G for the first kid down the columns for the others. To do that, place your curser in cell G6. You should see almost the same formula as above, except it should be =SUM(H6:AP6) since you are now dealing with row 6, not row 4. Now hold the left mouse down and drag down the column the number of rows as you have students. When you reach the botton, release the mouse. Your column should be "selected" (different color). Now press the Control and D (for "down") keys. This will fill the formula from the top row down the column. Now, repeat this process for columns D, E and F.
CLASS AVERAGE
If you want to know the class average for a quiz, you can create a row below the rows of students to do that. Let's suppose the grade for your last student was entered in cell H26 (column H row 26). In cell H28 you write this formula =AVERAGE(H6:H26). This will average all the grades in the range of cells H6 to H26. When you add a new column of grades in column I, you can "fill" this formula by beginning in column H row 26 and dragging across to row I. Then press the Control and R (for "right") keys. This will fill the formula from left to right across the row.
INSERT COMMENT
To insert a comment for a particular cell, go to the INSERT menu and click on COMMENT. You can resize the comment box by dragging on the lower right corner. When you have added a comment to a cell, you'll see a small red triangle in the upper right corner of the cell.
RESIZE COLUMN, ROW
When you place your cursor on the line between columns B and C at the very top, you'll see the cursor change form to a thin cross. You can then drag to the left or right to resize the column. The same process will also allow you to resize a row.
Return to Teacher Tech
Return to top
TEXT SPILL OVER
If you type text in cell C1 and there is nothing in cell D1, the text will "spill over" into cell D1 (and maybe into cell E1 if your text is long enough.) To make a change to a word at the end of your text, you have to click in the cell where you started, then click in the formula bar (or retype the entire text.)
ALIAS
I post my grades periodically during the quarter so the students know how they're doing. To maintain anonymity. I hide the names column, then sort by the alias column and print it out. See me if you want to do this.
Return to Teacher Tech
Return to top
CREATING A NEW SHEET
Here are Theresa Di's instructions for creating a new sheet in your gradebook. Use this when you are beginning a new quarter, but you don't want to retype all those names!
1. Open to Grades Folder
2. Open class.
3. Go to Edit menu >>> Move or Copy Sheet
4. Check box on "Create a Copy" and highlight where you want new sheet placed.
5. Click on OK.
6. To rename your new sheet, go to bottom of sheet and click on new sheet "Grades(2)" with right mouse button.
7. Left click on "rename" in pop-up menu.
8. Retype on Grades(2) desired title - Ex: Grades-2nd Qtr.
9. You are now ready to clear old grades from 2nd qtr. sheet. To do this, go to column and row where old grades begin (not in the formula columns!)
10. Drag from upper left to lower right all the grades you want to clear. (Not the formula columns!)
11. Go to Edit menu, scroll down to "Clear" and across to the right "All"; release.
12. Save!
13. Now you should have Qtr.1 and Qtr.2. Toggle from one to the other!
Return to Teacher Tech
Return to top
