A student summary report pulls together performance data for a class, a grade level, or a whole school into a single view that's easy to read and act on. In Excel, the right tool for this job is a pivot table — it lets you slice student data by class, teacher, subject, or any other dimension without rewriting formulas every time.
This guide walks through building a student summary report in Excel from a flat data export, step by step.
What You Need Before You Start
Your starting point should be a flat data table — one row per student per assessment or course. The columns you'll typically have: student ID, student name, class, teacher, subject, assessment name, score, and date. This might come from a student information system, a grade book export, or a manual log.
Clean it up before you touch the pivot table. Remove any blank rows, make sure every row has a student name and score, and check that the Score column contains numbers, not text. If scores imported as text (common with SIS exports), select the column, go to Data, Text to Columns, and finish the wizard without changing anything — this forces Excel to re-read them as numbers.
Building the Pivot Table Summary
Click anywhere in your data, go to Insert, click PivotTable, and place it on a new sheet. In the PivotTable Fields pane:
Add Class to Rows. Add Student Name to Rows below Class. Add Score to Values, set to Average. Add Score to Values again, set to Min. Add Score to Values a third time, set to Max.
Now you have a summary showing each student's average, lowest, and highest score within each class. Collapse the student rows to see class-level averages at a glance.
Adding a Letter Grade Column
Pivot tables don't let you write custom formulas directly in the value cells, so add a helper column in your source data first. In a new column called Letter Grade, use:
=IF(H2>=90,"A",IF(H2>=80,"B",IF(H2>=70,"C",IF(H2>=60,"D","F"))))
Where H2 is the Score column. Refresh your pivot table (right-click, Refresh) and now you can add Letter Grade to the pivot to count how many A, B, C grades each class has.
Spotting Performance Gaps
Add a second pivot table on the same sheet (or a new one) with Teacher in Rows and Average Score in Values. Sort by Average Score descending. This surfaces which classes or teachers have the highest and lowest average performance — useful for identifying where students might need more support.
Use conditional formatting on the Average column to color-code the values: green above 80, yellow 70–79, red below 70.
Summarizing by Subject
If your data spans multiple subjects, add Subject to the Columns area of your main pivot table. Now you can see each student's average score per subject side by side — useful for spotting a student who's strong in math but struggling in English, or vice versa.
The Easy Way: Using SheetXAI in Excel
Building this manually takes time, especially when the data has issues or you need to rebuild the report every term. SheetXAI works directly inside Excel as a sidebar.
Example 1: Your data is already in the spreadsheet.
"I have a student data export on Sheet 1 with columns for student name, class, subject, teacher, and score. Build a summary report showing average, min, and max score per student, grouped by class, with conditional formatting to flag students below 70."
SheetXAI reads your data, builds the pivot summary, adds the conditional formatting, and formats the output.
Example 2: Your data lives in your school's system.
"Pull this semester's assessment scores from our student information system and build a summary report by class and subject, showing average performance and flagging any student with an average below 70."
SheetXAI connects to the data source, pulls the records, and builds the full summary report.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Track Student Grades in Google Sheets, How to Create a Pivot Table in Excel, and Google Sheets AI Guide.
