Give every entrant a random number with RAND(), rank the numbers, and take the top few. That is the whole mechanism and it takes about four minutes to build.
The trap is that RAND() regenerates on every edit, every recalculation and every time the file opens. Announce a winner, then type in any other cell, and the sheet will now show somebody else. There is no undo and no record that the first name was ever there.
So the method matters more than the formulas. Below: both, a free template, and an honest account of what happens when you take this into a ballroom.
The Mechanism.
Names in column A, starting at row 4. Alongside each name:
=RAND()Then rank those numbers, lowest first:
=RANK(C4,$C$4:$C$503,1)The team ranked 1 is your winner. Ranked 2 is your second winner, and so on. Pull them out by rank:
=INDEX($A$4:$A$503,MATCH(1,$D$4:$D$503,0))Change the 1 to 2, 3 and so on for however many winners you need. Press F9 to draw again.
That's it. Everything else in this article is about not getting burned by it.
The One Rule.
Copy the winners and paste them somewhere as values, immediately, before you touch anything else.
RAND() is a volatile function. It recalculates when you edit any cell, when you press F9, when you open the file, and sometimes when you simply click around. Every recalculation is a fresh draw.
The failure looks like this. You draw the grand prize. You read the name out. Someone asks you to check the spelling, so you click into a cell to look. The sheet recalculates. The winner is now a different person, and there is no way back to the one you just announced in front of six hundred people.
Right-click, Paste Special, Values. Every single time. Make it a step in the run sheet, not something you remember.
No Duplicate Winners.
The question everyone asks, and the reason most home-made draws fail.
Keep a winner log on its own sheet. Then, next to each entrant, check whether they are already in it:
=IF(COUNTIF('Winner log'!$B:$B,A4)>0,"Yes","No")And make the draw number blank for anyone who has already won:
=IF(B4="Yes","",RAND())A blank is not ranked, so they cannot be drawn again.
Notice what this depends on. The name has to be in the log before the next draw runs. The spreadsheet does not know who you announced. It only knows what you wrote down. Skip that step in the rush between prizes and the same person can absolutely win twice, which is the one outcome a room actually notices.
Drawing Fifty At Once.
Nobody draws eighty prizes one at a time on stage. Forty minutes of a run sheet that does not have forty minutes in it.
Set a cell to the number of winners you want, then guard each output row against it:
=IF(5>$B$4,"",IFERROR(INDEX($A$4:$A$503,MATCH(5,$D$4:$D$503,0)),"NOT ENOUGH ENTRANTS"))Row 5 stays blank unless you asked for at least five winners. The IFERROR catches the case where you ask for more winners than you have eligible names, which happens later in the night than you expect once people start winning.
Draw the whole consolation tier in one go, paste it into the log, and hand the list to whoever is calling names.
Prize Tiers And The Pre-Draw.
Load every prize before the event: name, tier, and how many of each. Then count what has gone:
=COUNTIF('Winner log'!$C:$C,A4)Now you can see, live, that you have handed out thirty-eight of forty gift cards.
On pre-draws: they exist because drawing eighty prizes live would eat the run sheet, and the answer is usually to draw multiple winners at once rather than to do the whole thing the night before. A pre-draw means a spreadsheet at 9pm, an envelope of names, and someone cross-checking a printed list against a screen the next day. If you can draw fifty at once, most of that ritual disappears.
Verify The Name List First.
Duplicate entries in your name list are two chances to win, and nothing here will tell you.
Before the event, sort column A and look. Or use conditional formatting, Highlight Cells Rules, Duplicate Values. Do it while you can still ask HR which of the two Ben Carters is real, rather than discovering it when a name comes up twice.
The Free Template.
Download the lucky draw template (.xlsx)
Five hundred entrant slots, a tiered prize list, up to fifty winners drawn at once. The winner log makes names ineligible automatically. Prize counts update as you go. Yellow cells are yours, everything else calculates, and there is a read-me sheet.
Free, no form, use it commercially if you like.
What Happens In An Actual Ballroom.
Everything above works at a desk. Here is the rest.
There is nothing to watch. A name appears in a cell. No spin, no build, no moment. The draw is usually the last thing in the run sheet and the thing guests remember, and this version of it is a spreadsheet on a screen.
It depends on one person doing it right, under pressure. On a stage, holding a microphone, with the host waiting. The paste-as-values step is the one that gets skipped, and skipping it is unrecoverable.
There is no undo and no audit. Once it recalculates, the previous draw is gone. If someone asks on Monday who won prize thirty-six, the only answer is whatever got typed into the log at the time.
The winner left after dessert. You need to redraw for that prize, without disturbing the twelve prizes already awarded, while the room waits.
Ballroom wifi does not matter here, which is the one genuine advantage. A local Excel file does not care about the venue's network. Worth saying, because it is true, and most alternatives do care.
And it is somebody else's spreadsheet on the client's screen. Or, if you use a free web spinner instead, somebody else's watermark. Months of planning, a real AV budget, six hundred guests, and the final moment of the evening looks like it was assembled in the taxi over.
So Should You Use It?
For a team draw, an office raffle, a small internal event: yes. Take the template, follow the one rule, and don't spend money on this.
For a client's annual dinner, read the "one person doing it right, under pressure" line again. That is the actual risk, and it is not really a spreadsheet problem. It is that the highest-stakes two minutes of the evening are resting on somebody remembering a keyboard shortcut.
We built GamesTally Draw because we kept running that risk. Prize list loaded up front, multiple winners drawn at once, duplicate winners prevented by the system rather than by a discipline, an automatic log you can hand over on Monday, and a branded full-screen reveal that belongs at the event rather than a spinner with a stranger's logo on it.
USD 1,000, once. It is the cheapest thing we make, and for anyone running client dinners it is usually the first one they take.
For everything smaller, the template is genuinely fine. Just paste as values.
Related: Live leaderboard in Excel, with a free template · Live leaderboard in Google Sheets · Putting a leaderboard on the client screen with PowerPoint