Randomly split a list
Introduction
You’ve got a list of constituents or donors and you need to randomly split that list into two groups. As long as your list contains less than 1,048,576 rows, Excel is a great tool for this. Our approach uses a helper column and the rand() function to get the job done.
Tip
Follow along with this sample Excel file.
1. Add a helper column
We are going to use rand() to generate a random number for each row in the list, and we need a column to hold this random number. To the right of your existing data, add a column and give it any header value you want (we will use “Random” in this example):
2. Use rand() to generate random numbers
In the first row of our helper column (B2 in this example), enter =rand() and press enter. This will generate a random number between 0 and 1 in cell B2. Then, fill that formula down to the bottom of your list:
3. Sort by our helper column
Now that we have random numbers in our helper column, we can sort our data by that column. This will cause all of the records in our data to be sorted randomly.
Note
Every time the rand() function is evaluated, the number it returns changes. When you sort your data, Excel will re-evaluate all the rand() calls and the random numbers in your sheet will change. This is expected and not a problem.
I can’t find the sort button!
4. Divide the list in half
Now that our list is randomly sorted, we can cut and paste half of the records into a new worksheet. Create a second tab in your Excel document (or open a second Excel document) and copy over your header row. Then simply cut and paste, and you’re done!