Cellwise Try it free

Excel for your field

Excel for teachers

For classroom teachers, coaches and tutors who keep grades and attendance in a spreadsheet, or want to stop doing it by hand. Below are the formulas that do the weekly jobs: category-weighted grades, letter grades from a scale you can edit, attendance rates, a list of who owes you work, and mixed-ability groups. Every example runs on the small class sheet shown here, and there are two free templates and practice questions at the end.

Student records can be confidential. Follow your school's rules on where grade and attendance data may be stored and shared.

Sample data

Every result on this page comes from this sheet, calculated by the same engine the Cellwise app uses. Change a number and watch the formulas react.

ABCDEFGH
1StudentPeriodHomeworkQuizzesTestsMissingAbsencesDays so far
2Maya29691880145
3Leo272788143
4Priya288949710
5Omar265748339
6Jonah458666267
7Sofia491857902
8Eli484727024
9Nora4100969200
10Weight0.20.30.5
11Min %060708090
12LetterFDCBA

What Excel does for teachers

Weight grades by category (homework 20%, quizzes 30%, tests 50%)

Most syllabi weight categories, not individual assignments. Keep one average per category (C to E) and the weights in their own row (C10:E10), so you can change a weight once and every grade updates.

SUMPRODUCT multiplies each average by its weight and adds the results. Dividing by the SUM of the weights keeps the grade correct when the weights do not add up to 100%, for example early in the term when no test has been given yet and you set that weight to 0. The dollar signs lock the weights row so the formula copies down the class.

=ROUND(SUMPRODUCT(C2:E2,$C$10:$E$10)/SUM($C$10:$E$10),1)

Result: 90.5

Maya's grade is 90.5: 96 x 20% + 91 x 30% + 88 x 50%. Copy it down and each student gets their own weighted grade.

Turn a percentage into a letter grade from your own scale

Put the cutoffs in a small table (row 11 holds the minimum percentage, row 12 the letter) instead of burying them in a long nested IF. When your school uses a different scale, or adds plus and minus grades, you edit the table, not 30 formulas.

XLOOKUP with match mode -1 finds the largest cutoff that is less than or equal to the grade. Round the grade first, to the same decimals you report, so a student shown 89.95 as 90.0 does not get a B. In Excel 2019 or older, =HLOOKUP(grade,$B$11:$F$12,2,TRUE) does the same job on a sideways table; the cutoffs must be in ascending order for it.

=XLOOKUP(ROUND(SUMPRODUCT(C6:E6,$C$10:$E$10),1),$B$11:$F$11,$B$12:$F$12,,-1)

Result: D

Jonah's weighted grade is 62.4, which sits between the 60 and 70 cutoffs, so he gets a D.

Attendance rate for each student

Attendance rate is the share of school days a student was there: 1 minus absences divided by days so far. Keep the day count in one cell (H2) and lock it with $ so the whole column updates when you change it on Friday.

Format the result as a percentage (Home > %). If you track tardies separately, decide whether a tardy counts as present and keep that rule the same for everyone.

=1-G5/$H$2

Result: 0.8 shows as 80% with Percent format

Omar has missed 9 of 45 days, so he has been in class 80% of the time.

Spot chronic absence before it shows up in grades

Many US schools and states define chronic absence as missing 10% or more of school days, whatever the reason. Use your own district's threshold if it differs. Building the threshold from the day count means it grows as the term goes on: 10% of 45 days is 4.5 days.

COUNTIF with a joined condition (">="&H2*0.1) counts the students at or above the line. Run it every couple of weeks; it is much easier to call home after 5 missed days than after 15.

=COUNTIF(G2:G9,">="&H2*0.1)

Result: 2

2 students, Omar (9 days) and Jonah (7), have already missed 10% of the term.

List who owes you work, ready to paste into an email

FILTER returns every name that meets a condition and TEXTJOIN glues them into one line with commas. Change the 3 to 1 to list everyone with anything missing.

This is the list you need before progress reports, a team meeting or a parent newsletter. Because it is a formula, it updates the moment you enter a late assignment. FILTER and TEXTJOIN need Excel 2021 or Microsoft 365 (or Google Sheets).

=TEXTJOIN(", ",TRUE,FILTER(A2:A9,F2:F9>=3))

Result: Leo, Omar, Jonah

Leo, Omar and Jonah each have 3 or more missing assignments.

Compare class averages across periods

When you teach the same course to several sections, AVERAGEIF gives each period's average for any column: the first range is the condition column, then the condition, then the column to average.

