EducationPedagogySecondary EducationTechnologyUncategorized

Use Microsoft Excel to be More Data Driven (Part I)

As a teacher, one of my tasks is to assess how well students are comprehending the material I am teaching. There are many ways to assess student comprehension, but this post will focus on using Microsoft Excel to extract meaningful information from formal assessment data.  I am defining a formal assessment as a task that is individually completed and is summative (think traditional test or quiz).

Most are familiar with using some measure of central tendency to evaluate assessment data.  Typically, this is using the mean or the median to measure how well a class performs on an assessment (and by proxy, how well they comprehended the material).  This is useful but limited.  In addition, one would like to know the variance of the data to see how spread the distribution of performances is.  This will help the teacher to drill down and identify individuals that need additional interventions.

In this space, I want to walk you through how I use Excel to give me a visualization of average and variance.  I show the distributions to each class when discussing assessment results.

Setting up your tables

There are two tables we want to make.  One will be a tidy data set (See Hadley Wickham’s Paper on Tidy Data), where each column will contain only 1 variable, and each row will represent 1 observation (an assessment score).  The second table will be used to visualize the data.

We want Sheet 1 of our spreadsheet to contain columns that describe a single variable, and rows that describe a single observation (an assessment score).  For me, those variables are:
Name (Last name first, separated by a space.  If we want to export this data set as a .csv, commas will cause a headache.)

  • ID (For anonymity)
  • Class (Grade Level of the Student, e.g. “9, 10, 11, or 12”)
  • Assessment Type (Type of assessment, e.g. “Quiz,” “Test,” “Informal,” “Formal,” etc.)
  • Assesment Unit (A number corresponding to the Unit to which the assessment corresponds.  For multiple assessments within a unit, I use decimals, e.g. “6.1, 6.2, etc.”)
  • Raw Score (Number of raw points earned)
  • Curve (for shifting the whole distribution to the right, and controlling for my failures in teaching material or making the test too hard)
  • Curved Score (A calculated value Raw Score + Curve)
  • Max Points (for calculating the percentage)
  • Raw Percentage (a calculate value Curved Score / Max Points)
  • Adjusted Percentage (I grade my exams on the college scale – 10 pts, but I will need to adjust to my district’s scale – 7 pts)
  • Weight (For entry in the gradebook.  This is how many points out of the entire term this particular assessment is worth.  Depending on how your grading scheme works, this and the Gradebook variable may be unnecessary or require modification.)
  • Gradebook (The actual value that will be entered into the gradebook.  This is calculated from the product of Adjusted Percentage and Weight)

Each variable gets a column.

Excel Spreadsheet Example

Variables as Column Headers

Fill in the Name, ID, and Class fields with your class roster.  I’ve created dummy names for anonymity.  If your district doesn’t assign IDs, you can create one.  These fields will get copy-pasted below every time a new assessment’s data is entered.  This allows you to quickly sort by student and see all of their assessment scores at once (and even visualize them).

Excel Example with Roster Added

Roster and Demographics

Now we want to freeze the top row, so that when we scroll down, the variable names are always visible.  Additionally, we want to be able to sort our data by each column.

Under the View Ribbon -> Freeze Panes -> Freeze Top Row

Excel Example Of Frozen Row

Freeze Top Row

You will see that when you scroll, you can always see what each column describes.

Now, the filter.  Under the Data Ribbon -> Filter.  You should see each variable, now has a drop down sorting button.

Excel example of Filtering

Filter allows for sorting

Enter some assessment data in the Raw Score field.  Set the Assessment type, unit and Max points for the assessment in the top observation.  For my example, this is a 10 point quiz, titled “Quiz 1.”

Excel Example, Data input

Input Data

Excel Functions

The next step, we will only have to do once, and then for every future assessment, we will only need to enter the assessment scores.

We want to let Excel do as much of the work for us as we can, so let’s set-up our first entry, and then we can copy-paste down.  Excel cells can be loaded with functions that will do some computation based on the values of other cells.  There are many built in functions, but ours will be simple, and we will define them ourselves.  Function cells always start with “=”

Set the Curve to 0.  We can change this later, if we want.

Now, in the first Curved Score cell (H2), we will define our first function that will add the Raw Score and the Curve.  Select cell H2 and type “=F2 + G2”.  You don’t have to type in the cell coordinates, instead you can use your mouse.  So you can type “=”, then select cell F2 with your mouse.  You will see that the cell will have a colored border, and that “F2” will be the same color.  This color coordination helps you to see how your functions are being calculated at a glance.  You can continue by typing “+” and selecting cell G2 with your mouse.  Press “enter” and you will see that nothing changed.  Try changing the value of Curve, and see what happens.

Excel Example of Function

First Function to Compute Curved Score

