How to do course allocation in Excel: methods, maths and formulas

Quick answer: how to allocate students to courses in Excel

The simplest fair method that works in a spreadsheet is called serial dictatorship, and it takes four steps:

1. Put one student per row with their ranked preferences (Preference 1, 2, 3) and a Courses sheet with each course's seat capacity.
2. Sort the students by your priority rule: CGPA highest first for merit, submission time oldest first for first-come-first-served, or a random number for a lottery.
3. In an Allotted column, use a formula that gives each student their highest preference that still has a free seat, counting only the seats taken by the rows above them.
4. Fill the formula down. Row order is now priority order, and the sheet allocates itself.

The rest of this guide explains why that works, which other methods exist, where each one is the right choice, and the exact formulas. If you would rather start from a working file, download the free template.

What every allocation needs: the four inputs

Open electives, NEP-2020 baskets, minors, lab batches, project guides and hostel rooms look like different problems. Mathematically they are the same one. Every allocation is defined by four inputs, and getting these right matters more than the method you pick:

• Participants: the students (or applicants) who need a seat.
• Options and capacities: the courses, sections or rooms, and how many seats each one has. Capacity is a hard limit; an allocation that overfills a course is not an allocation.
• Preferences: each student's ranked list of options. Three to five ranks is typical; more ranks means fewer students end up unallocated.
• A priority rule: who gets considered first when two students want the last seat. Usually CGPA, an entrance rank, a submission timestamp or a random draw.

Most real allocations add a fifth input, eligibility: a student may not take an open elective offered by their own department, a minor may need a prerequisite, a lab batch may be restricted to one programme. Eligibility simply removes options from a student's list before the method runs.

The 6 allocation methods, and when to use each

1. First-come-first-served (FCFS)

Students are processed in the order they submitted. Each gets the best option still available. FCFS is easy to explain, but it rewards connection speed and free time rather than anything academic, and when it runs on a live portal it causes the stampede, crashes and overselling most colleges know well. It is acceptable for low-stakes allocations such as clubs, as long as submissions are timestamped reliably.

2. Merit order (serial dictatorship by CGPA)

Identical to FCFS, except students are sorted by merit, usually CGPA or an entrance rank, with a published tie-break. The top student gets their first choice, the next student gets their best remaining choice, and so on. Economists call this serial dictatorship. It is the method most institutions mean when they say "allocate on merit", and it is the default we recommend for electives, minors and specialisations.

3. Lottery (random serial dictatorship)

Serial dictatorship again, with students sorted by a random number instead. Use it when every eligible student has an equal claim to a seat, such as a first-year elective where there is no CGPA yet. A lottery is only fair if the draw is recorded and can be verified afterwards; a random order nobody can reproduce is indistinguishable from an admin choosing names.

4. Rounds by preference (the Boston mechanism)

Round 1 looks only at first choices and fills each course from those applicants, by priority. Round 2 looks at the second choices of everyone still unplaced, and so on. It feels intuitive ("we honour first choices first"), which is why it is common, but it has a serious flaw shown in the worked example below: a high-CGPA student who lists a popular course first can lose their second choice to a lower-CGPA student who listed it first. Students quickly learn to rank strategically instead of honestly.

5. Deferred acceptance (Gale-Shapley)

Used when the options also rank the students: a project guide who prefers certain students, or a course that prioritises its own majors. Students propose to their top choice, each option tentatively holds its best applicants up to capacity and rejects the rest, rejected students propose to their next choice, and the process repeats until nobody is rejected. It is stable (no student and course would both rather be matched to each other) and students cannot gain by lying about their preferences. When every course ranks students by the same CGPA list, deferred acceptance gives exactly the same result as merit-order serial dictatorship.

6. Optimisation (minimise total preference rank)

Treat the whole cohort at once and choose the assignment that minimises the sum of the ranks students receive, for example the most first choices overall. This is a linear programme (or, with one seat per option, the classic assignment problem solved by the Hungarian algorithm). It maximises overall satisfaction, but as the example shows, it can give a lower-CGPA student a seat a higher-CGPA student wanted, so it is hard to defend in an appeal.

The maths: a worked example with 6 students

Three electives, two seats each, six students. Everyone ranks all three courses:

Merit order (serial dictatorship)

1. Asha takes Data Science (1 of 2 seats filled).
2. Rohan takes Data Science (now full).
3. Meera wants Data Science, which is full, so she gets her 2nd choice, Psychology.
4. Kabir takes Psychology (now full).
5. Zoya wants Psychology, which is full, so she gets her 2nd choice, Photography.
6. Dev wants Psychology, then Data Science, both full, so he gets Photography.

