Excel for Teachers: Grades, Averages and Rankings

Excel for teachers is mainly about the gradebook: working out each student's average, finding the class high and low, turning scores into letter grades and ranking the class. A handful of functions, AVERAGE, MIN, MAX, IFS, LARGE and RANK.EQ, do almost all of it. This guide is for teachers, teaching assistants and school administrators, and every example uses one small gradebook you can type in yourself.

What teachers use Excel for#

Most schools have a learning platform, but Excel is still handy for quick calculations and your own records. Common uses include:

• Gradebooks: scores per test, averages, letter grades and rankings. The guide to building an Excel gradebook covers the full layout.
• Attendance: present, absent and late, counted with COUNTIF.
• Seating plans and groups.
• Reports: class averages and the number of students at each grade.

The examples use this gradebook in A1:E7:

Averages, highs and lows#

AVERAGE adds a set of numbers and divides by how many there are. In E2:

=AVERAGE(B2:D2)

returns 85 for Ava ((78 + 85 + 92) ÷ 3). Fill it down (Windows: Ctrl+D; Mac: ⌘D) to get every student's average. For the class average on Test 1, average the column instead: =AVERAGE(B2:B7) returns about 73.33.

MIN and MAX find the lowest and highest values:

• =MAX(B2:B7) returns 91, the top Test 1 score (Cara).
• =MIN(B2:B7) returns 55, the lowest (Dan).

AVERAGE skips empty cells, but it counts a 0. If a student missed a test, leave the cell blank rather than typing 0, unless a zero is the real score. When a few very low or high scores skew the picture, the median can be fairer; see AVERAGE vs MEDIAN. The AVERAGE function guide and the guide to AVERAGE, MIN and MAX have more examples.

Letter grades with IFS#

IFS checks conditions in order and returns the result for the first one that's true. That makes it ideal for grade bands. In F2:

=IFS(E2>=90,"A",E2>=80,"B",E2>=70,"C",E2>=60,"D",TRUE,"F")

Ava's 85 isn't 90 or more, but it is 80 or more, so she gets B. Filled down, the grades are B, D, A, D, B and C.

Two rules make IFS work:

1. Check the highest band first. If you test E2>=60 first, every student scoring 60 or more gets a D.
2. End with TRUE. TRUE,"F" catches every score that didn't match a band. Without it, a score below 60 returns #N/A.

IFS needs Excel 2019 or later. In older versions, nest IF functions in the same order: =IF(E2>=90,"A",IF(E2>=80,"B",IF(E2>=70,"C",IF(E2>=60,"D","F")))). See the IFS function guide.

To count students at each grade, use COUNTIF on the grade column: =COUNTIF(F2:F7,"B") returns 2.

Top and bottom scores with LARGE and SMALL#

MAX gives only the highest value. LARGE gives the 1st, 2nd, 3rd or any nth highest:

• =LARGE(E2:E7,1) returns 91.33 (the same as MAX).
• =LARGE(E2:E7,2) returns 85, the second-highest average.
• =SMALL(E2:E7,1) returns 62.33, the lowest average.

To find who has the second-highest average, combine it with INDEX and MATCH: =INDEX(A2:A7,MATCH(LARGE(E2:E7,2),E2:E7,0)) returns Ava. The comparison of MAX vs LARGE explains when to use each.

Ranking a class with RANK.EQ#

RANK.EQ gives each value's position in a list, with the highest as 1. In G2:

=RANK.EQ(E2,$E$2:$E$7)

Ava is ranked 2. Fill down to rank everyone: Cara 1, Ava 2, Eve 3, Fin 4, Ben 5 and Dan 6. The $ signs keep the comparison range fixed as you fill. If two students tie, they share a rank and the next rank is skipped. For example, two students on 85 would both be ranked 2, and the next student would be ranked 4.

Flagging students who need support#

Conditional formatting colours cells that meet a rule, so you can see at a glance who's struggling.

1. Select the averages, E2:E7.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Less Than.
3. Type 65 and choose a fill colour.

Ben (64) and Dan (62.33) are highlighted. =COUNTIF(E2:E7,"<65") gives the count, 2, for your report. Use a colour scale instead to shade every score from low to high.

Counting attendance with COUNTIF#

COUNTIF counts the cells that match one condition, which makes it the natural tool for attendance. If each row is a student and columns B to U hold a P, A or L for each school day in a month, then:

=COUNTIF(B2:U2,"A")

counts that student's absences. =COUNTIF(B2:U2,"P")/COUNTA(B2:U2) gives their attendance rate; format it as a percentage. COUNTIF ignores capitals, so "a" and "A" count the same, but a stray space after the letter won't match. A dropdown list (Data > Data Validation > List) with P, A and L keeps the entries consistent.

Weighted averages and keeping records safe#

If tests count differently, such as a final exam worth half the grade, use a weighted average. With weights of 25%, 25% and 50% in B9:D9, =SUMPRODUCT(B2:D2,$B$9:$D$9) gives Ava a weighted score of 86.75.

Student data is personal. Follow your school's rules on where gradebooks are stored and who can see them, and share class summaries rather than named lists when that's enough. The same averaging skills work in other fields too: Excel for real estate uses them on house prices.

Practise it#

Gradebook formulas are quick to master with a little practice. Practise nested IF and IFS in a live spreadsheet: you type the formula, and it checks it instantly. Then practise AVERAGE, MIN and MAX and find top and bottom scores with LARGE and SMALL.