Skip to content

See the attendance impact we’re having in California!  >> Read Now <<

Topbar Close icon
Site Logo
  • Products
    • Product Overview
    • Attend: Attendance Interventions
    • Connect: Family Engagement
    • Boost: Teacher Observation and Growth
    • Forms & Flows: Digital Forms and Payments
    • Sites: Accessible Websites
  • Who We’re For
    • Superintendents
    • District Leaders
    • School Leaders
    • Educators & Staff
    • Families
  • Resources
    • Resource Library
    • News & Press
    • Blog
    • Case Studies
    • Checklists & Infographics
    • eBooks
    • Guides & Playbooks
    • Reports
    • Webinars
  • About
    • About SchoolStatus
    • Careers
    • Contact
Request a Demo Log In
Request a Demo Log In
5 Excel Functions Every Educator Needs to Know
Educator Development
Sep 18, 2023

5 Excel Functions Every Educator Needs to Know

SchoolStatus logo.
By SchoolStatus • 13 min
Data,Resources,Teacher,Technology
Share

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:

Edit Blog Post | 5 Excel Functions Every Educator Needs to Know Google Chrome, Today at 3.07.55 PM.png

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)

Edit Blog Post | 5 Excel Functions Every Educator Needs to Know Google Chrome, Today at 3.09.14 PM.png

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:

5 Excel Functions Educators Need to Know.docx Microsoft Word, Today at 3.11.04 PM.png

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:

5 Excel Functions Educators Need to Know.docx Microsoft Word, Today at 3.12.12 PM.png

*You can also achieve the same thing with AVERAGEIF

Maximizing Data From Your SIS

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:

5 Excel Functions Educators Need to Know.docx Microsoft Word, Today at 3.13.38 PM.png

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:

5 Excel Functions Educators Need to Know.docx Microsoft Word, Today at 3.15.08 PM.png

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:

5 Excel Functions Educators Need to Know.docx Microsoft Word, Today at 3.16.10 PM.png

*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:

5 Excel Functions Educators Need to Know.docx Microsoft Word, Today at 3.17.12 PM.png

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:

5 Excel Functions Educators Need to Know.docx Microsoft Word, Today at 3.18.14 PM.png

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.

5 Excel Functions Educators Need to Know.docx Microsoft Word, Today at 3.19.05 PM.png

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

5 Excel Functions Educators Need to Know.docx Microsoft Word, Today at 3.19.55 PM.png

*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: 

5 Excel Functions Educators Need to Know.docx Microsoft Word, Today at 9.22.10 AM.png

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:

5 Excel Functions Educators Need to Know.docx Microsoft Word, Today at 9.23.00 AM.png

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.

5 Excel Functions Educators Need to Know.docx Microsoft Word, Today at 9.24.50 AM.png

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!

SchoolStatus logo.
SchoolStatus
SchoolStatus is transforming K-12 education with an integrated suite of solutions that provide educators and administrators with real-time student insights and district attendance trends, enabling complete visibility across all levels of the education ecosystem. Through data-driven, multi-channel communications including calling, texting, video, and print materials, SchoolStatus empowers educators to implement proactive interventions that enhance student outcomes and family engagement. With millions of successful school-home interactions, SchoolStatus leads the way in bridging the gap between schools and families across the U.S. SchoolStatus partners with thousands of districts and serves over 22 million students across all 50 states.

Stay Connected

News, articles, and tips for meeting your district’s goals—delivered to your inbox.

More Resources

7 tips for creating an effective school newsletter
BrandingSchool Websites
May 9, 2024

7 Tips for Creating an Effective School Newsletter

read more
School Websites
Oct 8, 2022

Does Your School Website Reflect Your School’s Quality?

read more
School Websites
Aug 27, 2024

School Communications Planning Guide

read more

Document icon

Ready to learn more about our suite of solutions?

Request a Demo
Logo

Want Updates?

Stay connected with news, articles, and tips delivered to your inbox.

Stay in the Loop

Facebook LinkedIn YouTube

Solutions

  • How it Works
  • Attendance Interventions
  • Family Engagement
  • Teacher Observation & Coaching
  • Administrative Efficiency
  • Data Analytics

Who We're For

  • Superintendents
  • District Leaders
  • School Leaders
  • Educators & Staff
  • Families

Products

  • Products Overview
  • SchoolStatus Connect
  • SchoolStatus Attend
  • SchoolStatus Boost
  • SchoolStatus Forms & Flows
  • SchoolStatus Sites

Company

  • About SchoolStatus
  • Careers
  • Contact

Resources

  • Resource Library
  • News & Press
  • Blog
  • Case Studies
  • eBooks & Whitepapers
  • Webinars
  • Help Center
  • Customer Support
  • Website Terms of Use
  • SchoolStatus Privacy Policy
  • Accessibility
  • Sitemap
© 2025 SchoolStatus. All rights reserved