Method 1 – Insert RANDBETWEEN Function to Generate 4 Digit Random Number
STEPS:
- Select the cell where you want to put the formula using the RANDBETWEEN function. So, we select cell B5.
- Put the formula into that selected cell.
=RANDBETWEEN(1000,9999)- Press Enter.
- Drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the Plus (+) symbol.
- See 4 digit random numbers in the cell range B5:B9.
Method 2 – RANDARRARY Function as Random 4 Digit Number Generator in Excel
STEPS:
- Choose the cell where you want to insert the RANDARRAY functions’ formula.
- Type the formula below into the selected cell.
=RANDARRAY(5,1,0,9999,TRUE)- Press the Enter key to finish the procedure.
This will automatically generate four-digit random numbers in five rows, as we put the number of rows 5 into the formula.
Method 3 – Produce 4 Digit Random Numbers with TRUNC and RAND Functions
STEPS:
- Select cell B5 and substitute the formula
=TRUNC(RAND()*9999,4)- Press Enter. The formula will be shown in the formula bar.
- To copy the formula over the range, drag the Fill Handle down or Double-click on the Plus (+) icon.
- See the result in column B.
How Does the Formula Work?
- RAND()*9999,4: The RAND()Â will generate a random number between 1 to 9. Multiplying 9999 will increase the number range and the 4 will return only the 4-digit of that random number.
- TRUNC(RAND()*9999,4: This will return the 4-digit of the random number.
Method 4 – Combine ROUND & RAND Functions to Generate 4 Digit Random Number
STEPS:
- Select the cell where you want to put the formula to generate random numbers. Select cell B5.
- Enter the below formula into that selected cell.
=ROUND(RAND()*(9999-1000)+1000,0)- Hit the Enter key.
- Drag the Fill Handle down to copy the formula over the range. Double click on the plus (+) sign. This also duplicates the formula.
- Generate 4-digit random numbers into column B.
How Does the Formula Work?
- RAND()*(9999-1000)+1000,0: This will mainly multiply RAND() with 9999 to generate only 4-digit random numbers.
- ROUND(RAND()*(9999-1000)+1000,0): This will remove the extra decimal numbers and generate only the 4-digit numbers.
Method 5 – Generate 4 Digit Random Number Using LEFT & RANDBETWEEN Functions in Excel
STEPS:
- Select the cell and insert the formula into that cell.
=LEFT(RANDBETWEEN(1,9)&RANDBETWEEN(0,999999999999999)&RANDBETWEEN(0,999999999999999),B5)- Press Enter.
How Does the Formula Work?
- RANDBETWEEN(1,9): This will allow taking a number between 1 to 9.
- RANDBETWEEN(0,999999999999999)&RANDBETWEEN(0,999999999999999),B5: This line of formula will generate random numbers based on the number we put in cell B5.
- LEFT(RANDBETWEEN(1,9)&RANDBETWEEN(0,999999999999999)&RANDBETWEEN(0,999999999999999),B5): This will return the first numbers based on number in the cell B5 is given.
Method 6 – Create 4 Digit Random Number by Combining INT & RAND Functions
STEPS:
- Select cell B5.
- Type in the formula below.
=INT(RAND()*(9999-1000)+1000)- Hit the Enter key to complete the process.
- The result will now be displayed in the selected cell, along with the formula in the formula bar.
- Drag the Fill Handle down to duplicate the formula across the range. Alternatively, to AutoFill the range, double-click the Plus (+) symbol.
- Column B shows 4-digit random numbers as a result.
How Does the Formula Work?
- RAND()*(9999-1000)+1000: This will mainly multiply 9999 with the RAND function to generate 4-digit numbers.
- INT(RAND()*(9999-1000)+1000: This will take the closest integer of the random number and generate only the 4-digit on random numbers.
Method 7 – Use Excel Analysis ToolPak to Generate 4 Digit Random Number
STEPS:
- Select the File tab on the ribbon.
- Click Options. Or, use the keyboard shortcut Alt + F + T.
- Open the Excel Options dialog.
- Click Add-Ins in the pop-up window’s left sidebar.
- Sselect Analysis ToolPak in the Add-ins section.
- Select the Excel Add-ins menu at the bottom of the main window from the Manage drop-down bar.
- Click the Go button.
- This will open a pop-up window with a list of all accessible Excel Add-ins.
- Checkmark the box Analysis ToolPak and click OK.
- Go to the Data tab from the ribbon. There is now an extra category named Analysis on the Excel ribbon’s Data tab, with one button labeled Data Analysis. Click on that.
- This will appear in the Data Analysis dialog box.
- Select Random Number Generation and click OK.
- A dialog box will appear named Random Number Generation.
- Type the number of columns in the Number of Variables text box and type the number of rows in the Number of Random Numbers text box.
- In the Distribution drop-down menu bar, select Uniform.
- Take the range for a 4-digit number. We take a range between 1000 and 9999.
- In the Output options, select Output Range. We selected $B$5:$B$9.
- Click on the OK button.
- See the result in the selected range.
Method 8 – Excel VBA to Produce 4 Digit Random Number in Excel
STEPS:
- Click the Developer tab on the ribbon.
- Launch the Visual Basic Editor by clicking on Visual Basic.
- Access the Visual Basic Editor by pressing Alt + F11.
- Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.
- This will appear in the Visual Basic Editor where we write our codes to create a table from range.
- Copy and paste the VBA code shown below.
VBA Code:
Sub FourDigit_RandomNumber()
Dim num As Integer
num = Int((9999 - 100 + 1) * Rnd() + 100)
Range("B5") = num
End Sub- Run the code by clicking the RubSub button or pressing the keyboard shortcut F5.
- Following the steps will generate a 4-digit random number in cell B5.
Download Practice Workbook
You can download the workbook and practice with them.
<< Go Back to Random Number in Excel | Randomize in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
