Md. Shamim Reza
This article illustrates how to randomly select a winner in excel. Assume you have thousands of customer information with unique order IDs. Your company has decided to randomly select a customer each week and give a special discount offer. If you are wondering how to do that then you can follow the article. We will discuss 2 very simple methods to select a random winner in excel. Hopefully, this will be helpful for you.
Download Practice Workbook
You can download the practice workbook from the download button below.
2 Simple Ways to Randomly Select a Winner in Excel
Assume you have the following dataset. Now you want to select a random winner from them. You can easily do that in excel by following the methods below.
1. Select a Random Winner Using Formula
First off, we will see the use of INDEX, RANDBETWEEN, and ROWS functions combined in a formula to choose a random winner. Follow just the steps below.
Steps:
- You need to enter the following formula in cell G5 to retrieve a random unique ID from the list to select the winner. Then you can apply the VLOOKUP formula to retrieve other information associated with that ID.
- Take a note of the winner to notify him/her. Then you can press F9 to find a new winner.
=INDEX(B5:E14,RANDBETWEEN(1,ROWS(B5:E14)),1)
- The VLOOKUP formula to retrieve the email of the winner in cell G8 is as follows.
=VLOOKUP($G$5,$B$5:$E$14,4,FALSE)How Does the Formula Work?
➤ ROWS(B5:E14)
The ROWS function returns the number of rows in a reference or array.
Output: 10
➤ RANDBETWEEN(1,10)
The RANDBETWEEN function returns a random number between two specified numbers.
Output: 7
➤ INDEX(B5:E14,7,1)
The INDEX function returns the value or reference of the cell at the intersection of a particular row and column, in a given range.
Output: “RD22A007”
Read More: Random Selection Based on Criteria in Excel (3 Cases)
2. Use RAND Function to Select a Winner Randomly
You can also select a random winner using the RAND function in excel.
Steps:
- First, apply the following formula in cell F5. Then drag the fill handle icon below or double-click on it to copy down the formula. After that, you will see a list of random decimal numbers.
=RAND()
- Next, select any of the random numbers or the one at the top of the list. Now, press ALT+H+S+S to sort them.
- You will find a random ID and associated information at the top of your dataset each time you use the shortcut.
- Finally, take a note of the ID and associated information to notify the winner.
Read More: How to Randomly Select from a List in Excel (5 Swift Methods)
Things to Remember
- Don’t forget to take note of the winner immediately so that you can notify him/her. As the formulas contain volatile functions, the result will change any time a change occurs in the worksheet.
- You can use the keyboard shortcut to refresh the sheet. Alternatively, you can add a refresh button for that using VBA.
Conclusion
Now you know how to randomly select a winner in excel. Which method do you prefer? Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.
Related Articles
- How to Generate a Random String from a List in Excel (5 Suitable Ways)
- Excel VBA: Random Selection from List (3 Examples)
- How to Freeze Random Selection in Excel
- Select Random Sample in Excel (4 Methods)
- Random Selection from List with No Duplicates in Excel (5 Cases)
- Select a Random Sample from a Population in Excel (3 Ways)
- How to Randomly Select Rows in Excel (2 Ways)
Very cool. If we want to have 3 winners — say 1st prize, 2nd prize, 3rd prize — how could the chosen winner be removed or excluded so he isn’t selected again? Thanks!
Dear PA,
Thanks for your appreciation and valuable question. If you want to have 3 winners without repetition, you can follow the following procedure.
Create a column with random values using the following formula.
=RAND()
Then, apply the formula mentioned below in another column to rank the random values.
=RANK(D5,D5:D14)
Now, Insert the following formula where you want to have the winners’ names.
=XLOOKUP(C17:C19,E5:E14,C5:C14)
and press Enter to have multiple winners.
I hope this is what you are looking for.
Best regards,
Naimul Hasan Arif