Function Problems

How to Randomly Select Names in Excel and Keep Results Static

Guest Writer Oct 8, 2026 871 views

Question details

The user needs to randomly select 10 names from a list of 50 in Excel and ensure the chosen list remains static instead of recalculating.

How to Randomly Select 10 Names in Excel and Keep the Results

Product: Excel
Device & OS: not provided
Scenario: Generating a random sample or prize draw from a fixed list of names without the formula automatically refreshing the results.
Observed behavior: Dynamic random array formulas successfully pull the names, but they constantly change and overwrite the previous selection whenever the workbook recalculates.

Before you start

Ensure your list of 50 names is organized in a single continuous column without any empty rows. Verify that you are using a modern version of Excel or WPS Office that supports dynamic array functions like RANDARRAY and SORTBY.

Solution 1Recommended

Use Dynamic Formulas and Paste Special as Values

Combine the TAKE, SORTBY, and RANDARRAY functions to generate your random list, then freeze the data using the Paste Values feature.

This is the most efficient method for modern spreadsheet software. It instantly shuffles the dataset and extracts exactly 10 names. Because random functions are volatile and recalculate continuously, pasting them as values permanently locks the output in place.

Enter the dynamic array formula

Select an empty cell where you want your 10 random names to appear. Type the formula: =TAKE(SORTBY(A2:A51, RANDARRAY(50), 1), 10) (adjusting A2:A51 to match your exact data range) and press Enter. The 10 names will spill into the cells below.

Copy the spilled results

Highlight the newly generated list of 10 names. Right-click the selection and choose Copy, or press Ctrl+C on your keyboard.

Paste as values to lock results

Without deselecting the range, right-click the highlighted cells again. Under Paste Options, click the 'Values' icon (clipboard with 123). This replaces the volatile formula with static text.

Use Dynamic Formulas and Paste Special as Values

Static Results Achieved: Your random list is now permanent. Entering new data or recalculating the workbook will no longer alter these 10 names.

Solution 2

Use a Helper Column with the RAND Function

A universally compatible method for older versions of Excel that do not support modern dynamic arrays like TAKE or RANDARRAY.

Easily manage data with WPS

Use WPS Spreadsheet for Advanced Array Functions

WPS Spreadsheet fully supports modern dynamic array formulas like RANDARRAY, SORTBY, and TAKE, making random sampling both quick and effortless. Enjoy a familiar interface and seamless compatibility with Microsoft Excel files.

1. Open your file in WPS Spreadsheet: Launch WPS Office, open the Spreadsheet module, and load your workbook containing the list of names.
2. Apply the random selection formula: Input =TAKE(SORTBY(A2:A51,RANDARRAY(50),1),10) into an empty cell to instantly pull 10 random names.
3. Freeze the results: Highlight the generated list, press Ctrl+C, then right-click and choose Paste Special > Paste Values to keep the names static.

100% compatibility with Microsoft Excel (.xlsx) formulas and formats.Natively supports modern dynamic arrays for quick random selection.Lightweight, fast, and completely free to use for everyday data management.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my randomly selected names keep changing?

Formulas containing RAND, RANDBETWEEN, or RANDARRAY are known as volatile functions. This means they automatically recalculate and generate new numbers every time any change is made to the workbook. To fix this, you must copy the results and paste them as values.

Can I randomly select names without getting duplicates?

Yes. By using the SORTBY and RANDARRAY method, you are shuffling the existing list rather than independently picking random rows. This inherently prevents the same name from being selected twice, assuming there are no duplicates in your original list.

What if my Excel version says the TAKE function doesn't exist?

The TAKE function is only available in newer versions of Excel and Microsoft 365. If you see a #NAME? error, use the helper column method instead: add =RAND() in the adjacent column, sort the data by that column, and manually copy the top 10 names.

Excel & Data Problems