(Ultimate Guide) Create Random Numbers In Excel!

How to make random numbers In Excel

Seeking to extract a sample out of a diversified population?

Or working on some stuff and looking for ways to generate precisely random numbers?

In either case, if you want an unbiased set of random numbers, the Excel random number generator is your go-to tool.

Generating random numbers using excel is one of the simplest things you’d want to learn.

There are two primary ways to generate random numbers in excel, and here’s the ultimate guide to it.

You can learn more about the advanced functions of excel through our top-notch excel training courses.

 

What are Random Numbers in Excel?

What is meant by generating random numbers in excel? By general definition, random numbers constitute a set of numbers that fit the following two conditions.

  1. Their values are evenly distributed within a defined range; and
  2. Upcoming values cannot be predicted by looking at the past or present values in the set.

If you are looking for a similar set of unique numbers, you may use one of the two methods to generate random numbers in excel.

 

5 Uses of Random Numbers

The Random number generator Excel helps you choose random values from a population. While the reasons for generating such a set of numbers can be endless, here are a few reasons why you may employ the intelligent Excel random number generator function.

  1. The random winner for a competition;
  2. Forming groups of random students in a class;
  3. Generating a random list of numbers;
  4. To perform data analysis and obtain a random result; or
  5. To select an unbiased sample from a given population etc.

The random number generator in excel has a wide application in the fields of auditing and assurance, statistical analysis, computer simulation, systematic sampling, and Cryptography. In short, you may use the random number generator function of excel in any area where unpredictable and unbiased results are desired.

 

How to Generate Random Numbers in Excel?

To generate random numbers in Excel, you may employ one of the two built-in functions of excel i.e.  RAND or RANDBETWEEN. Though both the functions are more or less similar, they differ in a few aspects. Below is a detailed guideline into how both of these functions work.

1. The RAND Function

The RAND function enables generating an equally distributed real random number; the value whereof may lie anywhere between 0 and 1.

The value can be equal to or greater than zero, but less than 1. The RAND function includes no arguments and is written as follows:

 

=RAND()

 

Let’s take a glance at the virtual application of this function.

Toggle to excel and in any cell, write in the function RAND() to get a random value somewhere between 0 to 1 as follows.

Generating random real numbers using the RAND Function

 

Need more than one random value? Just drag and drop to have a range of values as wide as you may desire.

 

Drag and Drop to create a list of random real numbers

 

Once you have your list ready, to delineate on your data set better, try adding borders to it. Read about adding borders to your worksheet in excel here.

Another easy way to yield a different set of Excel random number could be to select a range of cells containing random numbers and then press the ‘F9’ key. Every time you hit the F9 key, excel will generate a different set of random numbers for you without you having to re-enter the ‘RAND()’ formula over and over again.

Note: Every time a new random number is generated, the previously Excel generate random numbers also change. In case you are linking these random numbers to multiple other worksheets and want all of them to regenerate uniformly, you may try linking the data to different sheets.

 

Two possible variations to the RAND Function:

     I.  Generating Random Numbers between 0 and 100

The RAND function of excel can be worked out to generate random number Excel equal to or greater than 0 but less than 100. The syntax of this function reads as follows:

 

=RAND()*100

 

Here is how the function works.

Generating random real numbers between 0 and 100 using the RAND Function

Drag and drop to get more numbers to the list.

    II.  Generating Whole Random Numbers between 0 and 100

To yield random whole numbers equal to or greater than 0 but less than 100, here is how you may modify the RAND formula.

 

=INT(RAND()*100)

 

Here is how it works:

Generating random whole numbers between 0 and 100 using the RAND Function

Pro Tip!

To generate numbers using the RAND function that range anywhere between 0 to a defined limit, simply replace the ‘100’ in the above formulas with the last value of your desired range. For instance, if you want to generate random real numbers between 0 and 500, you may write the RAND()*100 as follows:

 

=RAND()*500

 

Here is how it works.

Generating random real numbers between 0 and 500 using the RAND Function

Try doing the same with the ‘INT(RAND()*100)’ function to generate random whole numbers anywhere between 0 to a defined range. Take a look below.

Generating random whole numbers between 0 and 500 using the RAND Function

 

Advantages

  • No chance of overlapping values.
  • Great performance in recent tests of randomness.
  • Works for both distributed and parallel data points.
  • Requires no arguments.
  • Helps you yield real and whole random numbers.
  • Can be manipulated to obtain random numbers starting from 0 up to any range.

 

Disadvantages

  • Formula can be difficult to remember.
  • Value changes on recalculating the worksheet.
  • Only allows you to define the upper limit of a range.

 

2. The RANDBETWEEN Function

Excel’s random number generator function RANDBETWEEN generates a random value between any two integers as easy as two steps. The RANDBETWEEN uses arguments to generate random values. The syntax of the function is as follows:

 

=RANDBETWEEN(bottom, top)

 

Here ‘Bottom’ represents the lower limit, and ‘Top’ represents the upper limit. For example, the function RANDBETWEEN (1, 100) will generate random integers between 1 and 100, as shown below.

Generating random numbers between 1 and 100 using the RANDBETWEEN Function

 

