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:
|=RAND()||A random number between 0 and 1 (varies)|
|=RAND()*100||A 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|
|Random numbers between 0 and 100 |
|Random integer numbers between 50 and 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 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
Generate random character strings in a range
Recommended Productivity Tools
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!