Guide to Learning the Tricks

The Example Gradebook is an Excel 2007 workbook for a fake math class made up of popular baby names. The data is randomly generated to ensure lots of good grades and the grading policies are purposely a little funky in order to show a variety of things that sometimes come up with gradebooks.

Getting Started

To get a sense for the kinds of things in the example gradebook, Download the Gradebook and Save it someplace. Then, read the "Guide" sheet in the workbook and peruse the various sheets by clicking on the tabs along the bottom. All of the grade calculations are linked to the "rawScores" sheet, so that any changes made in the rawScores sheet will automatically cause changes throughout the workbook.

Basics of Cell Formulas

If you click on a cell, any formulas used in that cell will show up in the formula bar. For example, the cell X3 (highlighed) in the image was calculated using the formula below.

=(SUM(S3:W3)-SMALL(S3:W3,1))/120

This means: Find the sum of the numbers in cells S3 to W3, subtract the smallest of these numbers, and then divide by 120

Note: All cell formulas start with an equals sign.. This is how Excel knows it is a calculation and not text. The code means "

Once you start a formula in a cell, you can refer to a cell or range of cells by clicking on the cells. The example on the right shows how you can determine which numbers to "SUM" by just selecting the cells with your mouse.

Trick 1: Linking Grade Calculations to Raw Scores

Doing grade calculations sometimes requires more than one worksheet. In those cases, it's useful to put the raw scores in a single sheet and link other sheets to that data by referring to the raw scores sheet when necessary. That way, you only have to change a score once and all the other calculations and graphs will automatically update.

may want to make sure you don't accidentally delete the "rawScores." To protect or unprotect a worksheet, go to the Review tab, click Protect Sheet and choose the protection options you want.

Protecting and Unprotecting the Raw Scores

Because changes in the "rawScores" sheet influence the entire gradebook, it is protected from editing. You can copy and paste the data, but can't change anything. To make changes, go to the Review tab and choose Unprotect the Sheet. Otherwise, a warning message will stop you from editing cells.

Trick 2: Freeze Panes or Hide Columns

Sometimes spreadsheets get confusing to read because of all the data. The good news is there are a couple easy ways to limit what you see.

Freeze Panes

If you click on a cell, you can "freeze" the rows to the left of the cell and the columns above the cell by going to the View tab and selecting Freeze Panes. Then, when you scroll, the frozen portions will not move. This is done in the "rawScores" sheet, for example.

Hide/Unhide Columns or Rows

Several of the columns in the "FullGrades" tab are hidden. The good thing about a hidden column (or row) is that it is "out of sight" but can still be used for calculations. To unhide a column, you'll need to select the columns before and after the hidden columns and then right click and choose "Unhide." To Hide a column, select the column heading (e.g., click on "C"), right-click, and choose "Hide".

Trick 3: Drop Lowest Scores (SMALL)

To drop a lowest score or scores from an average calculation, you can use the SMALL function. The code below shows the calculation of a homework average where the 2 lowest scores are subtracted from the total.

Note: If your assignments have different points and you'd like to drop the assignment with the lowest percentage, it helps to scale the assignments so they all the same number of points (see column G in "FullGrades" for example).

Trick 4: Calculate Weighted Averages

An example of a weighted average to calculate grades is given in cell AW3 in the "FullGrades" worksheet. In the fake example, grades are determine according to a weighted scheme (tests are worth 45% for example).

A couple of questions to think about.

• What do each of the pieces in the weighted average mean?
• How would you change the code if the exams were 40% of the grade and the projects were 25%?

Trick 5: Calculate Letter Grades (Nested IF Statements)

An example of a nested IF statement that could be used to calculate letter grades is given in the cell AX3 in the "FullGrades" worksheet. The basic structure is to put a test (e.g., "AW3>.895"), and if it's true, set a letter grade. If it's not true, test a different value, and so on.

=IF(AW3>=0.895,"A",IF(AW3>=0.795,"B",IF(AW3>=0.695,"C",IF(AW3>=0.595,"D","F"))))
(here AW is the column with the overall percentages)

A couple of questions to think about.

• Why are there 4 IF statements in the above code?
• How would you modify the code to produce +/- grades?

Trick 6: Format Cells

Formatting cells can be a good way to avoid having to scale values. For example, instead of multiplying the HW average by 100, the numbers between 0 and 1 can be formatted as "percentages" in Excel using the Number section of the Home tab.

The arrow buttons in the Number dialogue can be used to increase or decrease the number of decimals shown. See below.

Trick 7: Sort Data

To sort data, first select all the data, then go to the Data tab and hit the big Sort button. You can sort by a set of criteria (e.g., by class, then by gender, then by name). Give it a try.

Trick 8: Use Conditional Formatting

Two of the columns in the "FullGrades" worksheet have the values colored depending on what the cells equal.

To see how the conditional formatting rules work, click on a cell in the columns with conditional formating (e.g., cell AW3), then click on the Home tab, select Conditional Formatting, choose Manage Rules, and then Edit Rule.

Trick 9: Create Frequency Tables and Bar Charts (SUMPRODUCT)

The BarGraphs tab uses the "SUMPRODUCT" function to calculate frequency tables of things like gender*grade interactions..

The formulas are a little weird, but worth looking at. The code below means to count the number of rows (between row 3 and row 122) which satisfy both the gender is male and the grade is A.

 =SUMPRODUCT(--($B$3:$B$122=H$2),--($E$3:$E$122=$G3))

Note: the dollar signs are inserted so that the formulas can be copied-and-pasted to determine the number of people whose gender is male and whose grade was B, etc.)

Trick 10: Histograms and Pie Charts (COUNTIF)

Check out the HistPie worksheet and look at how the formulas are set-up.

The "HistPie" sheet uses the "COUNTIF" function to calculate frequency tables of grades for several assessments.

One way to construct these frequency tables is to calculate the number of cells which meet some criteria. The code below counts the number of Exam 1 scores that are above 59.5% and then subtracts the number which are above 69.5%... thus giving the number of exam 1 grades in the "D" range.

 =COUNTIF(FullGrades!Y$3:Y$122,">=59.5")-COUNTIF(FullGrades!Y$3:Y$122,">=69.5")Once you've created a frequency table, creating a bar chart is relatively easy. Choose Insert tab, and pick the Column chart option.

Bonus Trick: Create Individual Progress Reports (Mail Merge)

Microsoft Word 2007 has a cool tool to build "form letters" from spreadsheet data. It's fairly easy to use this Mail Merge option to create individualized progress reports. The process takes about 20 minutes:

1. Type your progress report in Word as a form letter. That is, create the progress report with blanks for the person's name, scores, and overall grades.
2. From the Mailings tab, choose Start Mail Merge and then Step by Step Mail Merge Wizard.
3. In Step 1, choose "Letters", and in Step 2 choose "Use Existing Document."
4. In Step 3, choose "Select a different list..." and browse to find your gradebook. Follow the steps to pick which data you'll be using to create the grade report.
5. In Step 4, you'll use the columns from your gradebook to insert fields into your letter. For example, column A has the individual names in it, so you'll add that into the appropriate place of your progress report by choosing More items and selecting the name column. (This step can take awhile.)

Here's an Example Progress Report Word document to go along with a Example Gradesheet file. The result is a Stack of Progress Reports.