Unlike the RAND function, the RANDBETWEEN function of excel doesn’t limit you to values between 0 and 1 only. If you want to set in a custom range of your choice, say 50 to 70 or 1080 to 2045 or anything. Here is how you can make the function work to the purpose.

Generating random whole numbers between 39 and 57 using the RANDBETWEEN Function

Two possible variations to the RANDBETWEEN Function:

     I.  Generating Random Decimal Numbers

If you want to generate random decimal numbers in excel using the RANDBETWEEN Function, the formula will be written as follows:

 

=RANDBETWEEN(30, 50)/10.

 

Where 30 and 50 are arguments provided for the formula, and the value 10 depends upon the number of decimal places you desire. Here is how the function works:

Generating random decimal numbers between 30 and 50 using the RANDBETWEEN Function

 II.  Generating Random Negative Integer

The RANDBETWEEN function enables you to generate negative integers in Excel by simple entering the values as arguments in the function as follows:

 

=RANDBETWEEN(Bottom, Top)

 

Generating random negative integers between -87 and 93 using the RANDBETWEEN Function

 

Note: Every time you open a worksheet or make any modifications, the value of RANDBETWEEN regenerates and gives a new random value. Keep scrolling through the article to learn about a couple of methods to put this feature to sleep.

Advantages

  • Requires arguments to generate random values.
  • Generates random integers within any range.
  • Generates negative random integers within any defined range.

Disadvantages

  • Requires practice to be adept with the formula.
  • Regenerates the values every time the worksheet is reopened.

 

Difference between RAND function and RANDBETWEEN

1.   Repetitive Values

With the RAND function, you would never see a repetitive value, but the random number generated will be between 0 and 1 only until and unless specified otherwise or combined with the RANK function to extract all sorts of random values. RAND Excel is one of the most used functions in Excel.

RANDBETWEEN function generates random number Excel of all kinds, but the only drawback is that you might experience repetitive values over and over again. It uses two arguments and generates values from within the limit given.

2.   Definite Values

RAND function specifies values between 0 and 1 only until you add an argument to the formula. But by default, it supports these two digits only.

RANDBETWEEN, however, can generate values between any two possible limits. Whether your arguments are 0 and 250 or 50 and 700, it will revert random numbers falling within the defined range.

3.   Regeneration of Values

The value regeneration feature is common to both the functions. Every time a worksheet is calculated, a new random number would be generated in the cell containing the function. This means that both the formulas are volatile, and their values keep changing until the regeneration function is turned off.

4.   Location in the Function Library

You can find both these functions under the Function Library of the Formulas tab in the Ribbon. Select either of the function from the Math & Trig tab.

 

How to Stop RAND Functions from Recalculating

The random generator Excel functions, RAND and RANDBETWEEN, both are extremely volatile. The slightest change in the worksheet will cause the values to be regenerated. However, this issue can be resolved easily. There are multiple solutions you can choose from if one doesn’t work for you. Let’s review them below:

1.   Manual Calculation Mode

If you prefer changing the value manually and don’t want Excel to recalculate every time you open the worksheet, you can set it to the manual calculation mode. Doing this will restrict the values from changing automatically. You can select it from the Formulas tab under calculation options.

Enabling the manual calculation mode in Excel

 

2.   Copying and Pasting

You can generate random numbers without automatic regeneration by copying and pasting. Select the values you need and copy them to the clipboard. Now paste those random numbers by selecting the Paste Special option (as text only) from the right-click menu as shown in the screenshot.

 

Paste Special options

 

Doing so converts the formula into a constant value. Once the numbers are pasted, you don’t need to worry about them changing anymore.

3.   Use the ‘F9’ key

An easy way to stop a value from changing in the RAND and RANDBETWEEN function is to select a cell and enter the formula into the function bar. It would look something like this:

A random real number generated through the RAND function

 

Now, go to the formula bar and press F9. The formula will reapply and change into a constant value, and on pressing Enter, the value will be copied in the active cell. It should be something like this.

Conversion of RAND formula into a constant value

 

Mistakes to Avoid When Working with RAND Functions

In Excel, using RAND functions to generate random numbers is extremely easy. All you need to do is put the right formula, and you have your desired result. However, often people make errors while working with these functions.

The errors are so insignificant that it would take you hours to find the mistake most of the time. We have listed below some common errors and their solutions to make your work easier. These are as follows:

  • Using the wrong function: If a person requires random values between 10 and 20 but chooses the RAND function instead of RANDBETWEEN. He would be making a mistake. Before you go on entering functions, do your research on which function and why.
  • Drag and Drop: Whenever you drag and drop a formula, the values regenerate. To avoid this, copy and paste the values, use either of the methods explained above to keep the values static.
  • Inserting the wrong formula: Almost 80% of all-time users type the wrong formula ranges. Since the formula is carried out depending on the previous cell’s range, it is essential for the reference order and range to be correct. Make sure to double-check all the parameters entered while simultaneously checking the source. Learn more about auditing your formulas for accuracy here.

 

Final Thoughts:

Random numbers are often needed as a reference to construct a data set. And Excel has a couple of functions to help you with that. With the Excel random number generator functions, RAND and RANDBETWEEN, generating random numbers is easy, and with a little practice, you could be the next expert.

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.