Excel: Randomly Select Names from a List

One of the main elements of statistical analysis is making sure your data is a good representation of the population. The best way to do that is to make sure your sampled data is truly randomly selected.

I am going to show a quick method in Excel where we can select random names from a list (our population) of names. We will be using the following Excel functions to do so (if you are not familiar with them, don’t worry, I will walk you through it):

  • index()
  • randbetween()

We will also be creating a Named Range.

The List

Let’s start with the list. I have 14 names, and I want to randomly select 5 names from this list.



Index is a method for using the looking up elements in Excel using the indexes (row numbers, column numbers). Note the index is always a number, so when dealing with columns, don’t be tricked by the column letters assigned.

Here I am looking for the 5th index in my list of names



My result is Steve


You can also work horizontally. Here I am looking for index 3 from my horizontal list.


Result is d


Now watch this. I am asking for the 4th index in this list. What do you think it will be? The 4th row is Tyrone.


Nope, when running INDEX() the index starts at 1 – starting at the first element in the selected list. If you start counting with Gary, Karen is our 4th person down.



RANDBETWEEN accepts 2 arguments, high and low numbers in a range. It then returns a random integer from with the range.

RANDBETWEEN(1,20) will return an integer from 1 to 20 (including 1 and 20)

Named Range

Now before me move forward, when formulas start to get larger (as they are about to) I look for ways to simplify the syntax.  One trick would be to name our range.

Highlight all the names in your list, then go up to the name box and enter Names


Now combine RANDBETWEEN() and INDEX()

now try this: =INDEX(Names, RANDBETWEEN(1,14))


Hit enter and drag the formula down 5 cells


Now you have 5 random names selected from your list.


Excel: Range Names

In Excel, you can name your cells and ranges of cells with more user friendly names. This comes in handy as your spreadsheets become more complex. It also comes in handy when you are sharing spreadsheets between co-workers.

File download available here: checkbook

This file is simple enough. I have an example checkbook with monthly salary and monthly expenses listed.


Select cell containing the monthly salary(B3). Now go up to the cell name box (circled in red)


Type a new name in the box – I used “Income”


You can do the same thing with a range. Select cells D3- D5, then rename it “Expenses”


If you check the drop down from the name box, you will see your new named cells in the drop down.


Let’s uses our new named cells (and ranges) to make a quick formula.




To see a video of this lesson, click the link here: Excel: Name Ranges