Randomly Select a Name from a list in Excel – How To

By
Hasaan Fazal
-

Suppose you have a long long list of customers from which you have to select a random name. Now this is certainly not a problem if you have tens or even few hundreds of names. But imagine if you have over 85,000 names in which case you cannot hand pic one name. Just imagine the time and money it will cost to print each and every name. And in the end people still complaining that results are biased 😉

So let’s make it very straight, fair, quick and inexpensive using Excel to find the lucky name using the following steps:

Step 1: I am assuming that you have all the names in one column. Click first name and hit Ctrl+Shift+DownArrow to select the whole range. This will select all the names to be included in lucky draw.

Step 2: Go to name box and name this range whatever you desire. Suppose: exceldraw

Step3: In a separate cell put the following formula:

=INDEX(exceldraw,RANDBETWEEN(1,COUNTA(exceldraw)))

Press Enter key and the lucky name will be displayed instantly.

Formula Spell:

COUNTA(exceldraw): gives us the value of number of items in the list. It basically counts every cell that is filled and ignores the one that is empty. This way we don’t have to manually input the value of top argument in RANDBETWEEN function. Using COUNTA saves us the effort of counting the number of rows our self we just leave that on Excel to do it.

RANDBETWEEN: randomly generates a number that provides a required argument of row number in INDEX function.

INDEX: function fetches the value in the cell that is in the mentioned row.

exceldraw: the name we defined for names

So there you have a very simple way of running lucky draw using Excel. In many of my articles where I have to generate list of names I use similar approach to do it.

Do you have other ways of accomplishing it? Please share your ideas in the comments below. I am pretty sure there are lots of other ways do this

4 COMMENTS

  1. How to avoid from repeating names?

  3. Tariq Abdul Qadir Tariq Abdul Qadir

    This is very useful. If we want to draw say five times and do not want the last draw name to be considered for next draws, is it possible and how can we do it?

  4. Good but for a scenario that have 3 names, after the end of the last lucky draw name, how to put a stop function? This is because if I press F9 it continue to pick up names. I do not know which is the last lucky draw name and the end of the name

Comments are closed.