Do you want to organize a Secret Santa swap with your friends but you don't know how to get everyone a partner quickly? Well one of the coolest way is to make a randomiser Secret Santa machine using Google Sheets. If you are a softy for Christmas while also being a big tech head, then this article is for you! I would use Google sheets to program the lights on my Christmas tree if I was able to write it as a spreadsheet.
In case you didn't know, Secret Santa gift exchanges are when a group of people get together and randomly draw names from a hat. Each person is then supposed to get a gift for your respective person, but you can't tell let them find out it is you. You get to become the "Secret Santa" for your group member.
Nowadays, it can be hard to get everyone together under one roof, especially as friend groups get bigger and people become more busy. That is why I figured out a way to run Secret Santa online using Google Sheets.
In case you are unfamiliar with some of the concepts we discuss in this article, there are links to each concept you will need to understand if you don't already. You can use this walkthrough to generate a free Secret Santa name generator using the power of google sheets.
Step #1: Establishing the Names
You will need two columns, one for the names and one for the assigned partners. This is as simple as running a simple copy and paste, and will be randomized by the Secret Santa generator.
Step #2: Randomize Range
To randomize the range, simply select "Data" and then click "Randomize Range".
That will rearrange and "shuffle" the cells so now each name should have a unique assignee. You can use this to ensure that you can create a random Secret Santa generator. But the question remains, what if you are assigned to get a gift for yourself?
Step #3: Highlight the Duplicates
Thanks to Conditional Formatting, you will be able to use custom formatting to highlight duplicates. Follow these quick and easy steps to make sure you only draw other names.
- Access Conditional Formatting (Format -> Conditional Formatting)
- Apply the range of the second column
- Select "Custom Formula is"
- Enter Conditional equation (=$D:$D=$C:$C). Essentially this means that for any given row, if the cell in column D equals the Cell in Column C, the formatting will take place.
- Select a color; this color will appear if the custom formula is activated.
- Select "Done"
From there, you will be able to see if your shuffle left behind any duplicates. If that is the case, simply rearrange and sort the cells until there are no highlighted cells.
Step #4: Map Out the Automated Messages
Here is where we get a little deeper into it. Let's say you have a super long list and you don't want to worry about sending a bunch of people essentially the same message. That is where the CONCATENATE function comes in to work as a sort of Secret Santa generator text automation tool.
Bonus Step: Broadcast the Messages via SMS
If you want to, you can use a sheets add-on to send SMS messages depending on cell values. I use TextBlaster, but I don't really have a favorite, as I don't use a lot of sheets extensions. To find those, simply Click Extensions->Add-ons->Get add-ons.
And there you have it, that is how you set up a simple Secret Santa generator that is automated by the power of Google Sheets! Merry Christmas and Happy New Year from us at 8020 Sheets!