Now we want to calculate the Raw Percentage.  In cell J2 type “=H2 / I2”.  This will calculate the percentage as a decimal.  You can have it reported as a percentage by right clicking on the cell -> format cells… -> Under category, select “Percentage”.  You can also change the number of digits reported after the decimal, the default is 2.  This is nice, because Excel is still storing your percentage as a decimal, which will make calculations more straight-forward, but it is easier for you, the human, to read.

Excel example using a function to compute raw percentage

Function to Compute Raw Percentage

Now, if you don’t need to adjust the grading scale, you can skip the next step.  I, however, like to grade on the college scale (A -90, B -80, C -70, D -60), because it is more intuitive than some odd base-7ish type system (which my district uses).  So, for me this means if a student scores a 90, she gets an A, but I need to report this as a 93 in my gradebook.  Similarly, if a student scores a 60, she passes with a D, but I need to report this as a 70 in my gradebook.  You can see that it is not as simple as just adding some constant to the raw percentage.  I’m not going to get into the dirty math here, but the nice thing about computers, is you can prove that it works by entering some test cases when we’re done.  In cell K2, enter the formula “=1 – ((1 – J2)*(7.5 / 10))”.  The only variable is “J2,” which is the Raw Percentage.  Try entering 9, 8, 7, and 6 into the raw score to see what the reported Adjusted Percentages are.  You should see an A is 93, B 85, C 77, and D 70.  There is a little fudgery here because someone long ago decided that A – C should be a 7 point range each, but a D only has a 6 point range.  This is some weird compromise between a 7 point scale, but wanting a passing score to end in 0 (i.e. 70).  This is further complicated, because the top range of the 10-point scale allows for a 100, rather than capping at 99.

I will also format the cell to report as a percentage

Anyway, you should see this:

Excel example function to adjust 7 point scale to 10 point scale

Adjust to a 10 point scale

Home stretch!  Now we just need to take that Adjusted Percentage and return it to a value to be entered into the gradebook, and copy-paste down.  Manually enter a value in the Weight field.  For me a Quiz is worth twice what a problem set would be worth.  So the weight for a problem set would be 10, and for a Quiz, 20.  I could’ve easily made this 1 and 2 (or 0.5 and 1), but the 10 points makes sense to students.  Finally, in the Gradebook cell (M2), we need to enter our last formula.  “= K2 * L2”.  This will multiply the Adjusted Percentage by the Weight, and give you how many points out of the Weight value the percentage represents.

Excel Example of Function converting percentage back to a weighted value

Convert Adjusted Percentage back to a value for input into a gradebook

You can avoid the Weight/Gradebook fields by just setting the Max Points for the assignment to the Weighted value in the Gradebook.  In my example, this means that the Max Points would have been 20 instead of 10.  However, I like the flexibility this provides.  For instance, I can make a quiz out of 35 points, because maybe some questions have 5 parts, and others have only 2, and I can just give every “piece” of a question 1 point, and do the adjusting to 20 points later.  Yay math!

Finally, we need to copy all of our hard work.  This is where the magic of Excel comes in.  All of the computations will be done quickly and automatically from here on out.  There are several ways to copy-paste in Excel.  You can drag the box in the bottom right of a cell down with the mouse, or just double-click on the box in the bottom right of the cell, or use Ctrl-C, Ctrl-V.  Here are those three methods for the first three columns:

Excel Example of Copy-Paste method

Copy-Paste by dragging with mouse

Excel Example of Copy-Paste method

Copy-Paste by Double Clicking

Excel method for Copy-Paste

Copy-Paste Ctrl-C, Ctrl-V

And our final product:

All Rows and Columns filled

Final Product

Here’s what it will look like after a second assessment is entered.  This time copy-paste the Name, ID, and Class fields, and Input the Assessment type, Assessment unit, Raw Score, Curve, Max Points, and Weight.  Finally, copy-paste down the remaining columns.

Excel Example of filled tables

Two Assessments Entered

Here is an example of the types of things you can do after having several assessments entered:

Filter by Student, then Assessment type, then by descending score:

Excel Example of filtering at work

Filtered by Student, Assessment Type, and Descending Score

Part 2:

Next week, I will show you the fun stuff.  On a second sheet, we will create a table for visualizing the data.

Previous post

Free community college, NCLB changes, Israeli science books sans ladyparts, Young Skeptics, and more: Required Readings, 1.11.15

Next post

Use Microsoft Excel to be More Data Driven (Part II)



Keith is a high school Chemistry and Biology teacher for an urban public school district in an area of the country where pants are called “britches.” Though he has a degree in Percussion Performance, he teaches science because he thinks that a well honed skeptical toolbox is necessary for a more informed citizenry and a more just and prosperous society. When he’s not in the classroom, he spends all his time with his wife and two children, attempting to become the first person in the world to be both a perfect husband and father.

No Comment

Leave a reply