Randomly Select a Specified Number of Cells in Excel
Question details
Select a random number of timestamp values from each row, based on a count in a control column, and return them in a separate area.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Each row contains timestamp values and a control cell specifying how many values to select.
- Observed behavior
- The desired result is a separate set of randomly selected values for each row, with the number of results matching its control cell.
Identify the source range, the count column, and an empty output area. In the examples below, A2:F2 contains six source values and G2 contains the requested count.
Use a Dynamic Array Formula to Select Random Values in Excel
Use this formula in an Excel version that supports TAKE, SORTBY, and RANDARRAY.
In G2, enter a whole number from 1 to 6, depending on how many values you want to select from A2:F2.
Select an empty cell such as H2 and enter =TAKE(SORTBY(A2:F2,RANDARRAY(1,COLUMNS(A2:F2))),,G2). The results spill horizontally into adjacent cells.
Copy the formula in H2 down alongside the other rows. Adjust the source range and count reference if your worksheet uses different columns.

Use Helper Cells to Select Random Values in Excel 2021
Use random helper values and standard lookup functions when TAKE is unavailable.
Randomly Select Timestamp Values in WPS Spreadsheets
Use random helper cells and lookup formulas in WPS Spreadsheets to return the number of timestamp values specified in each row.
- 1. Open the worksheet: Open your file in WPS Spreadsheets. Place source values in A2:F2 and enter a count from 0 to 6 in G2.
- 2. Add random helper cells: Enter =RAND() in H2 and drag the fill handle across to M2.
- 3. Enter the selection formula: In N2, enter =IF(COLUMNS($N2:N2)>$G2,"",INDEX($A2:$F2,1,MATCH(SMALL($H2:$M2,COLUMNS($N2:N2)),$H2:$M2,0))), then drag the fill handle across to S2.
- 4. Apply the formulas to other rows: Select H2:S2 and drag the fill handle down. Format the result cells as dates or times if the timestamps appear as numbers.

Frequently Asked Questions
Why do my randomly selected values keep changing?
RAND and RANDARRAY generate new values when the workbook recalculates. To keep a selection, copy the result cells and use Paste Special > Values in a separate area.
Why does the Excel formula show a spill error?
The dynamic array formula needs empty cells to the right of its entry cell. Clear any content blocking that output area and try again.
Can I use the TAKE formula in Excel 2021?
TAKE is not available in Excel 2021. Use RAND helper cells with INDEX, MATCH, and SMALL instead.
Why do selected timestamps appear as numbers?
Dates and times are stored as numbers in spreadsheets. Select the output cells and apply a date or time format from the number-format controls on the Home tab.
Will blank source cells be excluded from the selection?
No. The formulas select from every cell in the specified source range. Remove blanks or adjust the source range if you want to select only populated cells.




