Free Excel template

Free student group maker template

Q: How do I split a list into groups in Excel?

For simple groups in list order, number each row and use =MOD(row_number-1,groups)+1. For balanced groups, rank by score first and use a snake order, as this template does, so each group gets a similar mix.

Q: How do I rank with ties in Excel without skipping numbers?

Add a running COUNTIF to the rank: =COUNTIF(all,">"&C6)+COUNTIF($C$6:C6,C6). Tied values get consecutive ranks in list order, so every rank from 1 to the number of students is used exactly once.

For teachers, coaches, camp leaders and workshop facilitators who want mixed-ability groups or fair teams without shuffling name cards. Paste your list with a score (a test result, a skill rating or a reading level), choose the number of groups, and the template ranks everyone and deals them into groups with a snake draft so each group gets a similar spread of strong and developing students.

cellwise-student-group-maker-template.xlsxExcel workbook · 5.5 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup

Download free template

Preview and try it

This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.

	A	B	C	D	E	G	H	I
1	Balanced student group maker
2	Number of groups	6
3	Students listed	24
4	Group size	4
5	No.	Student	Score	Rank	Group	Group	Students	Avg score
6	1	Aaliyah Brown	88	6	6	1	4	76.5
7	2	Ben Ortiz	72	16	4	2	4	76.5
8	3	Carla Diaz	95	1	1	3	4	76.5
9	4	Dylan Moore	64	20	5	4	4	77.3
10	5	Esme Clarke	81	10	3	5	4	77
11	6	Felix Wong	77	13	1	6	4	77.5
12	7	Gia Romano	91	4	4	7
13	8	Hugo Patel	58	23	2	8
14	9	Isla Murphy	84	8	5
15	10	Jamal Reed	69	18	6	Class average		76.9
16	11	Kenji Sato	93	2	2	Highest group avg		77.5
17	12	Lena Fischer	75	14	2	Lowest group avg		76.5
18	13	Mason Hall	62	21	4	Spread		1
19	14	Nina Kaur	86	7	6
20	15	Oscar Lindqvist	79	12	1	Find a student	Nina Kaur

How the formulas work

Rank every student, with ties broken

D6=IF(B6="","",COUNTIF($C$6:$C$29,">"&C6)+COUNTIF($C$6:C6,C6))

The first COUNTIF counts how many students scored higher, which gives the rank. The second counts how many times this score has appeared so far, from the top of the list down to this row (note the range that only locks its start, $C$6:C6). That adds 1 for the first student with a score, 2 for the second, and so on, so tied students get different ranks and nobody is skipped. Isla Murphy and Willa James both scored 84 and get ranks 8 and 9.

Deal students into groups with a snake draft

E6=IF(D6="","",IF(MOD(INT((D6-1)/$B$2),2)=0,MOD(D6-1,$B$2)+1,$B$2-MOD(D6-1,$B$2)))

Think of dealing cards: with 6 groups, ranks 1 to 6 go to groups 1 to 6, then ranks 7 to 12 go back the other way, 6 to 1, and so on. INT((rank-1)/groups) is the round number; MOD(round,2) tells odd rounds from even ones; MOD(rank-1,groups) is the position inside the round. Snaking stops group 1 from always getting the strongest student of every round.

Check the groups are balanced

I6=IF(G6>$B$2,"",ROUND(AVERAGEIF($E$6:$E$29,G6,$C$6:$C$29),1))

AVERAGEIF averages the scores of students whose group number matches G6. Rows for group numbers higher than B2 stay blank, so the table works for 2 to 8 groups. I18 shows the spread between the strongest and weakest group average; with the sample class it is under 2 points.

Look up which group a student is in

I21=IFERROR(INDEX($E$6:$E$29,MATCH(H20,$B$6:$B$29,0)),"Not found")

Type a name in H20. MATCH finds that student's row (0 means exact match) and INDEX returns the group from the same row. IFERROR shows Not found for a misspelled name instead of #N/A.

Make it yours

To see each group together, copy columns B to E and paste them as values on a new sheet (Home > Paste > Values), then sort by Group. Sorting the original list is fine too, since the ranks and groups recalculate from the scores.
For random groups instead of balanced ones, put =RAND() in column C. Every recalculation (F9) reshuffles the groups, so paste the result as values once you like it.
To keep two students apart, swap one of them with a student of a similar score in another group by changing the scores slightly, or adjust by hand after the draft.
Fill the Score column for every student before you rank. A blank score counts below every number, which pushes that student to the bottom rank.

FAQ

How do I split a list into groups in Excel?

For simple groups in list order, number each row and use =MOD(row_number-1,groups)+1. For balanced groups, rank by score first and use a snake order, as this template does, so each group gets a similar mix.

How do I rank with ties in Excel without skipping numbers?

Add a running COUNTIF to the rank: =COUNTIF(all,">"&C6)+COUNTIF($C$6:C6,C6). Tied values get consecutive ranks in list order, so every rank from 1 to the number of students is used exactly once.

What if the class does not divide evenly?

The last round of the draft is only partly filled, so groups differ by at most one person, and B4 shows the sizes you will get. The groups that are one short miss a lower-ranked student, so their average can sit a few points higher; check the spread in I18 and move one student by hand if it matters.

Functions used

Related guides

More free templates

Updated November 2, 2026