CommentsToSheets

Guides

How to pick a giveaway winner in a spreadsheet

A spreadsheet can run a fair draw. What it cannot do on its own is prove the draw was fair. This guide does both halves: the formulas, and the routine that makes the result something you can show an entrant who asks.

Updated Nov 17, 2026 · 7 min read

Before any formula: the entry list

Fairness is decided here, not in the random step. From the exported comments:

  1. Keep top-level comments: filter is_reply to no.
  2. Remove your own and staff accounts.
  3. Apply your tag rule. Helper column: =IF(K2="",0,LEN(K2)-LEN(SUBSTITUTE(K2,"@",""))); keep rows at or above the number you announced.
  4. Drop comments after the deadline (times are UTC).
  5. One entry per person: Remove Duplicates on username, unless your rules said every comment is an entry.
  6. Copy the remaining usernames to a sheet named Entries, column A, from A2. Save a copy of the file now, before drawing.

One winner: RANDBETWEEN

=INDEX(Entries!A2:A, RANDBETWEEN(1, COUNTA(Entries!A2:A)))

Works the same in Excel and Google Sheets. The moment it shows a name, copy the cell and Paste special, Values only, because the formula picks again on every recalculation.

Several winners and backups: sort by RAND

  1. In Entries!B2: =RAND(), filled down to the last entry.
  2. Copy column B and paste it back as values, so the numbers stop changing.
  3. Sort the sheet by column B.
  4. Rows 2, 3, 4 are your winners in order; the next rows are backups if a winner does not reply.

Excel 365 does it in one cell: =TAKE(SORTBY(Entries!A2:A500, RANDARRAY(ROWS(Entries!A2:A500))), 3). Google Sheets: =ARRAY_CONSTRAIN(SORT(FILTER(Entries!A2:A, Entries!A2:A<>""), RANDARRAY(COUNTA(Entries!A2:A)), TRUE), 3, 1). Paste either as values straight away.

Make it defensible

  • Record the entry count and time before drawing, in a cell next to the list.
  • Screen-record the draw: the entry list, the formula, the result. A recording is the only thing that shows there was one attempt.
  • Keep the original export, unedited, so the list can be rebuilt from it.
  • Announce the rules you applied with the winner: "top-level comments tagging two friends, one entry per person, 412 entries".

When a seeded draw is the better choice

The spreadsheet's random number is gone the moment it is used, so nobody can check it. A draw tool built for giveaways can publish the random seed and a hash (fingerprint) of the entry list on a public results page; anyone can rerun the shuffle with that seed and get the same winner, and any redraw appears as a new seed. For a small giveaway among friends, the spreadsheet is fine. For one with a real prize or a sceptical audience, use a draw that leaves that proof, and keep this spreadsheet as your own record of who entered and why.

FAQ

Is RAND random enough?

For picking one name from a few thousand, yes. The weakness is not the randomness but the lack of a record.

Can I give extra entries to people who tagged more friends?

Only if your rules said so. If they did, repeat each username once per entry before drawing, and say so when you announce the winner.