How to Make a Gradebook in Excel (Percentage Method)

You do not need a specialty LMS plugin to answer “what is my percentage?” A blank workbook, a roster column, assignment headers, and one careful earned / possible formula will outperform another abandoned grading login—if you build the sheet in the right order.

For the full selection guide, see Excel gradebook template for college percentage grading.

Before You Type Headers

Decide one section per sheet. Mixing two lecture sections on one matrix creates paste disasters. Rename the tab BIO101-Fall or your course code. Keep attendance elsewhere.

Gather: syllabus weights (even if you start with flat points), letter scale, and a sample LMS export so name order matches.

Set Up the Columns

Create a DIY percentage gradebook

1. Open a new workbook and bold row 1 headers: Student | Quiz1 | Quiz2 | Midterm | Final | Total Earned | Total Possible | Percentage | Letter.
2. Row 2 points possible — enter the maximum for each assignment (leave Student blank or label “Possible”).
3. Freeze panes so names and headers stay visible.
4. Enter roster starting row 3 — paste names carefully; do not include blank spacer rows inside the data block.
5. Enter scores as points earned (not letter grades).
6. Total Earned — =SUM(B3:E3) (adjust to your assignment range).
7. Total Possible — =SUM($B$2:$E$2) or a per-row sum if possible points vary by excused work.
8. Percentage — =G3/H3 (earned/possible), then Format Cells → Percentage.
9. Letter — =IFS(I3>=0.9,"A",I3>=0.8,"B",I3>=0.7,"C",I3>=0.6,"D",TRUE,"F") (match your scale).
10. Drag formulas down, or convert to an Excel Table (Ctrl+T) so new students inherit formulas.

This mirrors patterns taught in wikiHow’s Excel gradebook guide and classic campus how-tos: SUM the earned points, divide by possible, then map letters. YouTube “percentage method” tutorials add the same idea with category averages when weights matter.

Blank Cells, Zeros, and Excused Work

For excused assignments, many teachers use “E” and exclude that column from the student’s possible points. Document the rule on a Start Here note so TAs do not invent policy at 11 p.m.

When DIY Stops Being Fun

If you spend more time repairing absolute references than recording scores, switch to a structured template with Start Here instructions. The College Gradebook Percentage Excell workbook (PlanoNest — disclosure: we sell it) is a one-time-purchase file aimed at Gradebook + Names + Grades sheets rather than an attendance or schedule tool. Live pricing sits on the product page.

Deep dives: gradebook percentage formula for teachers, weighted gradebook in Excel, and excel gradebook percentages.

Formatting Details That Prevent Week-Four Pain

Set narrow columns for short quizzes and wider ones for project titles. Use Wrap Text on headers only. Number format on score cells should be 0 or 0.0—not General with pasted “18/20” text. If the LMS exports fractions as text, clean in a staging column first.

Data validation helps on Letter columns if you ever override manually: limit to A–F plus blank. Conditional formatting on Percentage (<70% red) is optional but useful for advising triage—do not let color replace the numeric truth.

Stress Tests Before Go-Live

1. Perfect scores → 100%.
2. Half points on everything → ~50%.
3. One blank under zero-missing policy → percentage drops.
4. Add a new assignment column → totals still reference the expanded range (Tables help).

Quick Build Checklist

• One section per sheet
• Points possible on a locked row
• Percentage = earned / possible
• Letter map matches catalog
• Backup Save As before LMS paste

College-Specific Tweaks

Add a Student ID column if your registrar export uses it. Keep display names for printing. If you curve, do it on a separate “Curved %” column so the raw percentage remains auditable—Amherst-style notes emphasize keeping raw and curved math visible.

For weighted syllabi, do not stop at a flat percentage of all points unless the point budget already mirrors weights. Build category subtotals first, then apply weights (see the weighted article in this cluster).

Extra Implementation Notes

Keep a semester archive folder with dated copies after each major grade release. Name files with course code, term, and purpose so January-you can find December's official sheet. When you add assignments mid-term, extend Tables rather than inserting columns inside unprotected ranges that break neighboring SUM formulas. Teach TAs a two-minute paste ritual: staging sheet, values only, spot-check three IDs, then protect. If a student challenges a percentage, export the row to PDF with formula text visible rather than arguing from memory. Percentage gradebooks earn credibility when the audit trail is boring and complete. Revisit weights only when the syllabus changes; do not silently edit weights after scores exist without notifying the class. Finally, separate emotional curves from mechanical percentages: curve on a clearly labeled column so raw math remains inspectable.

Named Ranges and Absolute References

Once the Possible row works, lock it with $ so dragging student formulas does not slide the denominator down the sheet. =SUM(B3:E3)/SUM($B$2:$E$2) is the pattern most teachers need. If you insert a new assignment in column F, widen both SUM ranges in the same edit—or convert the block to a Table and use structured references so new columns are harder to miss.

Named ranges help when TAs fear dollar signs: define PossiblePoints as $B$2:$E$2 and write =SUM(B3:E3)/SUM(PossiblePoints). Document the name on Start Here so nobody deletes the name manager entry during “cleanup.”

Sample Lab Section Walkthrough

Imagine a 24-student lab. Week 1 quiz is out of 10. Week 2 quiz out of 10. Midterm out of 50. Final out of 50. After week 1, percentages look jumpy because totals are small—that is normal. Tell students early grades are provisional until more assessments post. Your sheet should still show honest math: 9/10 is 90% even if the course is only 7.5% complete by weight.

At midterm, recompute with all columns filled. Spot-check the student who asked for an incomplete on quiz 2: if excused, their possible points drop by 10 and the percentage should rise relative to a zero. If you forgot to adjust possible points, the sheet silently punishes them—catch that in the stress test, not in an appeal hearing.

Sharing Without Chaos

Emailing gradebook-FINAL-v7-REAL.xlsx to three TAs guarantees divergence. Prefer one cloud workbook with score columns editable and formula columns locked, or a weekly “scores inbound” sheet that only the owner merges. Never put FERPA-sensitive emails in a file you later upload to a public template gallery.

When you outgrow DIY maintenance, compare against the pillar checklist and the structured College Gradebook Percentage Excell workbook (one-time purchase; pricing on the product page).

Keep documentation boring on purpose: date every policy change, archive every official export, and refuse to edit live percentages during office hours. Create a duplicate sheet for what-if scenarios so the official gradebook remains stable. Train every TA on the blank-versus-zero rule in the first week, not after complaints arrive. Percentage gradebooks reward instructors who treat the spreadsheet like assessment infrastructure, not like a scratch pad that gets rebuilt each Sunday night under deadline pressure.

Frequently Asked Questions

How do I create grading in Excel quickly?

Roster + assignment columns + SUM + divide + percentage format. Add letters last.

What is the formula for a percentage grade?

Typically =earned/possible. Format as percentage. Example: =G3/H3.

Can I convert the range to a Table?

Yes. Ctrl+T helps formulas auto-fill when you add students.

---

Disclosure: PlanoNest sells digital templates. Product links go to our shop; pricing stays on the product page.