Embedded Files
Now that we’re done “fixing” the error in our midterm data, we want to start looking at our data – to get a feel for it – or to answer a specific question we have.
Suppose we want to look at the top scorers. All of these students (in rows) are jumbled up in random order (in our case, this often comes from the order in which we scanned the answer sheets) – but we’d like it to be re-ordered so the first row has the highest scoring student, the next row the second highest scoring, etc.
To do so, we can use AutoFilter. We would want to press the AutoFilter button to start allowing filters.
Boom. Suddenly there are arrows around each of the top columns. Now let’s sort by Current Total.
Click on the Current Total Arrow, and then you’ll see two options, Sort A->Z and sort Z-> A. Even though these are all numbers, we will follow the logic that A-> Z means smallest to largest, and Z-> A will mean largest to smallest. We want to see who has the highest grade in the class, so let’s sort Z -> A. Your result should be like this.
<Click on me to make me bigger>
Note: there is another way you can sort data without using AutoFilters (but we think AutoFilters are easier). However, if you turn off filtering (click on the filter icon again) then if you wanted to swap and sort Current Total from smallest to largest, you can click on that column, then go up to the Data menu and you should have the option to sort the sheet by column P (again, with two options A->Z or Z->A (in this case, again, we remember that A->Z will be smallest to largest).
Note: below those two options, you also have the ability just to sort that range. If you were to chose that – it would only move (sort) the data in that SPECIFIC column – and not move the data corresponding to that in that row. This would cause a MAJOR problem in this case! You would be changing the score associated with each student!!! Be very careful when you think you want to sort only the range selected. Do you really mean it? Probably only in the case (not ours here) where a “row” doesn't represent a single “entity” – the data on a row isn’t related to each other.
But wait! You noticed that Elizabeth has a 27 on her midterm! There was no extra credit but how is that possible? What about other students? Are there any more errors – if we say it’s an error for anyone to get more than the maximum score of 26?
Sure we can use Find and Replace again, but another option is to use filter to display any erroneous values – then we could just replace them ourselves or use find and replace.
We can just press the little triangle on top of midterm column and look below the sort options to “Filter”. What we see is a list of each unique value it found in this column. Right now, it has each value selected (with a checkmark) to be included in the filter.
Start by clearing that, then scroll down the filter values and you’ll see scores of 27, 28, 29. Uh oh! That’s not good! Click on those three (so they are checked) and click OK.
Once you’ve pressed OK, your screen should look like this.
<Click on me to make me bigger>
5 students have erroneous Midterm scores, one of the TAs must be really bad at entering grades (Nah! TAs work very hard – but they are human! We use checks just like this because computers can help us humans to do a better job.) Looking through the actual exams (we did that for you) we found out that Elizabeth and Jenner both received 26 and that was just a typo, Angela received 16, Victoria received 18 and Jacky received 25. Go ahead and make those corrections.
Once we have corrected them, we need to unfilter the data again. To do so, click on the arrow next to the midterm, and then press Select All. Once every single result has been checked, press OK.
Look. Due to the changes of a few scores, the grade sheet is now not in order again.
Page updated
Report abuse