Do you want to find out how to randomly select a winner in Microsoft Excel? Keep reading!

Think of managing an employee recognition program where you need to pick one person from a long list of top performers in Excel. You want it to be random, fair, and fast, but Excel doesn’t have a big shiny Pick Winner button.

Without the right know-how, this task can feel oddly technical and frustrating. I’ve been there myself, and I know how confusing it gets when you’re trying to combine formulas like RAND() or INDEX() without a clear guide.

That’s why I’ve broken it all down for you. Follow along, and you’ll be selecting random winners in seconds with no guesswork needed.

Using Data Analysis Toolpak

The Data Analysis Toolpak in Excel isn’t just for regression and histograms. It can also be a handy, underused tool for random selection. It includes one creative way to select a winner is by using its Random Number Generation feature to assign randomized values to each name in your list.

While it may not say Pick a Winner directly, the output it provides can easily be sorted to help you choose one entry at random. This method feels like using a controlled randomizer that’s part of Excel’s own built-in analytics suite.

What makes this method appealing is its reliability and the fact that it’s repeatable without writing a single formula. It’s ideal when you’re managing lists too long to manually scroll through, like in surveys, prize draws, or employee recognition events. Since the Data Analysis Toolpak is designed for data analysis, it adds a layer of professionalism.

Before you can start using this tool, you must enable it. The process is only required once. So, you activate Data Analysis Toolpak once and use it continuously from the Data tab of the Excel ribbon menu.

Activate the Developer tab in your Excel desktop installation if it hasn’t been activated yet. Here’s a cool Excel guide you can follow:

Now, navigate to the Developer tab and click on the Excel Add-ins command in the Add-ins group.

The Add-ins dialog box will open. You should see the Analysis ToolPak – VBA checkbox inside the dialog box. Check the checkbox to activate this Excel data analysis tool.

Click OK on the Add-ins dialog box to save the changes you’ve made.

Now, go to the Data tab, and you should find the Data Analysis command inside the Analysis group.

Do you see the Data Analysis button? Perfect! You can now proceed with the following steps to randomly select a winner.

Navigate to the worksheet where the names are listed that you need to rank randomly.

Click on the Data Analysis button in the Data tab.

The Data Analysis dialog box will show up. Scroll down the list, find the Random Number Generation tool, and click OK.

A new configuration window will pop up where you can define how many random numbers you want to generate.

The name of the configuration window is Random Number Generation. It has the following fields that you need to fill in according to the given instructions below:

• Number of Variables: Should be 1.
• Number of Random Numbers: It depends on how many names or objects you’re ranking. In this tutorial, it’s 7 since there are 7 names.
• Distribution: I selected Uniform in the exercise.
• Parameters: It depends on the types of random values you want to generate. Put 0 and 1 in the Between and field to get random numbers from 0 to 1.
• Output Range: Click on the field and select the blank cell range to the immediate right of the source data, for example, names. It’s B2:B8 in the current exercise.

Find below the definitions of the distributions available in the Random Number Generation tool. These definitions will help you to decide which distribution you must use:

• Uniform: Generates numbers evenly distributed between a minimum and maximum value, meaning every number in the range has an equal chance of appearing.
• Normal: Produces numbers based on a bell curve, where most values cluster around the mean with fewer values at the extremes.
• Bernoulli: Returns only 0s and 1s, simulating a yes/no or success/failure outcome with a given probability.
• Binomial: Outputs whole numbers based on a fixed number of trials and a probability of success for each trial, useful for modeling repeated yes/no events.
• Poisson: Generates numbers that represent the number of events happening in a fixed interval when events occur at a known constant rate.
• Patterned: Produces numbers in a repeating sequence that cycles through a set pattern you define, not truly random but structured.
• Discrete: Allows you to define specific values and the probability for each, and it randomly selects from that list based on the provided probabilities.

Once you’re done configuring the Random Number Generation tool, click OK.

A range of random numbers will be generated in the designated cell range. Let’s say it’s B2:B8.

Select B1 and press Ctrl + Shift + L to activate the Sort & Filter tool.

Click on the Sort & Filter drop-down arrow and select Sort Largest to Smallest sorting criteria from the context menu.

Excel will instantly sort the source list according to the random number values generated in the right-hand column.

This is how you can randomly select a winner in Excel using the Data Analysis Toolpak command.

Using the RAND() and SORT() Functions

If you’re looking for a formula-based solution to randomly select a winner in Excel, you can use the RAND and SORT functions.

