As many of our customers know, my favorite part of SchoolStatus is our reporting engine. I’m a big fan of manipulating and smashing data together to see cool correlations and timely trends. At SchoolStatus, we pull all your data together to do everything we can to make it accessible and easy to see. However, even with all of the data at your fingertips, sometimes you just have to download and manipulate a CSV or Excel file (BTW, for those who don’t know, CSV stands for Comma Separated Value).
For me, I am one of those data nerds who really like Excel. I’ll build a Spreadsheet, get really excited about it, and show my lovely wife how amazing it is, and just get an eye roll in response (Pro-tip: 10 pm is not the time to show off your mad Excel skillz to your significant other).
Anyway, throughout my journeys assisting educators with data, I’ve noticed a strange phenomenon that there are knowledge gaps regarding general Excel Functions that I am astounded people aren’t using! With that being said, if you ever need to turn a Smart Tag, Student Group, Assessment Student Detail List, Accountability File, or Report into an Excel Spreadsheet, here are the top 5 Excel Functions that will save your life and so much time. (If you don’t want/need the full explanation and just a short explanation will do, click to the bottom for a TL;DR)
[See Also: Teacher Uses Picture Messaging for Classroom Behavior Improvement]
1) COUNTIF(range,criteria)
OK, I’m making an assumption here. I’m assuming that you probably already know about Excel’s COUNT function and COUNTA function (which lets you count the number of cells in a column or array [wait, what’s an array in excel?! Don’t panic, it isn’t important to our conversation at this point]). OK then, what does COUNTIF do? Well, what if you exported your Assessment Student Detail list and you have every student at your district but wanted to only find the total number of 9th graders in the list, then that’s what you would use COUNTIF for. COUNTIF looks like this in English:
=COUNTIF(A, B)
A: The column where I have the data I want to count
B: The data I’m counting
Check out the screenshot below; I mentioned previously that perhaps you wanted to know how many 9th graders took a state assessment. Notice, my total is 3 because I have only 3 9th graders in my column D1:D11 (that means cells 1 through 11 in row D). Obviously, in this example it isn’t that helpful because I have very few kids, but what if I had 10,000 kids in my district? That would be difficult to count by hand!
A better way to design the formula would be to reference another cell that you can then change instead of explicitly referencing ‘9’ as the grade level you’re interested in. That way you can change the grade without needing to alter the function. In this case, G3 is where I’m changing the grade.
That’s pretty much it; super simple and super helpful.
2) SUMIF(range,criteria,sum_range)
Other times, we want to take it a step further. We want to add up all of the entries in a column based on a specific data point in another column. For example, let's say we wanted to find the sum of the scale score of all the 9th graders above. This is in 2 separate columns! How do I do that without filtering, summing, and then saving that somewhere else? Again, I’m making an assumption that you are already familiar with the SUM function in excel. So let’s take a look at SUMIF in English:
SUMIF(A,B,C)
A: The column or row where “if” data exists (for example in our case, the grade level)
B: What the data is I’m interested in using to determine the values to sum (example in our case, 9)
C: The row I want to add together (for example in our case, the scale score to add if the student is in 9th grade).
Checking out our same excel file from last time, let’s look at the picture now.
In the example above, I’m looking at Column range D1 through D11, then (because I’m excel savvy) I’m looking at my grade in the G3 cell just like we did in the example above, and finally, I’m telling excel to add the values together in the Scale Score column. Why would I do this for a scale score? Well, look what we have: We have the total number of 9th graders, we have their summed Scale scores, all we have to do is divide one from the other, and voila!! We get our average scale score!
P.S.: You can do the same thing in one step with AVERAGEIF.
3) VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
By far, this is probably one of the most useful excel functions that people end up using because it is super helpful and pretty easy to wrap your head around. What is this magical function? It stands for Vertical lookup and lets you look up data on one sheet and display it on another sheet based on specific criteria. Have you ever gotten your Scale Score data from one excel spreadsheet and then your benchmark data from another spreadsheet only to have to frantically pour over both trying to find the data point to copy over to the first spreadsheet and thought: “There has to be an easier way to consolidate this!”? Well, that’s where VLOOKUP comes in because that’s exactly what it does. First off, let’s break it down in English just like we did with the first two functions:
Vlookup(A,B,C,D)
A: What you want to match on your primary sheet/table with what exists on your “other” sheet/table
B: The “other” sheet/table
C: The column number on the “other” sheet/table of the data you want to display on your primary sheet
D: For our purposes, you want to always choose 0 (which is FALSE, which means that you want to match exactly. If you put a 1 or TRUE, you will look for approximate matches, which is more explanation than this blog can handle).
So let’s take our working example from before. In this case, I have another spreadsheet with a table on it that displays a list of students, their benchmark percentile scores, and a few other things. Perhaps it even has more data on it than I need! Well, not a problem, let’s see how vlookup can help us with this problem:
Here is an example of what vlookup is doing. We are displaying the number 32 as a percentile. How did we get this number? So, let’s take a look: C2 is referencing the student name Johnny 1 on our primary sheet, this is the data point I’m matching against. The next thing I’m looking at is this Benchmark data that is a table A1:D12… hold on… what is this?! OK, let me show it to you:
This is the “other” sheet that has the different data pieces on it. Notice that it is sorted by Percentile and so the names aren’t in order, oh no! Not to worry, all my vlookup is saying is “let's look at this sheet”. That wasn’t too bad, right? The next argument in the function is just the number 3. Remember in this case that means we are saying “On my primary sheet, I want to match the data from my ‘other’ sheet and then display what’s on the 3rd column”. I’ve helpfully labeled it Percentile as well to avoid confusion. Lastly, I put a 0 because I only want to display data on my primary sheet if I have an exact match.
NOTE: With vlookup, what you’re matching has to be in the first column of your selected table. In our example, I’m looking up the student name from my primary sheet, so the first column of my “other” sheet where I’m looking up the percentile has to be a list of student names. The last step is to copy that formula down into the remainder of my rows on my primary sheet and we are done!!
P.S.: You can do the same thing with rows by using HLOOKUP.
4) IFERROR(value,value_if_error)
You probably noticed that I have a few entries on my spreadsheet example that are displaying #N/A. This is because (and observant people will have already discovered) that I don’t have any percentiles for those student names in my “other” sheet. So when VLOOKUP went to fetch something, it panicked and threw up an error, and that looks bad if you have to present this to your board or your Superintendent. IFERROR resolves this by allowing you to display something else if there is an error. Let’s break it down:
Iferror(A,B)
A: What you want to display if there is NO error
B: What you want to display if there IS an error
This is going to be a little tricky, but if you’ve made it this far, I think you can handle it. In our example, what I want to display is the result from our vlookup, but if that result is an error, I want it to display “No Test”. We can do this with a nested function. A nested function uses another function as an argument within itself. This is very useful and can allow extreme flexibility. In our case, the vlookup function is being used as the argument to display something if there is no error. Take a look at the screenshot below if you’re a visual person:
There! Now all of my #N/A errors are displaying the words “No Test.” I simply used some text if something was an error, but you can also use another function, even another vlookup function that references a different sheet! I also have some nice metrics to consider like the Total number of students in a certain grade, the total sum of scale scores, and the average scale score.
5) INDEX/MATCH: INDEX(array,MATCH(lookup_value,lookup_array,match_type))
Index/Match is what VLOOKUP wants to be when it grows up. Don’t be afraid of this function, all we need to do is break it down. I promise it’ll be worth it. This function is actually two functions put together, the INDEX function and the MATCH function. I’m not going to get into what’s happening technically, but suffice it to say, this gives you the power of VLOOKUP, but with much greater flexibility and versatility.
VLOOKUP is great, but there are three restrictions. 1) Your match criteria HAS to be the first column on your sheet 2) you have to know the number of the column row (and it is frustrating when you’ve counted 27 columns and then lose track) and 3) you can only look up by column, you can’t look up by row. INDEX/MATCH resolves all of these deficiencies. Let’s break it down:
Index(A, match(B,C,D))
A: The column of the data I want to display from my “other” sheet
B: The data that I’m matching against in my primary sheet
C: The column that I want to match from my “other” sheet
D: 0… for FALSE, meaning that I want an exact match.
All right, let’s get back to our example. Let’s say we want the performance level but this data has the student ID and NOT the Student name so we are having to match by Student ID. No Problem! We’ve seen this before! But wait… for some strange reason the student ID isn’t at the beginning. Oh no! We can’t use VLOOKUP! But never fear for we have harnessed the power of Index Match! First of all, let’s take a look at what the other data looks like now.
Notice the Student ID isn’t at the front of the column so we can’t use VLOOKUP. Instead, we can use our Index Match function. In the figure below, the first parameter is column C on my “other” data sheet (listed above). This column has the data point that I’m interested in displaying.
Next, I use my match magic and tell excel to look in D2 (the student ID) on my primary sheet and then to look at column D on my “other sheet” to find the appropriate row remembering to use 0 to match exactly. This tells excel, “Hey, I want you to find the row where the student ID exists on both sheets”. Finally, Index is saying, “got it! I’ll display the data in my performance level column that is on the same row as the student ID that we matched.” And voila! We have successfully used Index Match.
All that remains is to copy it down the row (and to remember to use our IFERROR function to clean up the mess) and we are done!
P.S.: Because on the back end all INDEX is doing is displaying the value of the cell that occurs at the intersection of a Column and Row, if you switch the MATCH function to be the first argument that Index takes, you can do the same thing for rows (which is the replacement for HLOOKUP)!
BONUS Tip! (not a Function, but cool nonetheless)
Combo Boxes
While not a function, Combo Boxes are very useful drop downs that you can create in Excel that allow for some nifty user interface. To create a combo box, simply go to the Developer tab at the top, don’t worry you won’t be making a macro or programming in VBA (though you CAN if you want), then click on the “Combo Box” option. This will allow you to click anywhere on the sheet and a drop down box will appear. But what do I do with a combo box, you ask? You assign values to it so that you can then make it easy to see data summaries! To do this, right click on the combo box and click “Format Control”. This will bring up the way for you to assign a specific column or row to the box. Click on control in the pop up box. Under Input Range, this is where you select the column from which you want the choices. In my example, I’m going to use the Student Name Column:
You can have the option to link a cell to the display. What this means is that when you choose the data, it will reference that location in a cell. This enables you to use functions like the ones we went over. Lets look at a more comprehensive example:
Here, I’ve referenced Column C, which is my student name column. Therefore, my combo box will display all of my entries in column C. I’m linking the combo box to cell K4 and we notice on the sheet that currently cell K4 says 10. Why, because my student, Johnny 5, has his name on the 10th row of Column C. Got it? OK, now we can add our Index Functions or Sum Functions or whatever to display the information easily. In the following example, I’ve selected Jane 5 from my combo box, and I used a quick little Index function to say I want to display what’s on column D at whatever row is listed in K4. In this case, because I’ve tied my combo box to K4, the cell is displaying 11. So Index is looking for whatever exists in Column D at Row 11, which happens to be Jane 5’s Student ID.
With a simple example like this it really is redundant, but if you had 10,000 students and wanted to see a summary of just one of them? Combo Boxes and List Boxes work amazingly well.
Too Long; Didn’t Read (TL;DR)
Top 5 helpful Excel Formulas for educators:
- COUNTIF(range,criteria) – the range is the column you want to count, the criteria is what you’re interested in counting.
- SUMIF(range,criteria,sum_range) – the range is the column where you want to look for your criteria, the sum_range is the value you want to add if the criteria is matched in the range.
- VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) – the lookup_value is the data in the primary sheet that you’re matching to your table_array (my “other” sheet), the col_index is the column number of the data on the “other” sheet that you want to display, and the range_lookup for our purposes is always 0 which means you want to match the lookup_value exactly.
- IFERROR(value, value_if_error) – The value is what you want to display, the value_if_error is what you want to display if the first value results in an excel error (like #N/A, #REF!, #DIV/0!, etc)
- INDEX(array,MATCH(lookup_value,lookup_array,match_type)) – the array is the column of data from the “other sheet” that you want to display on the primary sheet while the lookup_value is the datum in the primary sheet you are matching against the lookup_array on the “other sheet”. The match_type for our purposes is 0, which means that you want to match the lookup_value exactly.
__________
SchoolStatus is the district-wide communication tool that integrates key student data in order to increase communication among educators, district administrators, and student families. The company’s solution aggregates individual student data, such as state assessments, attendance, and grades in an easy-to-visualize format and offers the option to communicate with student families via call, text, or email. Through SchoolStatus, millions of communications have occurred on the classroom, campus, and district level. For more information about SchoolStatus, visit www.schoolstatus.com.
