Generating Random Data in Excel

Randomly selecting data in Excel can be a convenient way to create test datasets or sample subsets of your data. In this guide, we will explore two separate formulas that can help you achieve this task.

Suppose you want to select a random selection from a list in cells A2:A10. One way to do this is by using the INDEX and RANDARRAY functions in combination with the SEQUENCE function.

The formula works as follows: INDEX(SORTBY(data, RANDARRAY(ROWS(data))), SEQUENCE(n)). This formula uses the SORTBY function to sort the original data based on an array of random decimals generated by the RANDARRAY function. The ROWS function is used to count the number of rows in your data set and pass the count to the RANDARRAY function.

To select a specific sample size, you can use the SEQUENCE function to specify the number of values to retrieve. For example, if you want to get 5 unique random names from the list in A2:A10, the formula would be: =INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(5)).

Alternatively, you can input the sample size in a predefined cell and supply the cell reference to the SEQUENCE function. This can make it easier to adjust the sample size without having to modify the formula itself.

For Excel versions prior to 2010, you may need to work out a different solution as these functions are only available in newer versions of Excel. Specifically, Excel for Microsoft 365 and Excel 2021 support dynamic arrays, which is required for these formulas to work.

By following this guide, you should be able to generate random data in Excel using the INDEX and RANDARRAY functions with SEQUENCE. This can be a powerful tool for creating test datasets or sample subsets of your data.

Generating random data in excel