Three first choices, two second choices, one third choice. The sum of ranks received is 3×1 + 2×2 + 1×3 = 10. Crucially, no student envies a lower-CGPA student's seat: every student who missed a course missed it to someone ranked above them. That property, no justified envy, is what makes the result defensible.

Rounds by preference (Boston)

Round 1: Data Science has three first-choice applicants and takes Asha and Rohan. Psychology has three first-choice applicants (Kabir, Zoya, Dev) and takes Kabir and Zoya. Round 2: Meera's second choice, Psychology, is already full, and so is Dev's, Data Science. Round 3: both get Photography.

The rank sum is still 10, but Meera (CGPA 8.7) ends up with her third choice while Zoya (CGPA 7.8) holds the Psychology seat Meera wanted. Had Meera put Psychology first she would have got it. That is justified envy, and an incentive to lie, in a six-student example.

Optimisation (minimise total rank)

Only four first choices are possible (two Data Science seats, two Psychology seats), so two students must take Photography. Photography is Rohan's and Zoya's 2nd choice and everyone else's 3rd, so the optimiser puts them there: Asha and Meera in Data Science, Kabir and Dev in Psychology, Rohan and Zoya in Photography. Rank sum 8, better than 10. But Rohan (8.9) has lost Data Science to Meera (8.7). Try explaining that to Rohan.

This is the trade-off in one table: efficiency versus defensibility. For anything a student can appeal, merit-order serial dictatorship is the method to beat. It is also the easiest to build in Excel.

Step-by-step: merit-based allocation in Excel

Step 1: lay out two sheets

A Courses sheet with columns A Course, B Offering department, C Capacity. A Students sheet with columns A Student ID, B Name, C Department, D CGPA, E Submitted at, F Preference 1, G Preference 2, H Preference 3, and an empty I Allotted course. Course names in F:H must match the Courses sheet exactly, so add a dropdown with Data > Data Validation > List pointing at Courses!$A$2:$A$51. Most broken spreadsheet allocations start with "Data Science" and "Data science " being counted as two courses.

Step 2: sort by priority

Select A:H and use Data > Sort: CGPA largest to smallest, then Submitted at oldest to newest as the tie-break. In Microsoft 365 you can produce a sorted copy with a single formula instead:

=SORTBY(Students!A2:H501, Students!D2:D501, -1, Students!E2:E501, 1)

Step 3: the allocation formula

Put this in I2 and fill it down. It checks Preference 1, then 2, then 3, and returns the first course that still has a seat:

=IF(COUNTIF(I$1:I1,F2)<VLOOKUP(F2,Courses!$A:$C,3,FALSE),F2,
 IF(COUNTIF(I$1:I1,G2)<VLOOKUP(G2,Courses!$A:$C,3,FALSE),G2,
  IF(COUNTIF(I$1:I1,H2)<VLOOKUP(H2,Courses!$A:$C,3,FALSE),H2,
   "Unallocated")))

The whole trick is the expanding range I$1:I1. In row 2 it covers only the header; filled down to row 50 it becomes I$1:I49. So COUNTIF(I$1:I1,F2) counts how many students above this one have already been given F2, and VLOOKUP fetches that course's capacity. If seats taken is below capacity, the student gets it. Because each row only reads rows above it, there is no circular reference, and the row order you sorted in step 2 becomes the priority order. That is serial dictatorship in a single formula.

Step 4: add eligibility rules

Open electives usually bar students from courses offered by their own department, and real data has blank preferences. In Microsoft 365, LET and LAMBDA keep the rule readable:

=LET(
  canTake, LAMBDA(c, AND(
    c <> "",
    COUNTIF(I$1:I1, c) < XLOOKUP(c, Courses!$A$2:$A$51, Courses!$C$2:$C$51, 0),
    XLOOKUP(c, Courses!$A$2:$A$51, Courses!$B$2:$B$51, "") <> $C2
  )),
  IF(canTake(F2), F2, IF(canTake(G2), G2, IF(canTake(H2), H2, "Unallocated")))
)

Each extra rule is one more line inside AND(): a minimum CGPA, a prerequisite looked up from another sheet, a programme restriction. The downloadable template contains an equivalent version written with IFERROR and VLOOKUP, so it also runs in older Excel and in Google Sheets.

Step 5: check capacity and outcomes

