Home / Functions / Generate a List of Random Numbers

Generate a List of Random Numbers

Sometimes you may need a list of random numbers which can be used as temporary passwords, pin codes, or just dummy data for your database application.

This can easily be produced without having to key in multiple entries. In the sample we use the RANDBETWEEEN function which returns a random number between the ranges you provide in the function’s arguments. You can follow the simple example below that will give a 5-digit random number.

Step 1 – Use the RANDBETWEEN function

Select a cell, preferably top-most cell, and enter the formula in the address bar:
= RANDBETWEEN(1, 99999)

randbetween function

This will give you numbers between 1 and 99,999. If you want three digit numbers, you must input 1 and 999.


Step 2 – Drag downward to copy to other cells

Next step is to drag the fill handle of the cell downward until it meets your requirement. You may need to press F9 to recalculate the numbers.

copy to cells

Note as a result of the function call we can also acquire numbers that are less than 5 digits.

In the screenshot, you can see that we have the number 74. What we will do is to simply pad zeroes to the left to make it 00074. In order to achieve this, we can just format the numbers in the next step.

Step 3 – Format the numbers

While the numbers are still selected, right-click on them then select Format Cells. The Format Cells dialog box will be shown.

custom format

In the Category list box, select Custom and enter 00000 (5 zeroes) in the Type field. This custom format will add zeroes to the left of the numbers to make it 5 digits.

Click Ok button to save.


formatted 5-digit random numbers
Final result


If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.

About Excel Help Desk

Check Also

Retain Trailing Zeros in a Text String

For this request we needed to ensure that a trailing zero was always displayed for a calculated text string. Through the use of an additional function the string included trailing zero's