excel generate random list of numbers

Excel Generate Random Number Lists

By Jessica King
AD

Ever needed to generate a random list of numbers in Excel for tasks like Monte Carlo simulations, risk analysis, or populating test data? While Excel doesn't have a built-in 'generate random' function, we can leverage other functions to achieve this. Let's explore how to create a dynamic, random list of numbers in Excel.

Before we delve into the methods, ensure that your version of Excel supports the RAND() function. This function generates a random decimal number between 0 and 1, which we can manipulate to fit our needs.

Using RAND() Function

The RAND() function is the bread and butter of random number generation in Excel. Let's discuss how to use it to generate a list of random integers.

AD
Random Number 1-100 Generator in Excel – Step by Step Guide
Random Number 1-100 Generator in Excel – Step by Step Guide
Excel - Generate Random Numbers, No Repeats (No Duplicates), Unique List
Excel - Generate Random Numbers, No Repeats (No Duplicates), Unique List
Randomize List In Excel
Randomize List In Excel

Assume we want a random list of 10 two-digit numbers. Enter the following formula in the first cell (A1):

``` =INT(RAND()*90) + 1 ```

After pressing Enter, you'll see a random two-digit number. Copy this formula down to the other nine cells (A2 to A10). Each cell will display a unique, random number between 1 and 99.

Generate Random Numbers: 2 Quick and Practical Ways in Excel
Generate Random Numbers: 2 Quick and Practical Ways in Excel
How to generate a list of random numbers in Excel (Excel tips #4)
How to generate a list of random numbers in Excel (Excel tips #4)
an image of a computer screen with numbers in the bottom left corner and on the top right
an image of a computer screen with numbers in the bottom left corner and on the top right

Using RAND() and INT for Multiple Columns

What if you need multiple columns of random numbers? You can apply the same formula across different cells in a range. Suppose you want a 10x10 table of random numbers. Enter the formula in the first cell (A1) and drag the fill handle to the other 99 cells. Each cell will now contain a unique, random integer between 1 and 99.

However, this method can generate repeated numbers. To avoid this, consider using the RAND() function with a structured reference:

AD
``` =INT(RAND()*90) + 1 ```

Using RANDARRAY() in Excel 365

Introduced in Excel 365, the RANDARRAY() function generates an array of random numbers. It's faster and more efficient than using RAND() in a structured reference. To generate a 10x10 table of random numbers, enter this formula in the first cell (A1):

I Generate Random Numbers in Excel With These Simple Formulas
I Generate Random Numbers in Excel With These Simple Formulas
Random Number Generator with Data Analysis Tool and Functions in Excel: 5 Methods - ExcelDemy
Random Number Generator with Data Analysis Tool and Functions in Excel: 5 Methods - ExcelDemy
How to Generate Random Number from List in Excel (4 Ways) - ExcelDemy
How to Generate Random Number from List in Excel (4 Ways) - ExcelDemy
How to Create a 1 to 999 Number Generator in Excel – Step by Step Guide
How to Create a 1 to 999 Number Generator in Excel – Step by Step Guide
How to Create Random Numbers in Excel (including no repeats)
How to Create Random Numbers in Excel (including no repeats)
Excel Secrets: How to Generate Random Numbers Like a Pro
Excel Secrets: How to Generate Random Numbers Like a Pro
``` =RANDARRAY(10,10) ```

The function generates a 10x10 table of random numbers between 0 and 1. To convert these into integers, multiply the result by 99 and use the INT() function:

``` =INT(RANDARRAY(10,10)*99) ```

Resetting Random Numbers in Excel

After generating a set of random numbers, you might want to reuse the formulae without losing the generated values. To do this, simply press F9. This recalculates all formulas in the workbook, generating new random numbers.

AD

Now that you've mastered generating random lists of numbers in Excel, what's next? Experiment with different number ranges, use pivot tables to analyze the random data, or combine these numbers with other data for more complex analysis.

Generate Random Numbers in Excel (5 Easy Ways + VBA)
Generate Random Numbers in Excel (5 Easy Ways + VBA)
AD