Create a Gradebook on Microsoft Excel

Learning how to create a grade book sheet on Microsoft Excel is a great tool to use. It provides a spreadsheet of data and formulas that will minimize the time and effort spent on recording and calculating grades using old fashioned ways. The following is a fairly detailed description on the steps that will promise learning this skill and adopting a tool that will be useful in future tasks of data analyzing. You only need basic knowledge on how to operate Windows 7, XP or Vista to use this article. You need not necessarily be familiar with Microsoft Excel.

Steps

  1. Open Microsoft Excel.
  2. Enter class information onto the Excel sheet
  3. Choose a Grade book Layout
  4. Create formulas

Open Microsoft Excel

  1. On your home screen, press "Start", then go to "All programs"
  2. Left click on "All programs",Open Microsoft Excel
  3. Find "Microsoft Office" from the list, and left click.
  4. Press "Microsoft Excel"

    • For easier access to Microsoft Excel, click and drag the Excel icon, from step 3, to your desktop.

Enter class information onto the Excel sheet

For the purpose of organization, you should always name the sheet you are creating and include general information about the class (i.e. Instructor’s name, name of the class, and/or the meeting times). This step is critical when you need to print the document, make copies and share them. It is very helpful in identifying the grade-book table presented correctly and efficiently.

  1. Name the Grade-book sheet
    • Double click on "Sheet1" at the bottom of the Excel window, "Sheet1" is now highlighted
    • Type a name for the sheet, for example: First Hour Grades
    • Press Enter

  2. Insert Class Information

    • Click cell A1 to select it
    • Type teacher’s name.
    • Press the down key to select cell A2
    • Type the class name, for example: Social Science Class
    • Press the down key to select cell A3
    • Type class meeting times
    • Press the down key to select A4
    • Enter the term, for example: Fall 2012
    • Press "Enter" twice to go to cell A6
    • The "name box" at the top of the sheet shows what cell is selected.

Choose a Grade book Layout

  1. Enter The Names of The Students
    • It’s important to choose a layout that is most convenient to you. Knowing the type of entries you are going to make will help identify the different columns that you will need. You will need a column for every assignment graded, in addition to a column for students’ names, the total, the average and the final grade.
    • For this data you will need three columns: First Name, Last Name and a column for the number of students.
    • Creating a Sequence Column of Numbers
      • With cell A6 being selected, type 1
      • Press the down key
      • Type the number 2
      • Hover over cell A6 until the cursor is shaped as a
      • Click and drag the cursor from Cell A6 to A7,now both cells are highlighted with a box around them
      • Hover over the lower right corner of the box until the cursor becomes a plus +(this is called the fill handle)
      • Click and drag until your end number is reached.

    • Type the name of the columns: Select cell B5, Type the column name First Name,Press tab from the keyboard, Type Last Name, Enter the names of the students to the corresponding columns.
  2. Create remaining columns (follow the steps as shown before): Type the remaining of the columns such as Homework 1,Homework 2, Quiz 1, Quiz 2, Exam, Total, Average, and Final Grade. Use the tab key to move from a column cell to the next.

    • To have the names displayed in alphabetical order, Under the home tab, click on the "Sort & Filter" icon, choose A to Z.

Create formulas

Excel provides a list of many functions that can be used in calculating grades. The first function is the sum function. We will use the sum function to find the total of the students’ grades. Secondly, we will use the average function that will translate the total to a percentage.

  1. The Total of The Students’ Grades
    1. Select cell I 6 (the cell directly below the "Total" cell)
    2. Under the Formulas menu, select Auto Sum
    3. Click and drag cells D6 through H6 across the row.
    4. Press Enter
    5. To copy the formula to the entire Total column, Click and drag the fill handle until you reach cell I15. (this will copy the function to each row, calculating the total grades for each student)

  2. The average of the grades
    To find the average of the grades for each student, we will divide the total found in the "total" column by the maximum total possible. (in this example, we will assume the maximum total possible of the grades is 500)
    1. Select cell J6 (the cell directly below the "average" cell)
    2. Click on the Formula bar to type
    3. Type =16/500
    4. Press Enter

    5. From cell J6, click and drag the fill handle to the entire average column, until cell J15
    6. To format the average in a percentage form, select the column J6 to J15
    7. Right click on the selected column
    8. Choose Format Cells, a dialog box will appear
      • From the Number tab, click on the Percentage category
    9. Change the number of decimal places as you prefer
    10. Click Ok.

  3. Translate The Calculated Average Grades to Final Letter Grades
    Excel allows us to add a function that automatically calculates a grade based their averages in column J. To perform this function we will need a key, which is simply table of letter grades and corresponding numbers. We will use a sample table from Excel.
    1. Create The Key Table
    2. Select cell M7, we will start typing the table here
      • Type "Average", this will be the first column
      • Press the tab key
      • Type "Grades"
      • Under "Average" type your grading scale scores
      • Under the "Grades" column, type the corresponding letter grade to each score

  4. Type The Formula. The function required to return a letter grade is the function, and is called a nested function. It follows the formula:
    _value,table_array,column_index_number,[range_lookup])
    • Select cell K6
    • Start typing the formula =VLOOKUP(J6,$M$18:$N$22,2,TRUE)
      • Explanation: after the brackets, type the cell that contains the final score of the student which is in this example cell J6. The second part of the formula is automatically included by selecting the key table; press F4 from the keyboard to insert the dollar signs that will lock the range selected. The third part is the column number from the table that contains the letter grades. True stands for approximate match with the values of the column, false will result in exact matches.
    • Press Enter
    • Copy the formula down to the entire column until cell K15 by clicking and dragging the fill handle from cell K6.
    • Repeating this process will allow you to calculate grades for other classes in the future.

Tips

  • Always give your grade-book a title, by clicking on the "file" tab, choose "Save As", in the Save As window, choose a location and type a name for your document. Press "Save" when ready to save.
  • Refer to Excel's extensive "Help" menu when in need. It offers tools on creating statistics of data.
  • To find out what operating system your PC has, press "Start", right click on "Computer", scroll down and click on "Properties", a system dialog box will appear with basic information about your computer.
  • For easier access to Microsoft Excel, click and drag the Excel icon, from step 3, to your desktop.
  • The name box at the top if the sheet shows what cell is selected.

Warnings

  • Be sure to confirm that the equations you created for your grade book are calculating correctly.
  • Make sure you save your progress throughout to prevent losing information as you work.
  • Always save a backup of your grade book and maintain hard copies.

Things You'll Need

  • Computer with Windows 7, XP, or Vista
  • Microsoft Office Excel 2010

Related Articles

Retrieved from "https://kipkis.com/index.php?title=Create_a_Gradebook_on_Microsoft_Excel&oldid=273413"