How to Randomly Select a Winner in Excel (2 Methods)

Dataset Overview

Let’s assume we have the following dataset. Now we want to select a random winner from the list.

Method 1 – Select a Random Winner Using Formulas

  • Retrieve a Random Unique ID
    • In cell G5, enter the following formula to retrieve a random unique ID from your dataset:

how to randomly select a winner in excel

How Does the Formula Work?

➤ ROWS(B5:E14)
The ROWS function returns the number of rows in a reference or array.
Output: 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”

  • Retrieve Other Information Associated with the Winner:
    • To retrieve additional information (e.g., email) associated with the winner, use the VLOOKUP formula.
    • In cell G8, enter:

Replace with the appropriate column index for the desired information.

  • Select a New Winner
    • Take note of the winner’s details and notify them.
    • To choose a new winner, press F9 to recalculate the formulas.

Method 2 – Use the RAND Function

  • Generate Random Decimal Numbers
    • In cell F5, enter the formula:

    • Drag the fill handle down to copy the formula to other cells.
    • This creates a list of random decimal numbers.
  • Select a Winner
    • Choose any random number from the list or select the top one.
    • Press ALT+H+S+S to sort the numbers.
    • The associated ID and information will be at the top of your dataset.

how to randomly select a winner in excel

Things to Remember

  • Note down the winner immediately to notify them.
  • As these formulas use volatile functions, results will change whenever the worksheet is modified.
  • You can refresh the sheet using the keyboard shortcut or add a refresh button using VBA.

Download Practice Workbook

You can download the practice workbook from here:

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

Get FREE Advanced Excel Exercises with Solutions!
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

  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.

      Then, apply the formula mentioned below in another column to rank the random values.

      Now, Insert the following formula where you want to have the winners’ names.

      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