Firstly, you’ll need to use the RAND function to generate a random decimal number series in the adjacent column of the source dataset.

Then, you can use the SORT function to sort the random numbers from the largest to the smallest. The largest number is the random winner from the source list of names, objects, or anything else you want to rank.

Let me show the process step by step below:

Suppose the list of names or competitors you must rank looks like the one shown above. There are two columns. One is for the names or participants, and the other is for ranking them. In this exercise, these are the Inventors and Rank columns.

Go to the first cell in the Rank column and enter the following formula into it:

 =RAND()

Hit Enter to calculate the cell.

Now, select this cell and click on the fill handle. Drag the fill handle down until the cell where data exists in the adjacent cell on the left, and release the mouse click.

Excel will replicate the formula in the rest of the cells in your source data column.

So, you have created a random ranking for all the names or participants in the source dataset.

Now, select the empty cell, cell C2 in column C, to the right of the rank values in column B. Now, enter the following formula into this cell:

=SORT(B2:B8,2,-1) 

Hit Enter.

The SORT function will sort the values in column B in descending order.

The first rank in column C, cell C2, is the winner. In this exercise, it’s Richard Arkwright, and the rank is 0.67874. This is the random winner.

Using the SORTBY() and RANDARRAY() Functions

In the previous formula, you assigned random values to a list of participants and then sorted the values from largest to smallest to figure out who the random winner.

However, if you want a method where you only want to select the winner from a list in one click, you can use this formula-based method.

This method involves three functions. These are SORTBY, RANDARRAY, and INDEX. Firstly, you’ll use the RANDARRAY and ROWS functions to create an array of random integers or decimal values. Then, you’ll use the SORTBY function to sort the array from top to bottom. Finally, the INDEX function will help you choose one random winner from the list.

To practice this method, navigate to the source data worksheet and select a cell where you wish to populate the random winner’s name from a list of names or participants.

Now, enter the following formula into the cell:

=INDEX(SORTBY(A2:A8, RANDARRAY(ROWS(A2:A8))), 1)

Here’s how you should edit the above formula before using:

• A2:A8: This cell range tells the formula where the names are available in the worksheet. So, change this cell range according to your own dataset.

Hit Enter to calculate the cell.

That’s it! You’ve got your random winner.

Now, if you wish to change the random winner on every click, click on the Calculate Now command in the Calculation block of the Formulas tab.

Using Power Query

If you’re selecting a random winner from a huge list of participants, loading the entire dataset in an Excel worksheet isn’t a good option. It’ll reduce the performance of the workbook. Instead, you can use Power Query. Here, the external data will be imported into the Power Query tool, and only the top selections will be imported into the active worksheet.

You can add data to Power Query in two ways. Firstly, go to the Data Tab and click on the Get Data command to expand the data connectors context menu.

There, hover the cursor over a data connector source, like From Azure, and then click on the secondary data connectors, like From Azure SQL Database option in the overflow menu. Now, follow the onscreen instructions to get data in Power Query.

If you wish to import data from the active worksheet, select the source data and click on the From Table/Range command in the Data tab.

Click OK on the Create Table dialog box to complete importing data to Power Query.

Once you have imported data into Power Query, it’ll look like a dataset as shown above.

Navigate to the Add Column tab and click on the Custom Column command.

The Custom Column wizard will open.

Type a column name in the New column name field.

Now, copy and paste the following formula inside the Custom column formula field:

=Number.RandomBetween(0, 999999)

Click OK to create the new column.

The Random column will be created showing random decimal values.

Click on the drop-down arrow in the top right corner of the Random column to access the context menu.

Select the Sort Descending option in the context menu.

Power Query will sort both the Inventors and Random columns. This is the step when you have successfully selected a random winner in Power Query.

Click on the File tab and select Close & Load To in the context menu.

The Import Data dialog box will show up. Select the Existing worksheet option and click on the cell where you wish to import the sorted data from Power Query.

Click OK to import.

You now have the list of random winners in your worksheet.

To refresh the winner’s name for a new dataset using the same query connection, right-click on the Table1 property in the Queries & Connections panel and select Refresh in the context menu.

The list of winners will again be refreshed or changed randomly.

Conclusions

Now you know how to select a random winner in Excel using Data Analysis Toolpak, Excel functions, and Power Query.

You can choose one or more methods from here and practice those on your dataset to master this skill.

If you liked the Excel guide, use the comment box to share your suggestions. Don’t forget to share this Excel tutorial with your colleagues and friends as well.