How to Combine Grades from Multiple Sheets in Excel

It's the end of term and your grades live in a workbook with a tab per section: 10-A, 10-B, 10-C, maybe a few more. Each tab has the same columns, and each holds a different set of students. Now the head of department wants one ranked list of the whole grade, and you're about to copy-paste three or four sheets on top of each other, praying the columns line up. To combine grades from multiple sheets in Excel without that copy-paste roulette, a one-click macro stacks every section into a single gradebook for you.

This is the end-of-term consolidation aimed at teachers, using a free macro to do the stacking and Excel's built-in tools where they already fit.

TL;DR: combine grades from multiple sheets in Excel

• Line the sheets up. Every section tab needs the same columns in the same order, headers in row 1.
• Run the combine macro. It stacks all sections into one "Combined" sheet with a single header.
• Grade the whole cohort. Rank, average, and assign letters on the combined list, not tab by tab.
• Know the limit. This stacks sections of students. Pulling different tests per student is a VLOOKUP job.

With the macro loaded this takes about 20 seconds. The copy-paste version takes ten careful minutes and usually leaves one section's header stranded in the middle of the data.

When this works, and when it's a VLOOKUP job

This matters more than any step, so read it first. The macro stacks sheets vertically, one on top of the next. That's exactly right when each sheet is a different group of students with the same columns, like your section tabs. You end up with every student in one long list.

It is the wrong tool when each sheet is a different assessment for the same students, a Quiz 1 tab, a Midterm tab, a Final tab, and you want them side by side per student. That's a horizontal join keyed on student ID, which is what VLOOKUP or XLOOKUP are for. Stacking those would pile the same names three times. So: different students per sheet, stack them. Same students, different columns per sheet, look them up.

Step 1: Line up the section sheets

Before combining, make the tabs consistent. Every section sheet should have the same column headers in the same order, with the header in row 1. If 10-A has "Roll No, Name, Final" and 10-B has "Name, Roll No, Final," the stack will misalign, because the macro copies each sheet's used range as-is.

Spend a minute making the columns match across sections. Same headers, same order. Blank sheets are skipped automatically, so an empty "Notes" tab won't cause trouble.

Step 2: Combine every section into one gradebook

Open the workbook and run Combine All Sheets Into One. It asks whether the first row of each sheet is a header. Answer Yes, and it keeps one header from the first section and drops the repeated header rows from the rest. New to running macros? The 30-second setup gets you going.

It creates a sheet called Combined at the front of the workbook with every student from every section stacked into one list, and reports how many sheets it merged and the total row count. Your section tabs stay untouched, so nothing is lost. Note that it replaces any existing sheet named Combined, so rename an old one if you want to keep it.

Step 3: Now grade the whole cohort

The point of one list is that you can finally treat the grade as a grade, not a pile of sections. On the Combined sheet:

• Rank and spot the extremes. Run Highlight Top and Bottom Values on the final-score column to see your highest and lowest performers across all sections at once.
• Get the class statistics. Run Quick Statistics Summary on the score column for the mean, median, and spread of the whole grade, not just one room.
• Assign letter grades. Run Letter Grade From Numeric Scores to turn the combined scores into A-to-F grades in one pass, with your own cutoffs.

The built-in alternatives

Excel has native ways to combine sheets, and they're worth knowing. VSTACK (in Microsoft 365) stacks ranges with a formula like =VSTACK(SectionA!A2:D40,SectionB!A2:D35), which is live but spills and needs editing when a section grows. Power Query can append the sheets into a refreshable table, which is great if you rebuild this every term but is more setup than a one-off needs.

For summarizing rather than stacking, Excel's Consolidate feature can average or total by a matching label across sheets. The macro wins when you just want the raw combined list, once, without writing a formula or building a query.

The hot take: twelve section tabs is not a gradebook

If your grades are spread across a tab per section, you don't have a gradebook. You have twelve small gradebooks that can't talk to each other. You can't rank the grade, you can't see the real class average, and you can't tell whether 10-C is quietly falling behind, because the data never sits in one place.

Consolidating isn't busywork you do for the report. It's the step that lets you actually see the whole cohort. One list is a source of truth. Twelve tabs is twelve chances for a number to disagree with itself.

Common pitfalls

• Mismatched columns across sections. The macro stacks the used range as-is, so different column orders misalign. Standardize the headers first.
• Stacking assessments instead of sections. Same students across sheets means you want a VLOOKUP join, not a vertical stack. Stacking duplicates the names.
• An existing Combined sheet. The macro overwrites any sheet named Combined. Rename the old one if it matters.
• Trusting the total blindly. Check the reported row count against your rosters. If it's short, a section sheet was probably empty or misnamed.

Frequently asked questions

How do I combine grades from multiple sheets in Excel?

Put each class section on its own sheet with the same columns, then run the Combine All Sheets macro and answer Yes when it asks about headers. It stacks every section into one Combined sheet with a single header row, so you get every student in one list ready to rank, average, and grade.

What if each sheet is a different test, not a different class?

Then stacking is the wrong move, because it would repeat every student's name once per test. Use VLOOKUP or XLOOKUP to pull each test's score into a single row per student, keyed on student ID or name. Stacking is for combining different groups of students, not different columns for the same students.

Will combining delete my original section tabs?

No. The macro copies the data into a new Combined sheet and leaves every original section tab exactly as it was. The only sheet it will overwrite is a pre-existing one named Combined, so rename that first if you need to keep it. Your source sheets are safe.

How do I get a class average after combining?

Run the Quick Statistics Summary macro on the score column of the Combined sheet. It returns the count, mean, median, mode, and standard deviation for the whole grade at once. Because it's running on the combined list, the average reflects every section together, not just one room.

Do these macros work on Excel for Mac?

Yes. The combine, statistics, letter-grade, and highlight macros all run on Excel 2016 and later on Windows and Mac. They avoid Windows-only features, so a teacher moving between a school PC and a personal Mac gets the same result.

What to do next

Line the sections up, run the combine, and grade the cohort as one list instead of chasing numbers across tabs.

To combine grades from multiple sheets in Excel this term, browse the full set of Excel tools for teachers, or start with Combine All Sheets Into One on your section workbook.