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 this article is for you!

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.

Step #1: Establishing the Names

SECRET Santa 1

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  

Step #2: Randomize Range

Secret Santa 2

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. But the question remains, what if you are assigned to get a gift for yourself?

Step #3: Highlight the Duplicates

Secret Santa 3

Thanks to Conditional Formatting, you will be able to use custom formatting to highlight duplicates. Follow these quick and easy steps

  1. Access Conditional Formatting (Format -> Conditional Formatting)
  2. Apply the range of the second column
  3. Select "Custom Formula is"
  4. 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.
  5. Select a color; this color will appear if the custom formula is activated.
  6. Select "Done"

From there, you will be able to see if your shuffle left behind any duplicates. If that is the case, simply rearrange the cells until there are no highlighted cells.

Step #4: Map Out the Automated Messages 

Secret Santa 4

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. 

Secret Santa Final
From here you can simply copy and paste the message and share that message with each member of the exchange without jeopardizing the secret of the Santa. 

Bonus Step: Broadcast the Messages via SMS

Secret Santa 5

If you want to, you can use a sheets add-on to send SMS messages depending on cell values. I use TextgBlaster, 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 an automated secret santa drawing in Google Sheets! Merry Christmas and Happy New Year from us at 8020 Sheets!

Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Google Sheets Magic Tricks!

Interactive, Easy and Free Way to Learn 5 Awesomely Cool Google Sheets Tricks!