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.

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

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.

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])))

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.