Function Problems

Randomly Select a Specified Number of Cells in Excel

Ayan MasoodAyan Masood Nov 18, 2026 869 views

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.

Randomly Select a Specified Number of Cells in Excel
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.
Before you start

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.

Solution 1Recommended

Use a Dynamic Array Formula to Select Random Values in Excel

Use this formula in an Excel version that supports TAKE, SORTBY, and RANDARRAY.

1
Enter the selection count

In G2, enter a whole number from 1 to 6, depending on how many values you want to select from A2:F2.

2
Enter the formula

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.

3
Apply it to other rows

Copy the formula in H2 down alongside the other rows. Adjust the source range and count reference if your worksheet uses different columns.

Use a Dynamic Array Formula to Select Random Values in Excel
Check the count and output area: Keep the spill area empty and use a count between 1 and the number of source cells. The selection can change when Excel recalculates. TAKE is not available in Excel 2021; use the helper-cell method instead.
Work with random selections in WPS

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. 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. 2. Add random helper cells: Enter =RAND() in H2 and drag the fill handle across to M2.
  3. 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. 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.
Select a controlled number of random values from each rowUse familiar spreadsheet formulas and fill handlesOpen and edit Microsoft Excel XLSX and XLS filesUse WPS Office for free
microsoft office alternative - wps office

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.