Generating a random number using MS Excel is very easy. It has two built-in functions called rand and ranbetween to get you through.
- Rand is used to generate a number between 0 and 1
- randbetween generates an integer within a given range
However, have you ever thought about choosing a random name or a random string from a given list? There can be many different situations where you might need that. For example, the editor of this blog plans to run giveaways soon. Excel can help him pick the winner in that case.
However, Microsoft Excel provides no direct formula to do that.
We have explored several ways in which we can apply some logic and combine several formulas to complete the task. And, we will show you two such methods.
Cool Tip: Looking to start writing on a new line in the same cell in MS Excel? Try Alt + Enter keys.
Method 1
Step 1: Fill in the list of names you have, in any column, regardless of the order. In our example, we used column A. Also note the first and last row numbers. Here, they are 1 and 13.
Step 2: Switch to any arbitrary cell and type the formula as shown below.
=INDIRECT(CONCATENATE(“A”, RANDBETWEEN(1, 13)))
Where,
A should be replaced with the column you used, and
1 and 13 should be replaced with the first and last row numbers respectively
Step 3: Hit Enter and you should be able to see the first random name. Whenever you want a new random name just press F9.
Method 2
Step 1: Fill the list of names you have in any column regardless of the order.
Step 2: Block the area (the cells are filled with names) and give it a name as shown in the diagram below. Make a note of the cell where I wrote the NameList. That’s the name I assigned to the list.
Step 3: Switch to any arbitrary cell and use one of the formulas shown below.
=INDEX(A1:A13, RANDBETWEEN(1, 13)) or
=INDEX(NameList, RANDBETWEEN(1, 13))
Where,
A should be replaced with the column you used
1 and 13 should be replaced with the first and last row numbers respectively and
NameList should be replaced with the name you gave your list in Step 2
Fight F9 whenever you want to refresh the randomly generated name. If you want to create two names at once, write the same formula in two different cells. Likewise, you can increase the number to 3, 4, etc.
The advantage of using a final formula is that your list and formula don’t have to be on the same worksheet. That is, the list can be on Sheet 1 and the formula on Sheet 2. That way, no one can tell from which list the random names are being generated.
Conclusion
So that’s how you can select one or more random names from a list using Excel. Can you think of any other formulas that would give the same result? If yes, please share with us. We are happy to learn.
Top image credit: Alan Cleaver
Categories: How to
Source: thpttranhungdao.edu.vn/en/
