MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to randomly select a certain number of rows of data

Posted by Chris L on April 19, 2001 11:27 PM

Out of a simple list of 50,000 people and their contact details I want to take a random sample of 400 of them. How do I do this?

Posted by JAF on April 20, 2001 4:29 AM

With having so many records, the easiest way is probably to insert a column, type in the formula =RAND() and copy that formula down for all records.

Sort your data (either ascending or descending) and then take the top 400 (or however many you need) rows of data.

As long as you leave the =RAND() as a formula, the next time you sort, the formula will recalculate (assuming you have calculation set to Automatic) and you will get a different sort order and consequently a different top 400 records.

Hope this helps.


Posted by Chris L on April 22, 2001 4:26 PM


And here was me trying to think of macros, a great simple idea, thanks JAF - Chris L