How to Select Every Nth Row or Random Rows in Excel
Question details
The user needs to extract periodic rows (e.g., every 46th row) or a specific number of randomly selected rows from a massive dataset for sampling and analysis.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sampling a large dataset of approximately 160,000 records for reporting or statistical analysis.
- Observed behavior
- The user wants an automated formula or method to retrieve the periodic or randomized rows instead of selecting them manually.
Check your spreadsheet software version to see if it supports Dynamic Array formulas, as functions like FILTER, CHOOSEROWS, and RANDARRAY provide the fastest solutions for this task.
Use Dynamic Array Formulas (Modern Excel)
The most efficient way to extract periodic or random rows using advanced functions available in modern spreadsheet software.
Dynamic arrays allow a single formula to output multiple values into adjacent cells. This eliminates the need for helper columns when sampling large datasets.
Enter the formula `=FILTER(A1:Z160000,MOD(ROW(A1:A160000)-ROW(A1)+1,46)=0)` into an empty cell. This checks the row number, divides it by 46, and filters only the rows where the remainder is zero.
You can also use `=CHOOSEROWS(A1:Z160000,SEQUENCE(INT(160000/46),,46,46))` to generate a mathematical sequence of row numbers and pull exactly those rows from your dataset.
To extract a random sample of 3,466 rows, type `=CHOOSEROWS(A1:Z160000,RANDARRAY(3466,,1,160000,TRUE))`. This randomly generates 3,466 row indices between 1 and 160,000, extracting the corresponding data.
Use a Helper Column and Filter (Older Versions)
A reliable workaround for older versions of Excel that do not support modern dynamic array functions.
Perform Advanced Data Analysis for Free with WPS Spreadsheet
WPS Spreadsheet fully supports modern array formulas, including FILTER, SEQUENCE, and RANDARRAY, making complex data extraction and random sampling a breeze for massive datasets.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your large .xlsx or .csv data file.
- 2. Enter the sampling formula: In an empty column or new sheet, use `=FILTER(A1:Z160000,MOD(ROW(A1:A160000)-ROW(A1)+1,46)=0)` to pull your periodic rows.
- 3. Save your results: Wait for the array to spill the extracted data, then save your sampled worksheet seamlessly in Microsoft Excel format (.xlsx).
Frequently Asked Questions
How do I select every other row instead of every 46th?
You can use the exact same logic by changing the divisor in the formula. Use `=FILTER(A1:Z100,MOD(ROW(A1:A100)-ROW(A1)+1,2)=0)` for even rows, or change the end condition to `=1` to extract odd rows.
Why am I getting a #SPILL! error when using CHOOSEROWS?
A #SPILL! error means the dynamic array formula doesn't have enough blank cells to display all the resulting rows. Clear the cells below and to the right of your formula to allow the data to populate freely.
Can I use Power Query to extract random rows?
Yes. You can load your table into Power Query, add a Custom Column using the formula `= Number.Random()`, sort the table by that new random column, and then use 'Keep Top Rows' to keep a specific random sample count.
Are the results from the RANDARRAY function permanent?
No, RANDARRAY is a volatile function. Your random sample will recalculate and change every time the worksheet changes. To make your sample permanent, select the spilled results, copy them, and use 'Paste as Values' to overwrite the formula.
