Function Problems

How to Randomly Select and Remove Names From a List in Excel

Phi Hung Vo Oct 8, 2026 871 views

Question details

The user wants to randomly pick a name from a list in Excel and automatically generate a new list that excludes the selected name.

How to Randomly Select and Remove Names From a List in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating a randomized draw or allocation system where previously selected names must be excluded from subsequent rounds.
Observed behavior: The user needs a formula-based method to dynamically extract a random name and output the remaining names without the drawn one.

Before you start

Ensure that your version of Excel supports dynamic array functions such as FILTER, which are required for the easiest formula-based method.

Solution 1Recommended

Use INDEX, RANDBETWEEN, and FILTER Functions

This is the most efficient and recommended formula-based method, utilizing dynamic array formulas to randomly select a name and instantly generate the remaining list.

This method requires a version of Excel that supports dynamic arrays (like Microsoft 365 or Excel 2021). It uses two separate formulas: one to draw the name and another to filter it out.

Select a Random Name

Click on an empty cell (for example, B2). Enter the formula =INDEX(A2:A33,RANDBETWEEN(1,COUNTA(A2:A33))) and press Enter. This will randomly select one name from your list in A2:A33.

Create the New List

Click on another empty cell where you want the new list to start (for example, C2). Enter the formula =FILTER(A2:A33,A2:A33<>B2) and press Enter. This generates an array of the remaining names.

Lock the Selected Name (Optional)

Because RANDBETWEEN recalculates whenever the sheet updates, copy the selected name in B2 and paste it as 'Values' to lock it in place.

Use INDEX, RANDBETWEEN, and FILTER Functions

Tip: You can repeat this process for subsequent rounds by referencing the newly generated list instead of the original one.

Solution 2

Use a Helper Column with the RAND Function

If your Excel version does not support the FILTER function, you can use a helper column to randomize the entire list and manually remove names.

Easily manage lists with WPS Spreadsheet

Perform Random Selections and Manage Data Effortlessly in WPS Office

WPS Spreadsheet provides powerful data processing capabilities, fully supporting dynamic array formulas like FILTER and INDEX. It's the perfect tool for creating randomized lists, draws, and managing tabular data with high efficiency.

1. Open Your Workbook: Launch WPS Spreadsheet and open the document containing your list of names.
2. Enter the Random Selection Formula: In an empty cell, type =INDEX(A2:A33,RANDBETWEEN(1,COUNTA(A2:A33))) to automatically draw a random name.
3. Filter Out the Selected Name: In a different cell, type =FILTER(A2:A33,A2:A33<>B2) (assuming B2 holds the drawn name) to dynamically output the rest of the list.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Supports advanced dynamic array functions like FILTER, UNIQUE, and SORTLightweight, fast, and completely free to useFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does the randomly selected name change every time I edit the worksheet?

Functions like RAND and RANDBETWEEN are volatile, meaning they recalculate every time any change is made to the worksheet. To keep a selected name fixed, you must copy the cell containing the result and paste it back as a 'Value'.

Can I use this method if my Excel version doesn't support the FILTER function?

Without the FILTER function, the dynamic formula approach will not work. Instead, you can use the helper column method with the RAND function to shuffle the list and manually remove the top names, or utilize a VBA macro for a fully automated draw.

How do I randomly select multiple unique names at once?

The easiest way is to use a helper column with the RAND function, sort the list by that column, and then pick the top N names. Alternatively, in newer Excel versions, you can use the SORTBY function combined with RANDARRAY to instantly shuffle the entire list.

Excel & Data Problems