Creating a Grade Book in Excel

Tara Juliano

Spreadsheets are a great tool for creating grade books. You will be able to create a customized grade book with weighted scores and averages.

Grade book Layout

1. Start by creating a lay out for the grade book. Click on the box you want your text in and start typing.

Figure A

**Note: Pressing enter in Excel will take you to the next cell. To make your cell double-spaced put the mouse over the number of the row until you see . Then click, hold, and drag the line down to the width you want your row.

In Figure A, I created a title, Science, Marking Period 1- 2006, so that we could identify the grade book. Next, I added labels for various columns.

Grade book Formulas

To create a formula to calculate the average of the grades:

1. Start by clicking in the cell where you want your average to appear. In Figure A, this would be cell L6.

**Note: You must be in the cell where you want your average to appear.

2. Select function from the Insert menu. Excel will display the

Insert Function box (see Figure B). Select statistical from the pull down menu of categories. Select Average from the function box. Then, click OK.

**Note: Sometimes the Average function will appear when you click on the most recently used category.

Figure B

3. When you see the Average box displayed (Figure C), highlight

the cells you want to be averaged.

Figure C

**Note: The (fx) button located below your main toolbar will also bring up the Function Arguments box in Figure C.

**Note: This can also be done in reverse. Highlight the cells you want to average, then click on the Insert Function (fx) and choose the function desired, in this case, it is the Average function.

4. Use the mouse to select all the cells that will have grades in

row 6 (See Figure D1 and D2). Notice how the range of cells is

displayed in the text box entry and in the cell L6.

Figure D1

Range

Of

Cells

Figure D2

Range of Cells

5. Once you have selected the cells, click OK. The average will then appear in cell L6.

6. You could repeat these steps for each student, but there is an

easier way to duplicate the formula for each student. The grade

book will have 5 students, so select cells L6 through L10 by

dragging the mouse. Select Fill from the Edit menu, the Down

from the Fill menu (see Figure E). Excel creates a formula in

each cell.

**Note: SHORTCUT: After computing the average in cell L6, place the mouse over the bottom right corner of the cell and a + sign will appear. Click the + and drag down to L10 or to the end of the student list. Excel will automatically create the Average formula for each cell.

Figure E

7. Now, add students and data to the cells. Figure F shows a

completed grade book.

Figure F

8. Notice that the averages are displayed with five decimal points.

You could have any amount of decimal points, but for this grade book I want whole numbers. To change the formatting of the numbers, highlight the cells. Select Cells from the Format menu. When the Format Cells box is displayed, select Number from the list on the left. Then select “0” as the number of decimal places to display (see Figure G).

Figure G

9. To create a weighted average, you will first have to average

each category separately. This will require you to insert columns. To do so, click in the column to the right of where you want the new column to be. From the Insert menu click Columns. Label these columns for each grading element. (See Figure H)

Figure H

**Note: To average each grading element, use the (fx) and repeat as an Average function. (See Figures B-D2).

10. Select the cell for the first student’s grade, now cell O6. For

this example, Classwork/Homework will be weighted 20%,

Quizzes will be 30%, and Tests will be the last 50% of the

grade.

11. Click on the fx and choose SUM. Click on the Homework Average Column and then type in on the formula bar the percent you would like to multiply. For example, H6 will be 20% of the final grade so we want to multiply H6*.20 (See Figure I). Press OK and the grade will appear in the Average cell you already have highlighted. You can now use the Fill function or the shortcut to find weighted averages for each remaining student.

**Note: The multiply function is inserted by pressing shift 8. In order to get the averages all together insert the + key between each grading element.

Figure I

Grade book Formatting

To change the formatting, select Cells from the Format menu.

1. To change the Alignment of the column titles, highlight the title of each column. Select Cells from the Format menu. Click on Alignment. In the orientation click the angle which you want the text to appear.

2. To change the look of the text select the cells you want to change. Select Cells from the Format menu. Click on the Font tab. Here you will be able to change the font, font style, font size, color, etc. Choose your options and Click OK.

3. To make the whole column one color, highlight the whole column by clicking the letter at the top of the column. Select Cells from the Format menu. Click on the Patterns and select a color.

4. To insert divider columns between each grading element, insert columns between each area we’ve created. For example, insert a column after each average column. To color that, highlight the column, select Cells from the Format menu, click on the Patterns tab and select a color.

5. To make a grade of a certain value standout, select Conditional Formatting from the Format menu. Select Cell Value Is, Less than, 70. The select Format, Font, Color, and red. This will make every grade you enter, less than a 70, red. (See Figure J).

Figure J

6. If you’ve completed all of the above formatting functions, your grade book will look like Figure K.

Figure K