How to Select Random Names from a List in Excel

Microsoft Excel, and other similar tools, is such a powerhouse when it comes to creating spreadsheets. Its virtually endless number of entries and cells make Microsoft Excel the primary tool of, well, just about anyone working in an office setting.

But did you know that Microsoft Excel could be used to select random names from a list? Well, now you do know, and it can do it in two different ways.

  • The RAND function can be used to generate a number between 0 and 1. Why? For starters, for the sake of simplicity, but mainly the long string of numbers that 0 and 1 occupy, specifically the decimal places.
  • The other method is using RAND Between. It’s similar to the RAND function, but the outcome will be different, which you’ll see why in a short moment.

Microsoft Excel doesn’t come equipped with a dedicated button for choosing names at random from a list you’ve created. Instead, you have to use formulas to get results, hence the use of RAND and or RAND between.

So, let’s get started.

Select Random Names from a List with RAND

Using the RAND function, as mentioned before, generates a number between 0 and 1, and then assigns each name with a number between those two. As you will see shortly, this can create some wildly long numbers, considering decimals are involved. But do not worry; you won’t be staring at place values trying to figure out who has the biggest number.

RAND is, arguably, the better of the two if you are looking to raffle off tickets. Because there are so many decimal places involved, you are less likely to run into the same number.

1. Start by launching Microsoft Excel. Visit the location it has been placed and double click to launch Microsoft Excel. Doing so will always open Excel to a blank book.

2. Next, click File in the top left corner of the program window. In the drop down menu, click Open and double click on the Microsoft Excel document you would like to open.

[NOTE]: You can also open the document from the Recent tab.

list of names

3. If you haven’t already, start by creating your list of names. For the RAND function to work, you’ll need a second column across from your list to create the randomly generated numbers. Use the image above for a rule of thumb.

=rand()

4. Now, in the column where you’ll be generating numbers, click on the first cell at the top of that column. Type “=rand(),” without quotations, and hit the Enter key. This will generate a number between 0 and 1.

random number

5. Take your cursor and double click on the little square in the lower right corner of the cell containing your first random number. This will assign a random to every person in the list.

You are welcome to leave it as is, but messing with the cells can and will change the value of the generated number. If you want to keep them static, continue with the steps.

column containing the generated numbers

6. Highlight the column containing the generated numbers. Now, hover on the right hand border and right click and hold. Drag the border to the next column, then move it back and let go of the right click mouse button. Doing this will produce a drop down menu.

Copy Here as Values Only

7. In the drop down menu, click on “Copy Here as Values Only.” This will make your generated numbers static, unchanging and unchanging.

Sort Smallest to Largest or Sort Largest to Smallest

8. Time to sort them. Click on the Data tab at the top of the ribbon. Click on the first generated number. In the Data tab, locate the “Sort & Filter” section. Within that box you can choose Sort or Sort Smallest to Largest or Sort Largest to Smallest. Choose what works for your document.

Select Random Names from a List with RAND Between

What if you don’t want to mess around with random numbers? If you would rather have Microsoft Excel spit out a random name from your list, then you can do that, too. This method is called “RAND Between” or the “=index” function.

This method is great for choosing a name at random, and have Excel do the work. It will give you the name rather than examining randomly generated numbers.

Here’s what you do:

1. Start by launching Microsoft Excel. Visit the location it has been placed and double click to launch Microsoft Excel. Doing so will always open Excel to a blank book.

2. Next, click File in the top left corner of the program window. In the drop down menu, click Open and double click on the Microsoft Excel document you would like to open.

[NOTE]: You can also open the document from the Recent tab.

list of names

3. If you haven’t already, start by creating your list of names. For the RAND Between function to work, you’ll need a separate cell that the random name chosen will appear in. Use the image above for a rule of thumb.

=index(

4. In the cell where the random name will appear, start typing “=index(,” without quotations, but do NOT press the Enter key yet.

=index(A2:A6

5. After the first parenthesis, you typed, input the array. The “array” is going to be the cells containing your list of names. In the example above, cells A2 through A6 are considered the “array” in the function. Your function should now look like this: =index(A2:A6,

row number

6. Now you need the row number. This is going to be the name that’s chosen at random by Microsoft Excel. With the “randbetween” function, you can specify the cells that will be chosen. In the example above, 2 through 6 will be used. Your function will now look like this: =index(A2:A6,RANDBETWEEN(2, 6))

[NOTE]: Yes, you do have to include two parentheses at the end. That is not a typo.

=index(A2:A6,RANDBETWEEN(2,6))

7. If the function you typed looks like this “=index(A2:A6,RANDBETWEEN(2,6)),” without quotations, then you can finally hit the Enter key.

Bottom Line

Microsoft Excel may not have a native button for randomly selecting names from a list, but its functions make up for what’s it’s missing. And in doing so, provides two great functions that can work for different situations.