How to Randomly Select Rows in an Excel Spreadsheet

Sometimes it's useful to select a random row or random set of rows in a Microsoft Excel spreadsheet. This can be useful if you want to randomly sample from a dataset, like a list of employees or customers, in order to efficiently test for certain properties. It can also be useful for situations like games and raffles where you may want to select a random option or player. Excel provides several ways to randomly sample data and generate random numbers.

There's no built-in Excel sampling tool that can automatically generate a random sample from a list of rows or data. You can, however, use Excel's function for generating a random number in order to generate a random sample.

Excel's random number function is called RAND, and it generates a random number that is guaranteed to be greater than or equal to zero and less than one. To use it, simply type the formula =RAND() into a cell of your choice, and a random number will be placed in that spreadsheet cell by the generator.

If you want to use the random number generator in Excel to randomly sample a set of rows, add a column at the end of the spreadsheet. Then, in the top cell of that column below any spreadsheet header rows, type =RAND() to generate a random number. Drag or copy the formula into the lower cells in the same column in order to add a randomly generated number to each row in the spreadsheet.

Then, have Excel sort the spreadsheet in increasing order of the random numbers. Take the first rows up to however many rows that you want, and they will be a random sample of your data.

Remember that you will lose any previous sort order or other ordering, so if you need to be able to reconstruct that ordering within your sample or the spreadsheet at large you should make sure that you have a column that you can sort by in order to restore the spreadsheet to its original order.

If you don't, before you sort the sheet by the randomized column, add another column and type the number 1 in the first row after any header rows. Type 2 and 3 in the subsequent rows and, assuming you have more than 3 rows, highlight those numbers and drag them down the spreadsheet to fill the column with increasing numbers beginning with 1. Then you can sort the spreadsheet or certain rows in it by this new column to restore the original order.

In some cases, you may want to sample rows one by one. This might be for dramatic effect if you're conducting a drawing or simply to make sure that you're able to inspect each row that you add into your sample to make sure it is valid.

Whatever the reason, you can do this by using a formula involving RAND to generate numbers between a certain range. Remember that RAND always generates numbers greater than or equal to zero and less than one. That means that if you multiply the result of RAND by a whole number n, you will always get a random result greater than or equal to zero and less than n.

That means that if you have n rows in your spreadsheet, you can use the formula =CEILING(RAND()*n, 1) to generate a number from 1 to n, inclusive, which you can use to pick a random row in your spreadsheet. That works because CEILING is a function that rounds its first argument up to the next highest multiple of its second argument. If you use 1 as the second argument, it will round up to the next whole number.

Of course, you will want to replace n with the number of rows in your sheet, subtracting any header rows you don't want to sample.