Whether you work in or are preparing data for analysis, market research or auditing, or simply need to create unbiased samples for statistical studies, the need to select X number of rows at random from a Table is a common requirement for Excel users.

In this video I demonstrate one way to perform this task, using a combination of functions… ROWS, RANDARRAY and CHOOSEROWS (these last 2 are only available in Excel 365).

I also provide a simple method to ensure that the randomly generated data contains no duplicates.

The formulas that I used in the demos:

=ROWS(Sales)

=ROUNDUP(L125%,0)

=RANDARRAY(L2,1,1,L1,TRUE)

=CHOOSEROWS(Sales,M1#)

=COUNT(UNIQUE(M1#))

=IF(L2=L3,”OK”,”Problem”)

Blog post with long convoluted formula to avoid duplicates: https://www.techrepublic.com/article/how-to-generate-random-numbers-with-no-duplicates-in-excel/