Effective Techniques for Creating Random Values in Excel Spreadsheets

Quick Links

If you need to generate one or more random numbers for business, education, or another purpose, you can do so right in Microsoft Excel. You can get random numbers using either a random number generator tool or an Excel function.

Use the Random Number Generator in Excel

With an add-in from Microsoft called Analysis ToolPak , you can perform many statistical and engineering operations such as finding a moving average . Another feature of the tool is a random number generator.

Add the Analysis ToolPak

To see if you already have the add-in, go to the Data tab and the Analysis section of the ribbon. Look for the Data Analysis button. If you have the button, you can skip down to using the tool.

If you don’t see the button, you can add it easily. Go to File > Options and select “Add-ins” on the left. At the bottom of the window, go to Manage and pick “Excel Add-ins.” Click “Go.”

When the Add-ins window opens, check the box next to Analysis ToolPak and click “OK.”

Use the Random Number Generator

Head to the Data tab and click “Data Analysis” in the Analysis section of the ribbon. When the window appears, select “Random Number Generation” and click “OK.”

Starting at the top of the window, enter the number of columns you want to fill using the Number of Variables box. Then, enter the number of rows in the Number of Random Numbers box.

This generator is advanced in that you can select the distribution you want to use from options like Bernoulli, Binomial, Patterned, and Discrete. Once you select the Distribution from the drop-down list, the Parameters section will update with the necessary information for you to complete.

Click here

In the Random Seed field, you can enter a starting number (up to 9999) for the generator to use if you like. Then, choose one of the Output Options for where you want your random numbers to display.

Click “OK” when you’re ready and you’ll receive your numbers.

Use the Random Number Functions in Excel

Another option for generating random numbers in Excel is using a function. There are three functions you can use . Each time you recalculate or reopen the workbook, a new random number will be generated with these functions. They don’t offer as many options as the Random Number Generator tool, but they’re simpler to use.

The RAND Function

With the

    `RAND`

function, you can generate a random number greater than or equal to zero and less than one. This provides you with decimal number options. But you can also get numbers going higher than one by tweaking the formula.

For a basic random number, enter the following and press Enter:

=RAND()

For a random number greater than or equal to zero and less than 500, enter the following and hit Enter:

=RAND()*500

For a random whole number greater than or equal to zero and less than 500, enter the following and press Enter:

=INT(RAND()*500)

The RANDBETWEEN Function

Maybe you want to generate a number that’s between two specific numbers. In this case, you’d use the RANDBETWEEN function .

For a random number between 10 and 100, enter the following and press Enter:

=RANDBETWEEN(10,100)

For a random number between negative 10 and 10, enter the following and hit Enter:

=RANDBETWEEN(-10,10)

The RANDARRAY Function

For Microsoft 365 subscribers, the RANDARRAY function provides a set of random numbers. You can choose the number of rows and columns to fill with numbers. You can also select minimum and maximum values and specify whole numbers or decimals.

The syntax for this function is RANDARRAY(rows, columns, minimum, maximum, whole-decimal) where you enter True for a whole number or False for a decimal number as the last argument. All arguments are optional.

For a random array of numbers that cover three rows and four columns, you’d enter the following and hit Enter:

=RANDARRAY(3,4)

For a random array that covers the same number of rows and columns but also has a minimum of 1 and a maximum of 10, enter the following and press Enter:

=RANDARRAY(3,4,1,10)

For a random array using these same arguments but returns only whole numbers, you would enter the following and press Enter:

=RANDARRAY(3,4,1,10,TRUE)

Microsoft Excel gives you easy options for generating random numbers . Whether you need a discrete number or simply a number between one and 10, Excel has you covered.

| | Mastering Excel Functions | | |
| —————————- | —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— | |
| Functions | AVERAGE · CONCATENATE · COUNT · COUNTIF · DATEDIF · FILTER · FREQUENCY · FV · HYPERLINK · IF · IFS · IMAGE · INDEX · IS · LEN · MATCH ·MEDIAN · RAND · ROUND · RRI · SORT · SQRT · SUBSTITUTE · SUBTOTAL · SUM · SUMIF · TODAY · TRIM · TRUNC · VLOOKUP · WEEKDAY · XLOOKUP · YEAR | |
| Types | Basic · Budgeting · Data Entry · Logical · Text · Time and Date | |
| Explained | Copying Formulas · Evaluating Formulas · Finding Functions · Fixing Formula Errors · Functions vs Formulas · Comparing Lookup Functions · Locking Formulas · Structuring Formulas · Translating Formulas | |

Win Solutions

Effective Techniques for Creating Random Values in Excel Spreadsheets