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.


How to Randomly Select a Winner in Excel: 2 Simple Ways

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”


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


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.

Download Practice Workbook

You can download the practice workbook from the download button below.


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. Stay with us and keep learning.


<< Go Back to Random Selection in Excel | Randomize in Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

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