Excel for teachers: gradebooks, grades and attendance

In short: =SUMPRODUCT(weights,scores) for a weighted grade, =IFS(...) or =VLOOKUP(score,table,2,TRUE) for letter grades, =COUNTIF(range,"x") for absences, and a conditional formatting rule to highlight students who need help.

Set up the gradebook

A good gradebook has one row per student and one column per assignment, with the column headers in row 1 and nothing else mixed in. Keep the raw scores separate from the calculations: scores on the left, averages and grades on the right. That way you can sort, filter and fill formulas down without breaking anything.

For a plain average across all assignments, AVERAGE does the job: =AVERAGE(B2:H2). It skips empty cells, so a missing assignment does not count as zero. If a missing piece of work should count as zero, type the 0 in the cell. That small difference changes a lot of final grades, so decide on it before the term starts.

Weighted grades with SUMPRODUCT

Most courses do not weight everything equally. Homework might count for 20 percent, the final exam for more. SUMPRODUCT multiplies each score by its weight and adds the results in one step. If the weights add up to 1 (or 100%), that sum is the weighted grade. If they do not, divide by the total weight: =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6).

Task In C8, use SUMPRODUCT to calculate the student's weighted final grade. The weights in B2:B6 add up to 1.

	A	B	C
1	Component	Weight	Score
2	Homework	0.2	85
3	Quizzes	0.2	78
4	Project	0.25	92
5	Midterm	0.15	70
6	Final exam	0.2	81
7
8	Final grade

For a whole class, put the weights in a row above the students and lock that row with absolute references: =SUMPRODUCT($B$1:$F$1,B3:F3). Fill the formula down, and every student is graded with the same weights. Change a weight once, and every grade updates.

Letter grades with IFS or VLOOKUP

Grading scales differ between schools, so the cutoffs below are only an example. Use the ones your school sets.

IFS checks conditions from left to right and returns the first match. Start with the highest cutoff and end with TRUE to catch everything else:

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

According to Microsoft, IFS is available in Excel 2019 and later and in Microsoft 365. The IFS lesson has exercises with the same pattern.

VLOOKUP with approximate match is often the better choice for a gradebook, because the scale lives in a small table instead of inside the formula. Put the lowest score for each grade in ascending order (0, 60, 70, 80, 90) next to the letters (F, D, C, B, A), then use =VLOOKUP(G2,$J$2:$K$6,2,TRUE). TRUE tells Excel to find the largest cutoff that is not above the score. If the scale changes, you edit the table, not dozens of formulas. This works in every Excel version; the approximate match lesson explains the details.

Track attendance with COUNTIF

An attendance sheet works like the gradebook: students in rows, days in columns. Mark each absence with the same symbol, for example an x, and let COUNTIF do the counting. To count only one kind of absence, such as unexcused ones marked with a different letter, change the criteria. With COUNTIFS you can combine conditions across columns.

Task The attendance sheet has an x for every day a student was absent. In G2, use COUNTIF to count Ava's absences. The formula is filled down to G7.

	A	B	C	D	E	F	G
1	Name	Mon	Tue	Wed	Thu	Fri	Absences
2	Ava Mitchell		x
3	Ethan Brooks	x			x	x
4	Chloe Rivera
5	Mason Reed			x
6	Lily Foster	x	x
7	Owen Price					x

To show an attendance rate, divide the days present by the days taught: =1-G2/5 for a five-day week, formatted as a percentage.

Highlight students who need support

Once grades and absences are in one sheet, conditional formatting can color the rows that need attention, so you see them at a glance instead of scanning numbers. The rule is a formula that returns TRUE for the rows to highlight. With OR a low average or frequent absences is enough to trigger it. The dollar sign before the column letter keeps the whole row tied to the same cells.

Task Use conditional formatting to highlight the whole rows A2:C8 of students who need support: an average below 60 or 3 or more absences. Write the rule formula as it applies to A2.

	A	B	C
1	Name	Average	Absences
2	Ava Mitchell	78	1
3	Ethan Brooks	55	2
4	Chloe Rivera	88	0
5	Mason Reed	64	4
6	Lily Foster	91	1
7	Owen Price	59	5
8	Zoe Hayes	72	3

The conditional formatting lesson covers color scales, data bars and more formula rules.

Other skills that save time

• Sort and filter to list students by score or show only one group.
• Drop-down lists for entries like present, absent and late, so everyone types the same codes.
• RANK, LARGE and SMALL to find the top scores in a test.

Gradebooks hold personal data. Store them where your school's data rules allow, and share only what each person needs to see.

Using LearnThisNext with your students

If you teach Excel itself, or want students to handle spreadsheets in another subject, you can send them to the lessons here. Every lesson has a short explanation and exercises that run in the browser, with hints and instant feedback, so students do not need Excel installed. The lessons are free to use right now.

• Absolute beginners can start with the Excel for beginners guide.
• Students who already know some Excel can take the free skills test to find their level and a lesson to continue with.
• The course page shows the full learning path, from basic math to lookups and PivotTables.

Each student works on their own. Without an account, progress stays in their browser. With a free account, it is saved across devices. There are no class accounts or teacher dashboards at the moment, so to check progress, ask students to show you the lessons they have finished.

FAQ