Random Number Assignment In Excel

Random number between two numbers

To generate random integers between two numbers, you can use the RANDBETWEEN function. In the example shown, the formula in B5 is: =RANDBETWEEN(1,100) This formula is then copied down from B5 to B11. The result is...

Random value from list or table

To get a random value from a table or list in Excel, you can use the INDEX function with help from the RANDBETWEEN and ROWS functions. In the example shown, the formula in G7 is: =INDEX(data,RANDBETWEEN(1,ROWS(data...

Random number from fixed set of options

To quickly fill a range of cells with random numbers from a set of fixed options (i.e. 25,50,75,100), you can use a formula based on the CHOOSE and RANDBETWEEN functions. In the example shown, the formula in B4 is: =...

Randomly assign data to groups

To randomly people (or anything) to groups you can use the RANDBETWEEN function with the CHOOSE function. In the example shown, the formula in F3 is: =CHOOSE(RANDBETWEEN(1,3),"A","B","C...

Random date between two dates

To generate random dates between two dates, you can use the RANDBETWEEN function, together with the DATE function. In the example shown, the formula in B5 is: =RANDBETWEEN(DATE(2016,1,1),DATE(2016,12,31)) This...

Random text values

To quickly fill a range of cells with random text values, you can use a formula based on the CHOOSE and RANDBETWEEN functions. In the example shown, the formula in C5 is: =CHOOSE(RANDBETWEEN(1,4),"Red",...

How to insert random (integer) numbers between two numbers without repeats in Excel?

Do you need to insert random numbers in Microsoft Excel? There are several methods to insert random numbers in a range as follows:

Insert random numbers by formulas

Insert random numbers with specified decimal places by VBA

Insert random integer numbers/dates/times without repeats/duplicates

Randomly insert whole numbers / dates / time between two numbers/dates/times without repeats

Kutools for Excel's Insert Random Data utility enables to insert random integers (whole numbers) between two certain numbers easily, randomly insert a series of dates (only workdays, or only weekends, or no limitation) between two dates, randomly insert a series times between two times, or generate random text strings with certain length. And the Unique values option will prevent from inserting duplicates. Click for 60-day free trial!


Insert random numbers between two numbers with RAND function and RANDBETWEEN formulas

This method will apply the RAND function and RANDBETWEEN function to insert random numbers between two numbers into a specified range in Excel. Please view below formulas:

FormulaDescription (Result)
=RAND()A random number between 0 and 1 (varies)
=RAND()*100A random number between 0 and 100 (varies)
=RANDBETWEEN(low, high)A random integral number between two given integer numbers, such as RANDBETWEEN(50, 100).

See below screenshots:

Random numbers between 0 and 1
=RAND()
Random numbers between 0 and 100
=RAND()*100
Random integer numbers between 50 and 100
=RANDBETWEEN(50, 100)

Insert random numbers between two numbers with specified decimal places by VBA

Below VBA can help you insert any random integer numbers or random numbers with given decimal places into a specified range in Excel. Please do as follows:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

Public Function RandomNumbers(Num1 As Long, Num2 As Long, Optional Decimals As Integer) 'Update20131113 Application.Volatile Randomize If IsMissing(Decimals) Or Decimals = 0 Then RandomNumbers = Int((Num2 + 1 - Num1) * Rnd + Num1) Else RandomNumbers = Round((Num2 - Num1) * Rnd + Num1, Decimals) End If End Function

3. Save and close the code, then in a blank cell, input this formula =RandomNumbers(X,Y,Z), X indicates the lower limit of the numbers, Y indicates the higher limit of the numbers, and Z is the specified decimal places of random numbers.

For example, I want to generate some whole numbers between 50 and 1000, I can insert this formula =randomnumbers (50,1000,0); and insert random numbers between 50 and 100 with 2 decimal places with this formula =randomnumbers (50,100,2). See below screenshots:

Insert Random numbers without decimal places:
Insert Random numbers with 2 decimal places:

Insert random whole numbers / dates / time without duplicates by Kutools for Excel

Kutools for Excel's Insert Random Data makes it possible to insert random integral numbers between two numbers without duplicates into selected range in Excel. Please do as follows:

1. Select the range you want to insert random integer numbers, and click Kutools > Insert > Insert Random Data, see screenshot:

2. In the Insert Random Data dialog box, (1) click Integer tab, (2) In the From and To boxes type the number range you will generate random whole numbers between, (3) check the Unique Values option, (4) and click the Ok button. See screenshot:

This fantastic Insert Random Data feature also supports to insert random dates without repeats, random time without duplicates, random characters, and random data from certain custom list without repeats as well.

Insert random dates (excluding weekends) without repeats by Kutools for Excel

Insert random time data without repeats by Kutools for Excel

Insert random characters with specified string length by Kutools for Excel

Insert random data from certain custom list without repeats by Kutools for Excel


Demo: insert random (integer) numbers between two numbers without repeats in Excel


Related article:

Generate random character strings in a range


Recommended Productivity Tools

Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Tip: In this Video, Kutools tab and Enterprise tab are added by Kutools for Excel. If you need it, please click here to have a 60-day free trial without limitation!

One thought on “Random Number Assignment In Excel

Leave a Reply

Your email address will not be published. Required fields are marked *