5 Excel Functions Every Educator Needs to Know

As a valuable resource for K-12 educators and admin, SchoolStatus works to help you save time on administrative tasks and attendance management. By creating processes that scale across your district, we help reduce staff hours spent on generating documents reflecting—and collecting—district data. One of our customer’s favorite parts of SchoolStatus is our reporting engine. We pull all of your data together for you to make it accessible and easy to see. 

Still, even with our platform, there are times when you have to download and manipulate a CSV (Comma Separated Value) or Excel file yourself. Not everyone loves building spreadsheets. More importantly, there are knowledge gaps regarding general Excel functions. So, 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 time! 

Top 5 helpful Excel Functions for Education Leaders:

• 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 (the “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

#1 COUNTIF(range,criteria)

You may be familiar with Excel’s COUNT function and COUNTA function, which lets you count the number of cells in a column or Array. So, what does COUNTIF do? 

COUNTIF looks like:

=COUNTIF(A, B)

A: The column with the data you want to count

B: The data counting

For example, if you exported your Assessment Student Detail list—and it includes every student in your district—BUT only wanted to find the total number of 9th graders on the list. This is what you would use COUNTIF for. 

See this example:

Perhaps you want to know how many 9th graders took a State Assessment. Here, the formula here would be:

=COUNTIF(cell range, grade number) ex. =COUNTIF(D1:D11,9)

Notice, the “Totals” in this example is “3.” This is because there are 3 students in 9th grade—see column D1:D11 (that means cells 1 through 11 in row D). This example may not seem that helpful as there are not many students you need to count. BUT, if you had 10,000 students in the district, that would be difficult to count by hand. Hence, the usefulness of this function. 

Another way to design the formula would be to add a Reference Cell. Then you can change the number in that cell instead of explicitly referencing “9” as the grade level you’re interested in (as in the above example). If you want to change to another grade, just change the number in that cell. 

In this case, G3 is where we have defined the Reference Cell. Here the formula would be:

=COUNTIF(cell range, reference cell) ex. =COUNTIF(D1:D11,G3)

Either way works. Both are equally easy once you get the hang of it. 

#2 SUMIF(range,criteria,sum_range)

You are likely already familiar with the SUM function in Excel. SUMIF helps you add specific data together.

SUMIF looks like:

=SUMIF(A,B,C)

A: The column or row where “if” data exists 

B: The data you’re interested in using to determine the values to Sum 

C: The row you want to add together 

Let’s say we want to add up all of the entries in a column based on a specific Data Point in another column. To continue with our above example, let’s say we want to find the Sum of the “Scale Score” of all 9th graders. This is in two separate columns. How do you do that without filtering, summing, and then saving that somewhere else? Checking our same example Excel file, let’s look at the picture now:

We are now looking at column range D1 through D11. Looking at the “Grade” in the G3 cell—as we did in the example above—now we are telling Excel to add the values together from the “Scale Score” column for that “Grade.” Here we have the total number of 9th graders and their summed Scale Scores. 

Finally, if you want to see what the Average Score is for these 9th graders, you can add a cell that simply divides the “Sums” cell (=cell/cell).

Now we have our Average Scale Score:

*You can also achieve the same thing with AVERAGEIF

Maximizing Data From Your SIS

Best practices for maximizing your SIS with an integrated data and communication platform.

Download Now

#3 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

One of the most useful Excel functions is the “Vertical Lookup.” This allows you to look up data on one sheet and display it on another sheet based on specific criteria. 

VLOOKUP looks like:

=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” 

Let’s take our working example from above. In this case, we are adding another spreadsheet to our mix. This one has a table that displays a list of students, their Benchmark Percentile Scores, and other example data. 

Let’s see how VLOOKUP can help us:

Here we are displaying the number “32” as a Percentile. How did we get this number? 

Look at C2. This is referencing the student name “Johnny 1” on our Primary Sheet (this is the Data Point we are matching against). The next thing to look at is this Benchmark Data located on the other sheet we are matching with. 

This is the “other” sheet:

This sheet is organized by Percentile (you will notice the names here aren’t in alphabetical order. Rather, it is organized by number in “Column C”). 

The VLOOKUP is showing us where to look in this sheet. 

You will see, if we look for “Johnny 1” on this sheet, he has the number “32” in the Percentile Column (C5).

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.” We labeled it “Percentile” as well to avoid confusion. Lastly, we put a “0” because we only want to display data on the primary sheet if we 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, we’re looking up the student name from the Primary Sheet, so the first column of the “other” sheet with the “Percentile” has to be a list of student names as well. 

The last step is to copy this formula down into the remainder of the rows on the Primary Sheet:

*You can achieve the same thing with rows by using HLOOKUP

#4 IFERROR(value,value_if_error)

You will noticed that there are a few entries on the above spreadsheet example that are displaying “#N/A.” This is because we don’t have Percentiles for those student names on the “other” sheet we used. So, when VLOOKUP went to fetch something, it panicked and threw up an “error.” 

IFERROR resolves this by allowing you to display something else if there is an error. 

IFERROR looks like:

=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 step is a little trickier, but you’ve made it this far, so you can handle it. In our example, what we want to display is the result from our VLOOKUP, but if that result is an error, we want it to display the text of our choosing. Here we are using: “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 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:

Now, all of the “#N/A” errors are displaying the words “No Test.” We simply used “No Test” if something was an “error.” You can also use another function—even another VLOOKUP function—that references a different sheet. 

#5 INDEX MATCH: INDEX(array,MATCH(lookup_value,lookup_array,match_type))

INDEX Match is what VLOOKUP wants to be when it grows up. This function is actually two functions put together: the INDEX function and the MATCH function. This gives you the power of VLOOKUP, but with much greater flexibility and versatility.

INDEX looks like:

=INDEX(A, MATCH(B,C,D))

A: The column of the data you want to display from the “other” sheet

B: The data that you’re matching against the primary sheet

C: The column that you want to match from the “other” sheet

D: “0,” or for FALSE, meaning that you want an exact match

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 

3) you can only look up by column, you can’t look up by row 

