How to Select a Random 10 Percent of Excel Rows
Question details
The user needs to select a random 10% sample of rows from an Excel list without having duplicate random numbers.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a random sample of students or data from a larger dataset for analysis or testing.
- Observed behavior
- A reliable method is needed to randomly extract exactly 10% of the dataset rows without encountering duplicate selections that distort the sample size.
Determine the exact number of rows that make up 10% of your dataset (e.g., 100 rows from a list of 1,000) so you know exactly how many rows to highlight after sorting.
Use the RAND Function and Sort Feature
This method involves creating a helper column with random decimal numbers and sorting the data to easily extract a non-duplicated random sample.
Using the RAND() function ensures every row gets a highly unique decimal number, preventing the duplicates common with other integer-based randomizing tools. Sorting the table shuffles your data randomly, allowing you to easily skim the top 10%.
Insert a new blank column next to your data. Type =RAND() in the first cell of this column and press Enter.
Double-click or drag the fill handle at the bottom-right corner of the cell to apply the =RAND() formula down to all rows in your dataset.
Select your entire dataset, including the new random number column. Go to the Data tab and choose Sort. Sort by the helper column in ascending or descending order.
Calculate the number of rows equal to 10% of your total data. Select that specific number of rows from the top of your sorted list and copy them to a new worksheet or location.
Easily Sample Data with WPS Spreadsheet
WPS Spreadsheet provides powerful functions like RAND and advanced sorting tools to help you perform random data sampling efficiently. It is fully compatible with Microsoft Excel formulas, making it seamless to manage your worksheets.
- 1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx file containing the target data.
- 2. Insert the RAND formula: Add a new helper column and enter =RAND() to generate random decimals for each row.
- 3. Sort and sample: Use the Sort feature under the Data tab to randomly shuffle the rows, then copy the top 10% for your sample.
Frequently Asked Questions
Can I use RANDBETWEEN instead of RAND for random sampling?
While RANDBETWEEN generates random integers, it often produces duplicate values in large datasets, which can mess up your unique row selection. The RAND() function generates decimal values, making duplicates highly unlikely.
How do I calculate exactly how many rows make up 10%?
Count the total number of data rows using the =COUNTA() function, then multiply that number by 0.10. Round up or down to the nearest whole number to determine exactly how many rows to select after sorting.
Why do my random numbers keep changing when I click anything?
The RAND() function is volatile, meaning it recalculates automatically whenever the worksheet changes. To stop this, select the cells with the RAND formula, copy them, right-click, and choose 'Paste as Values'.
