rand is used to generate a number between 0 and 1randbetween generates a whole number within a given range
However, have you ever thought of selecting a random name or a random string from a given list? There could be various scenarios where you might need that. For instance, the editor of this blog plans to run giveaways soon. Excel can help him choose the winners in that case. However, Microsoft Excel provides no direct formula to get that done. We have explored a few ways where we can apply some logic and combine a few formulas to get the task done. And, we will show you two such methods. Cool Tip: Looking for a way to start writing on a new line in the same cell in MS Excel? Try the keys Alt + Enter.
Method 1
Step 1: Fill the list of names that you have, in any column without worrying about the order. In our example we have used column A. Also take note of the first and last row numbers. Here, they are 1 and 13. Step 2: Switch to any arbitrary cell and type in the formula shown below. =INDIRECT(CONCATENATE(“A”, RANDBETWEEN(1, 13))) Step 3: Hit Enter and you would be able to see the first random name. Whenever you want a new random name just press F9. A should be replaced with the column that you have used and 1 and 13 should be replaced with the first and last row numbers respectively
Method 2
Step 1: Fill the list of names that you have in any column without worrying about the order. Step 2: Block the area (the cells that are filled with names) and give it a name as shown in the diagram below. Take note of the box where I have written NameList. That’s the name I have assigned to the list. Step 3: Switch to any arbitrary cell and use either of the formulas shown below. =INDEX(A1:A13, RANDBETWEEN(1, 13)) or =INDEX(NameList, RANDBETWEEN(1, 13)) Hit F9 whenever you want to refresh the randomly generated name. If you want two names to be generated at once, write the same formula in two different cells. Likewise you may increase the number to 3, 4 and so on. A should be replaced with the column that you have 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 The advantage of using the last formula is that your list and formula do not have to be on the same sheet. Meaning, the list may be on Sheet 1 and the formula on Sheet 2. That way no one will be able to see what list the random name is being generated from.
Conclusion
So, that was how you can pick one or more random names from a list using Excel. Can you think of any other formula that would render the same result? If yes, do share with us. We’d be happy to learn. Top Image Credit: Alan Cleaver