On the Courses sheet, add Filled and Remaining columns so overfilling is impossible to miss, and measure how well the allocation went:

=COUNTIF(Students!$I:$I, A2)
=C2-D2

=IFERROR(MATCH(I2, F2:H2, 0), "-")

=COUNTIF(Students!J2:J501, 1) / COUNTA(Students!A2:A501)

First-come-first-served allocation in Excel

Use exactly the same formula, and sort by Submitted at, oldest first, instead of CGPA. If preferences came from Google Forms or Microsoft Forms, the response timestamp is in the first column of the export. Two things to check before you trust it: remove duplicate submissions (keep the first per student ID, with Data > Remove Duplicates after sorting by time), and make sure the timestamps are real date-times rather than text, or the sort will be alphabetical.

Random (lottery) allocation in Excel

Add a column called Draw with =RAND() next to each student, then sort by it. One catch makes most spreadsheet lotteries unverifiable: RAND() recalculates every time the workbook changes, so the order you allocated on disappears the moment someone edits a cell. Freeze it first:

1. Fill =RAND() down the Draw column.
2. Copy the column, then Paste Special > Values, so the numbers stop changing.
3. Sort A:H by Draw, smallest to largest, and let the allocation formula run.
4. Save a copy of the file with the frozen draw numbers and publish it with the results, so the order can be checked later.

Optimal allocation with Excel Solver

If your goal is maximum overall satisfaction rather than merit order, Excel's Solver add-in can solve the linear programme. Enable it in File > Options > Add-ins, then:

1. Build a cost matrix: one row per student, one column per course, each cell holding the rank that student gave that course (1, 2, 3). Put a large number such as 99 where a course is unranked or the student is ineligible.
2. Build a same-sized decision matrix of empty cells. Solver fills these with 1 (assigned) or 0.
3. Objective: minimise =SUMPRODUCT(CostMatrix, DecisionMatrix).
4. Constraints: every decision cell is binary; each student's row sums to 1; each course's column sum is at most its capacity.
5. Choose the Simplex LP method and click Solve.

Mathematically, with x_ij = 1 when student i gets course j, r_ij the rank and c_j the capacity, this is: minimise Σ r_ij·x_ij subject to Σ_j x_ij = 1 for every student and Σ_i x_ij ≤ c_j for every course. The catch is size: the Solver that ships with Excel is limited to 200 decision cells. Forty students choosing among five courses already uses all of them, which rules it out for most real cohorts.

Bonus: splitting students into balanced sections

Section and lab-batch splits usually have no preferences at all; the goal is balance, so every section gets a similar spread of ability. Sort students by CGPA and deal them out in a "snake" (1, 2, 3, 3, 2, 1, 1, 2, 3, ...) so no section collects all the toppers. With the student list starting in row 2 and the number of sections in cell L1:

=IF(MOD(INT((ROW()-2)/$L$1),2)=0, MOD(ROW()-2,$L$1)+1, $L$1-MOD(ROW()-2,$L$1))

Wrap it in CHAR(64 + ...) to get section letters A, B, C. To balance gender or programme as well as CGPA, sort by that field first and CGPA second, then run the same formula.

Which method fits which allocation

Each of these has its own page with the specific rules involved: open electives, NEP-2020 baskets, MDC allotment, minors and honours, clubs, project guides, sections and lab batches and hostel rooms.

Where the spreadsheet breaks

The formula is the easy part. For a class of 60 the template above is genuinely enough. At a few hundred students across several departments, the work moves to everything around the formula:

• Collecting preferences. Forms produce typos, duplicate submissions, students who list the same course twice and choices that are not on offer. Cleaning the export often takes longer than the allocation.
• Eligibility at scale. Own-department rules, prerequisites, programme restrictions and reserved seats turn one readable formula into a nested one nobody else can audit.
• One wrong sort. Sorting a single column instead of the whole table silently reassigns preferences to the wrong students, and nothing on screen tells you.
• Multiple baskets and rounds. NEP-2020 asks for five baskets per semester; a second round for unallocated students means copying sheets and re-running by hand.
• Version chaos. "Allocation_final_v3_REAL.xlsx" on four laptops. Which one was published?
• Communication. Every student needs their result, usually by email, and every change means another round of messages.
• Appeals and audit. When a student asks why they did not get a course, you need the rule, their position in the order and the moment the course filled, not a spreadsheet that has been edited since.
• Student data. Spreadsheets of names, IDs and CGPAs get emailed around and end up in personal drives.