A big gap between periods on the same test is worth a second look: it can point to a lesson that went better in one room, a scheduling issue, or a question that was worded differently.

=ROUND(AVERAGEIF(B2:B9,4,E2:E9),1)

Result: 75.8

Period 4 averaged 75.8 on tests. Change the 4 to 2 and period 2 comes out at 87.3, so period 4 may need a review day.

Make mixed-ability groups in one formula

Ranking students and dealing them out like cards gives every group a mix of stronger and weaker students. COUNTIF counts how many scored higher than this student (that is their rank, starting at 0), and MOD(rank,4)+1 cycles through groups 1, 2, 3, 4, 1, 2...

Change the 4 to the number of groups you want. Two students with exactly the same score land in the same group; break ties by adding COUNTIF($E$2:E2,E2)-1 to the rank. Sort or filter by the group column to print the lists.

=MOD(COUNTIF($E$2:$E$9,">"&E2),4)+1

Result: 3

Two students scored above Maya's 88, so her rank is 2 and she goes to group 3. Priya (top score) and Leo (fifth) share group 1.

Practice: 8 exercises

Use the sample data above. Type a formula and press Check: it is graded by a real spreadsheet engine, the way Cellwise grades lessons.

  1. 1What is the class average on tests? Put it in a cell next to the sheet.

    Show answer

    =AVERAGE(E2:E9) = 81.5. AVERAGE adds the eight test scores (652) and divides by 8, giving 81.5.

  2. 2What is the highest quiz average in the class?

    Show answer

    =MAX(D2:D9) = 96. Nora's 96 is the top value in D2:D9.

  3. 3How many students have no missing work at all?

    Show answer

    =COUNTIF(F2:F9,0) = 3. COUNTIF counts Maya, Sofia and Nora, so the answer is 3.

  4. 4How many assignments are missing in period 2 altogether?

    Show answer

    =SUMIF(B2:B9,2,F2:F9) = 8. It adds the Missing column only where Period is 2: 0 + 4 + 1 + 3 = 8.

  5. 5What is Leo's attendance rate? (Leo is row 3; days so far are in H2.)

    Show answer

    =1-G3/H2 = 0.93333333333. 3 absences out of 45 days leaves 0.933, or 93%.

  6. 6Work out Leo's weighted grade using the weights in C10:E10.

    Show answer

    =SUMPRODUCT(C3:E3,C10:E10) = 78.3. 72 x 0.2 + 78 x 0.3 + 81 x 0.5 = 78.3. The weights add up to 1, so there is no need to divide.

  7. 7Give Leo his letter grade from the scale in B11:F12, in one formula.

    Show answer

    =XLOOKUP(SUMPRODUCT(C3:E3,C10:E10),B11:F11,B12:F12,,-1) = C. 78.3 is below 80 but at least 70, so XLOOKUP stops at the 70 cutoff and returns C.

  8. 8List the names of students with 5 or more absences, separated by commas.

    Show answer

    =TEXTJOIN(", ",TRUE,FILTER(A2:A9,G2:G9>=5)) = Omar, Jonah. Only Omar (9) and Jonah (7) pass the test, so the result is "Omar, Jonah".

Free templates for teachers

Guided project: Parent conference report

Weight grades, assign letters, check attendance and build the list of families to call.

5 steps in the Cellwise app. Each step checks your formula in a real grid, with hints when you get stuck. Part of Cellwise Plus.

Open guided projects

Guides for these formulas

FAQ

How do I calculate weighted grades in Excel?

Put each category average in its own column and the weights in a row, then use =SUMPRODUCT(scores,weights)/SUM(weights). Lock the weights row with $ signs so you can copy the formula down the class. Dividing by SUM(weights) keeps the grade right when the weights do not add up to 100%.

How do I drop the lowest score in Excel?

Subtract the lowest score and divide by one fewer: =(SUM(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-1). To drop the two lowest, subtract SMALL(B2:F2,1) and SMALL(B2:F2,2) and divide by COUNT minus 2.

Should I round grades before giving letter grades?

Round to the same number of decimals you show students, then look up the letter from the rounded value. Otherwise a grade displayed as 90.0 can be 89.96 underneath, and a lookup that uses the unrounded value returns B.

Can I use these formulas in Google Sheets?

Yes. SUMPRODUCT, XLOOKUP, COUNTIF, AVERAGEIF, FILTER and TEXTJOIN all work in Google Sheets with the same arguments. In Excel, FILTER and TEXTJOIN need Excel 2021 or Microsoft 365.

Excel for other fields

Updated