Build a Secret Santa Spreadsheet

Christmas is here again, and while we at SpreadsheetSolving appreciate the festive spirit, our practical side always wonders “does every single friend need a gift from each of their other friends?”. And… “why can’t we just give cash?”

image

The game Secret Santa somewhat lessens this gift giving burden among a large group of people. In this game, each person is secretly assigned one other person in the group to give a gift to. Typically, when the person opens their gift, they try to guess who gave it to them. Great fun is had by all (in theory).

We can create a simple spreadsheet to set up the random assignment of people to others in a group. First we enter in a hypothetical list of names:

image

Next we’ll create a list of random numbers with the Rand() function.  Then we’ll rank the random numbers we generated from 1 to 17 using the Rank() function

image

Then we’ll use Vlookup() Function to lookup the name of the of the person whose number was randomly selected as a Giftee for each person.

image

The only thing is that there’s the chance someone gets themselves as their own Secret Santa. Let’s add a column that looks to see whether we assigned someone themselves, which would mean we would need to run the random number generator again (by hitting F9).

image

Once there is a good assignment, just copy the table and paste values to save it (or else the randomizer will re-run every time a key is pressed). One more thing, don’t forget to set a price limit!

Author: Amie

Our names are Steve and Amie. Over the past several years, we have spent countless hours with spreadsheets at various financial companies in New York, Philadelphia, and California. Both CFA charter holders, Amie studied Economics at Princeton University, and Steve majored in Electrical Engineering at Princeton University (Go Tigers!) with a certificate in Finance. Steve also has his MBA from NYU Stern.