How to Randomly Select a Winner in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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)

how to randomly select a winner in excel

  • 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.

how to randomly select a winner in excel

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

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

2 Comments
  1. 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!

    • Reply Avatar photo
      Naimul Hasan Arif May 2, 2023 at 4:26 PM

      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo