Secret Santa Generator in Excel and Google Sheets (Free Formula, No Self-Picks)
Secret Santa season starts in November, when offices, friend groups and families organise their gift swaps before Christmas on Friday 25 December 2026. You do not need a website or an app to draw names: a spreadsheet can do it in two formulas, guarantee that nobody draws themselves, and keep a record for the organiser. Everything below works in current Excel and Google Sheets, with a version for older Excel too.
The idea: shuffle, then pass to the next person
Drawing names from a hat can go wrong: someone picks their own name, and you start again. The spreadsheet method avoids that completely. First, shuffle the list of names into a random order. Then each person in the shuffled list buys for the next person on the list, and the last person buys for the first. Because nobody is next to themselves, nobody can draw their own name, and everybody gives exactly one gift and receives exactly one.
Step 1: List the names
Type the names in A2 downwards, one per row. The examples use six people in A2:A7. If your group is bigger, change every 7 in the formulas to your last row.
Step 2: Shuffle and assign
| Step | Excel (Microsoft 365, Excel 2021 or later) | Google Sheets |
|---|---|---|
| Shuffle the names into D2 | =SORTBY(A2:A7,RANDARRAY(ROWS(A2:A7))) | =SORT(A2:A7,RANDARRAY(ROWS(A2:A7)),TRUE) |
| Who each person buys for (E2, fill down to E7) | =INDEX($D$2:$D$7,MOD(ROW()-ROW($D$2)+1,ROWS($D$2:$D$7))+1) | Same formula |
| Check nobody drew themselves | =SUMPRODUCT(--(D2:D7=E2:E7)) should be 0 | Same formula |
| Check everyone receives once | =SUMPRODUCT(--(COUNTIF(E2:E7,A2:A7)=1)) should equal the number of people | Same formula |
Here is what one draw might look like:
| Row | A: Names | D: Shuffled (giver) | E: Gives a gift to |
|---|---|---|---|
| 2 | Alex | Dana | Alex |
| 3 | Bea | Alex | Fatima |
| 4 | Chris | Fatima | Chris |
| 5 | Dana | Chris | Bea |
| 6 | Eli | Bea | Eli |
| 7 | Fatima | Eli | Dana |
Read it across: Dana buys for Alex, Alex buys for Fatima, and so on until Eli, who buys for Dana. Every name appears once in each column, and nobody is matched with themselves.
Older Excel (2019 or earlier)
RANDARRAY and SORTBY are only in newer versions. Use a helper column instead:
B2: =RAND() (fill down to B7)
D2: =INDEX($A$2:$A$7,RANK(B2,$B$2:$B$7)) (fill down to D7)Column D is now the shuffled list. Use the same E2 formula as above to assign who buys for whom.
Step 3: Freeze the result
RAND and RANDARRAY are volatile: they recalculate every time the sheet changes, so your draw will reshuffle the moment anyone types anything. Once you are happy with a draw:
- Select columns D and E and copy them.
- Paste them back as values: in Excel, Home > Paste > Paste Values (or Ctrl+Alt+V, then V); in Google Sheets, Ctrl+Shift+V (Cmd+Shift+V on a Mac).
To draw again before you freeze it, press F9 in Excel, or edit any cell in Google Sheets.
Keep couples (or last year's pairs) apart
Want to stop partners drawing each other? List pairs on a sheet called Couples, with one name in column A and the other in column B. Then add a check in F2 and fill it down:
=IF(COUNTIFS(Couples!$A:$A,D2,Couples!$B:$B,E2)+COUNTIFS(Couples!$A:$A,E2,Couples!$B:$B,D2)>0,"Redraw","OK")Count the problems in one cell with =COUNTIF(F2:F7,"Redraw"). If it is above 0, redraw (F9 in Excel) until it shows 0, then freeze the result. The same trick works for "not the same person as last year": put last year's giver and receiver in the Couples sheet. With very small groups and many exclusions, there may be no valid draw, so keep exclusions to a few pairs.
Send each person their match privately
The fair way is for the organiser not to look. A few options:
- Organiser not playing: the simplest. They send each giver a private message with their name.
- Self-lookup sheet: on a second sheet, put a name in A2 and
=XLOOKUP(A2,Draw!D2:D7,Draw!E2:E7)in B2, so the organiser can check one name at a time without seeing the whole list. In older Excel use=INDEX(Draw!E2:E7,MATCH(A2,Draw!D2:D7,0)). Our XLOOKUP vs VLOOKUP guide explains both. - Mail merge: in Microsoft Word or a Google Sheets mail merge add-on, merge the giver's email and their match into a short email, so nobody sees the list.
Remember that anyone with edit access can see a shared spreadsheet, so keep the draw sheet private.
Add a budget and wish list
Add columns for a spending limit and gift ideas, and you have the whole exchange in one place. If the swap is part of your wider Christmas spending, pull the amount into a Christmas budget tracker. A drop-down column for "Bought / Wrapped / Delivered" helps the organiser chase up; see how to make a drop-down list.
Quick FAQ
Can someone draw their own name? Not with this method. Each person buys for the next name in a shuffled list, so a self-match is impossible.
Why does my draw keep changing? RAND and RANDARRAY recalculate whenever the sheet changes. Paste the result as values to freeze it.
Does it work for 3 people? Yes, for any group of 2 or more, though with 2 people each simply buys for the other.
Can two people end up buying for each other? Not with 3 or more people. The method makes one big circle, so A buys for B and B buys for someone else. If you want a draw where that can happen, use a fully random assignment instead, but then self-picks are possible.