How to pick a random name from a list in Excel

Whether you’re choosing an employee of the month, picking giveaway winners, or assigning tasks, Excel is the perfect tool for a bias-free draw. These simple formulas allow you to generate a single result or a full list of unique selections in just a few steps.

All the examples in this guide assume you have a list of names in a column titled Names within an Excel table renamed T_Names. To follow along, select your list, press Ctrl+T, and ensure “My table has headers” is checked. Then, head to the Table Design tab and enter T_Names in the Table Name field.

Method 1: The one-winner classic

To pick a random name from an Excel table using a method that works across every version of Excel, this classic combination is the industry standard.

The formula looks like this:

=INDEX(T_Names[Names],RANDBETWEEN(1,COUNTA(T_Names[Names])))

How this formula works

This formula uses three functions: COUNTA, RANDBETWEEN, and INDEX. Here’s how they interact:

1. COUNTA counts the number of non-empty cells in the Names column of your table.
2. RANDBETWEEN generates a random whole number between 1 and the total count provided by COUNTA.
3. INDEX looks at the Names column and retrieves the value at the row number generated by the randomizer.

Because you’re using an Excel table, this formula is fully dynamic. In other words, as you add or delete names from the list, the formula automatically adjusts its range. To re-roll the winner, press F9 to recalculate the sheet.

Important points to note

Before you go ahead and use this method, there are two points you should note:

1. Volatility: This is a volatile formula, meaning it will pick a new winner every time you enter data or change anything else in your spreadsheet.
2. Static winners: To lock in the winner so that the name doesn’t change, copy the cell and paste the content as a value.

Related

3 Ways to Stop Volatile Functions Recalculating in Microsoft Excel

Fix the values generated by volatile functions.

Method 2: The modern single pick

For those using Microsoft 365, Excel for the web, one-off versions of Excel released in 2024 or later, or the most up-to-date versions of the mobile or tablet app, this is a cleaner, more intuitive method to randomly pick an item from a list. It’s built for Excel’s modern engine and is easier to read because the function name explicitly describes its behavior.

Here’s the formula you’ll need to use:

=CHOOSEROWS(T_Names[Names],RANDBETWEEN(1,ROWS(T_Names[Names])))

How this formula works

Like in Method 1, this formula uses three functions: ROWS, RANDBETWEEN, and CHOOSEROWS:

1. ROWS calculates the total number of rows in the Names column of the T_Names table.
2. RANDBETWEEN selects a random whole number between 1 and that total row count.
3. CHOOSEROWS targets the Names column and extracts the row corresponding to the random number.

This approach is the best practice for modern users who only need one winner. By using the structured table reference, you eliminate the risk that the formula will break if you move the data or add hundreds of new entries to the list. After you enter the formula, press F9 to choose a new winner.

Related

Don’t Ignore the Power of F9 in Microsoft Excel

Recalculate and fix your Excel formulas using only your keyboard.

Important points to note

This is a great method for picking a random item from your Excel list, but note these two points first:

1. Compatibility: This formula requires modern versions of Excel. Those using Excel 2021 or earlier should use Method 1 instead.
2. Volatility and freezing: Like most random functions in Excel, this result will change every time the spreadsheet recalculates. To freeze your winner, copy the cell, then use “Paste as Values” to turn the formula into static text.

Method 3: The multi-winner shuffle

If you need to pick multiple winners from your Excel list for a giveaway, using the previous methods multiple times might result in the same person winning more than once. To ensure a fair draw, you need to shuffle the list and take the top results. This method also ensures that even if a name is entered into your table twice, it can only win once.

Here’s the formula to use:

=TAKE(SORTBY(UNIQUE(T_Names[Names]),RANDARRAY(ROWS(UNIQUE(T_Names[Names])))),3)

How this formula works

This is a robust formula, mainly because it uses the correct combination of functions: UNIQUE, RANDARRAY, ROWS, SORTBY, and TAKE:

1. UNIQUE creates a filtered version of your list, removing any duplicates so every person has exactly one “ticket” in the draw.
2. RANDARRAY and ROWS work together to generate a list of random decimals that matches the length of your unique list.
3. SORTBY reorders the unique names based on those random decimals, essentially shuffling the deck.
4. TAKE extracts the top three names from the shuffled results.

Because this method shuffles the names rather than picking row numbers, it’s the most robust way to handle giveaways. To change the number of winners, simply replace the 3 at the end of the formula with your desired count. As always, press F9 to re-run the machine.

Important points to note

Here are two points you should bear in mind if you use this method:

1. Spill range: This is a dynamic array formula, meaning it needs empty cells below it to spill the results. Otherwise, you’ll see a #SPILL! error.
2. Compatibility: This method requires Microsoft 365, Excel for the web, one-off versions of Excel released in 2024 or later, or the most up-to-date versions of the mobile or tablet app.
3. Volatility: The results of this formula will refresh whenever you edit the workbook, so remember to use “Paste as Values” once you have your final picks.

Related

Stop removing duplicates in Excel: The UNIQUE function is safer and dynamic

Upgrade from static snapshots to a live-updating system that handles complex repeats with ease.

Which method should you use?

Here’s a quick table to help you choose the right method for picking one or more random items from a list in Excel:

Your aim

Method to use

Excel version compatibility

Key benefits

Pick one winner in all versions of Excel

Method 1

All versions

It works for everyone, including those using older versions of Microsoft Excel.

Pick one winner using modern, readable logic

Method 2

Microsoft 365, Excel for the web, Excel 2024+, and the Excel mobile and tablet app.

It uses a clean syntax that is easier to read and troubleshoot.

Pick multiple unique winners

Method 3

Microsoft 365, Excel for the web, Excel 2024+, and the Excel mobile and tablet app.

This formula natively prevents duplicates.

If you don’t want to use one of the three retrieval formulas, you can perform a one-time random draw manually. Add a column next to your names and type =RAND(). After pressing Enter, sort the table by that random column, and you can then use the name at the top of the table as your winner.

Whether you need the universal compatibility of the INDEX method or the built-in duplicate prevention of the SORTBY shuffle, Excel makes picking winners effortless. That said, if you find that picking a few names isn’t enough, and you actually need to reshuffle your entire dataset, it’s just as easy to randomize rows in an Excel table to reorganize your data in seconds.

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.