INDEX MATCH resolves all of these deficiencies. 

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 will have to to match by Student ID. No problem! 

First, let’s 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 the “other” data sheet (listed above). This column has the data point that we’re interested in displaying.

Next, we tell Excel to look in “D2” (the Student ID) on my Primary Sheet. Then, to look at Column D on the “other sheet” to find the appropriate row––remembering to use “0” to match exactly. This tells Excel, “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.” Now, we have successfully used INDEX MATCH.

All that remains is to copy it down the row (and to remember to use the IFERROR function to clean up any “errors”).

*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).

Combo Boxes

While not a function, Combo Boxes are very useful drop-downs that you can create in Excel that allow for user interface. To create a Combo Box, simply go to the “Developer” tab at the top, then click on the “Combo Box” option. This will allow you to click anywhere on the sheet and a drop-down box will appear. 

Why is this useful? You can assign values to easily view data summaries. To do this, “right click” on the Combo Box and click “Format Control.” This will bring up a 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 our example, we’re going to use the “Student Name” column: 

You can have the option to link a cell to the display. This means when you choose the data, it will reference that location in a cell. This enables you to use functions like the ones above. 

Here is a more comprehensive example:

We’ve referenced Column C, which is the “Student Name” column. The Combo Box will display all of the entries in Column C. Linking the Combo Box to cell K4, we will notice on the sheet that currently cell K4 says “10.” Why? Because the student, “Johnny 5,” has his name on the 10th row of Column C. Now, we can add our Index Functions or Sum Functions to display the information easily. 

In the following example, we’ve selected “Jane 5” from the Combo Box, and used a quick INDEX function to say: “I want to display what’s on Column D at whichever row is listed in K4.” In this case, because we’ve tied the Combo Box to K4, the cell is displaying “11.” So, INDEX is looking for what exists in Column D at Row 11, which happens to be Jane 5’s Student ID.

For small examples like these, it might seem like extra work. However, if you have 10,000 students and want to see a summary of just one of them, Combo Boxes and List Boxes work amazingly well.

Optimizing Student Data with Excel Functions provides invaluable information for district administrators. Of course, saving time on building spreadsheets is also important. If you want to join our partners and let us help with these tools, book a demo to meet with our team!