Creating a Grade Book in Excel

Andrea Peach, Georgetown College

 

Prerequsites:  You need Microsoft Office 2000 installed on your computer. If you don’t know how to do this, call the Help desk.

 

Handout from class -- http://www.microsoft.com/education/print.asp?ctype=1&xml=/tutorial/classroom/o2k/excel&xsl=classroom

1.    Planning – before you begin to create your grade book, you need to do some planning…

Look at your syllabus – figure out how you would compute the grades on paper.

Look at old gradebooks – how did you compute grades in the past?

2.    Some Excel ‘Terms’

Microsoft Excel 2000 uses some of the same tools, commands and concepts that are used in most word processors, including Word 2000 and WordPerfect 8. However, there are some terms that are used by Excel that are not used (or are not commonly used) in WP programs. For our discussion today, they are…

 

Workbook

When you open Excel or when you click on the NEW tool (or File/New), you will actually be creating a WORKBOOK. This is essentially a group of SPREADSHEETS or WORKSHEETS. It is like having a new 5 subject spiral notebook that is full of empty pages. As each tab on your spiral notebook represents a new section, each TAB on the bottom of an Excel document represents another section of the WORKBOOK.

 

Spreadsheet/Worksheet

The spreadsheet (sometimes called a worksheet) is the ‘metaphor’ of Excel. It is the page that you see when you start up the program. This is where you type in your data.

 

Cell

The cell is the smallest unit on the spreadsheet. It is the intersection of a row and a column and is designated by the column and row coordinates, e.g. B11.

 

Formula Bar

The area above the worksheet that you type in headers, numbers, and formulas

Creating a new grade book (step by step)

  1. Open Excel.
  2. Click on the top right cell (A1) and type in your first header (for example, name) Continue moving to cells on the top rows and typing headers.
  3. Click on cell B1 (or the first cell under your header). Type in a line of sample data to use for testing purposes. Remember that the tab key will take you to the next cell!
  4. Starting with cell B3 (Test1), select the remaining cells on that line. Then, click the % tool on the toolbar (or right-click, choose Format Cells, and choose the percentage).
  5. On paper, figure out what formula you will use to figure out your grade. For example:

 

To figure this grade, I would write down on a piece of paper that my formula is ((Test Avg *  20%) + (Final * 20%) + (Paper * 30%) + (Assn Avg *20%))

 

  1. Create this formula…
    1. click on the first cell that you need to figure (for example, I need to figure my test and assignment averages before I can figure my final grade)

                                                               i.      to average a group of cells –

1.        click on the formula entry space (where you typed in your headers).

2.       Either click the '=' sign and choose average or  type “= average(“

3.        click on the first cell to average (in my case, C2)

4.        click on the last cell to average (in my case, D2)

5.        type in “)” and hit enter.

6.        THIS SHOULD LOOK LIKE: =average(C2:D2)

7.        If you got an answer in the field that seems logical, click on the field again (in my case E2) and hit the copy tool (or right mouse click and select copy). Then, select a bunch of other cells below this cell (hold down your left mouse button and drag down) and then hit the paste button.

                                                              ii.      repeat these steps for each average needed.

    1. click on the cell for the first person that will hold the final grade (in my case, B2)
    2. click on the formula entry field (same place that you typed in the headers)
    3. type ‘=’ and start with your formula. For example, I would do the following for my example…. =((E2*20%)+(F2*30%)+(G2*30%)+(J2*20%))

 and hit enter

    1. Now, you need to format the grade field so your final grade is a percentage. To do this, click on the grade field, right mouse click, and select Format. Choose the percentage option and click enter.
    2. If you got an answer in the final grade field that seems logical, click on the final grade field (in my case B2) and hit the copy tool (or right mouse click and select copy). Then, select a bunch of other cells below this cell (hold down your left mouse button and drag down) and then hit the paste button.

SAVE, SAVE, SAVE!!!!

EXTRA CREDIT

Let's say you give 3 assignments  in a semester and you want to drop the lowest grade. Here is how you do it....

1. Create columns that will hold the assignment grades (for this discussion we will call them C2-E2), a column that will hold the total assignment points, and a column that will hold the assignment average.

2.  Click on the total points cell (let's say it is C5). Click on the formula bar and type the following formula: =sum(C2:E2)-min(C2:E2) . This will add the three columns and subtract from the total the lowest score. 

3.  Now, we will create the average. In the Average cell, type: =C5/2 (remember we dropped the lowest score so we only have 2 scores to average). If you really want to get fancy, you can further adjust this formula by typing =C5/(count(C2:E2)-1). This way, you can add an assignment to the spreadsheet without affecting the formula, as long as you insert the assignment between C2 and E2.