6 Ways to Generate Random 10-Digit Number in Excel

Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. You might need a dataset for random number entry in Excel. The dataset might need a digit specification; say 10-digit numbers. This article will help you with six ways to generate a random 10-digit number in excel. You can just enter dedicated formula or settings for the action to take place. All the methods have step-by-step instructions with a figurative description.


📁 Download Excel File

Download the Excel file below.


Learn to Generate Random 10-Digit Number in Excel with These 6 Suitable Approaches

The article is going to introduce six approaches in Excel to generate a random 10-digit number in Excel. The methods will help you understand the uses elaborately. The following discussion will cover the uses of the RAND, the RANDBETWEEN, the LEFT, and the RANDARRAY functions in four ways. You will also get two methods using the VBA Macro Code and the Data Analysis ToolPak option. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.

Dataset for generating a random 10-digit number


approach

1. Using RAND and ROUND Functions

The RAND function is widely used to generate any random 10-digit number in Excel. The simplest form is to multiply a number with the function itself if you need numbers varying from 0 to that number.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell B6.
  • Next, enter the following formula.
=ROUND(RAND()*9999999999+1,0)
  • Here, hit Enter.

  • Then, dragging the Fill Handle icon, copy the formula till cell D9.
  • Finally, you can generate the dataset in Excel.

Using RAND function to generate any random 10-digit number in Excel

🔨 Formula Breakdown

ROUND(RAND()*9999999999+1,0)

👉  Here, RAND returns a random 10-digit number in Excel.

👉  Also, ROUND rounds the return as an integer number.

Finally, we get a 10-digit number on cell B6.

📕 Read More: 4 Ways to Introduce Random Number in Excel with Decimals


approach

2. Applying RANDBETWEEN Function

Now you can directly use a formula as well with the help of the RANDBETWEEN function.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select cell B6.
  • Secondly, enter the following formula.
=RANDBETWEEN(1000000000,9999999999)
  • Thirdly, hit Enter.

  • Fourthly, dragging the Fill Handle icon, copy the formula till cell D9.
  • Fifthly and finally, you can generate random 10-digit numbers in Excel.

Applying RANDBETWEEN function for generating any random 10-digit number in Excel

📕 Read More: Generating Random Number Between Two Numbers in Excel


approach

3. Combining LEFT and RANDBETWEEN Functions

You can compile the LEFT function and the RANDBETWEEN functions to generate any random 10-digit number in Excel.

⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, select cell B6.
  • Now, enter the following formula.
=LEFT(RANDBETWEEN(0,999999999999999),$D$5)
  • Next, hit Enter.

  • After that, dragging the Fill Handle icon, copy the formula till cell D9.
  • Moreover, you can generate a random 10-digit number dataset in Excel.

Combining LEFT and RANDBETWEEN functions to generate any random 10-digit number in Excel

🔨 Formula Breakdown

LEFT(RANDBETWEEN(0,999999999999999),$D$5)

👉  Here, RANDBETWEEN brings the numbers from 0 to 999999999999999.

👉  Also, LEFT returns the number counting from first 10 digits.

Finally, we get a 10-digit number on cell B6.

📕 Read More: 5 Ways to Generate Random Number Between 0 and 1 in Excel


approach

4. Improvising RANDARRAY Function

In Excel from Microsoft 365, you can use the RANDARRAY function to automatically generate an entire dataset of random 10 digit number in Excel.

⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, select cell B6.
  • Now, enter the following formula.
=RANDARRAY(4,3,1000000000,9999999999,TRUE)

  • Then, hit Enter.
  • Moreover, Excel returns the dataset to you automatically.

Improvising RANDARRAY function for generating any random 10-digit number in Excel

📕 Read More: 8 Ways to Make Random 4 Digit Number Generator in Excel


approach

5. Employing User-Defined Function

Using VBA (Visual Basic for Applications), you can employ a User-Defined function to apply on your worksheet. Moreover, it is also the only method that will create a new function to generate an entire random 10-digit number dataset in Excel automatically. With the proper instruction, you can make your work easier.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, press Alt+F11 to open Visual Basic Application (VBA).
  • Next, select the Module option from the Insert You can also press Alt+I+L for the action.
  • Then, write the following code on the Module box.
Function Rndm10()
Rndm10 = Int(Rnd * 10000000000#)
End Function
  • Here, you should see the window like the image below.

Employing VBA code to generate an entire dataset of random 10 digit number

  • Now, close the VBA
  • Then, select cell B6.
  • Next, enter the following formula.
=Rndm10()
  • Here, hit Enter.

  • After that, dragging the Fill Handle icon, copy the formula till cell D9.
  • Therefore, you will be able to generate the dataset.

Employing VBA feature to generate an entire dataset of random 10 digit number

📕 Read More: 7 Ways to Create Random 5 Digit Number Generator in Excel


approach

6. Utilizing Data Analysis ToolPak

The Data Analysis ToolPak option is available at the end of the Data tab, under the group of the Analysis. It helps you to generate a set of cells with any digit random numbers in any range.

⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, press Alt+F+T to open the Excel Options window.
  • Now, go to the Add-ins section and click on the Go option from the Manage menu in the following image.

  • Next, tick the Analysis ToolPak option and proceed with OK.

  • After that, click on the Data Analysis Option from the Data tab, under the Analysis group.
  • Then, you will get the Data Analysis window.
  • Here, select the Random Number Generation option and proceed with OK.

Utilizing Data Analysis ToolPak to generate an entire dataset of the random 10-digit number

  • Now, fill in the required blanks as shown in the image according to your dataset and proceed with OK.

  • Here, you will find Column B with random 10-digit numbers.

Utilizing Data Analysis ToolPak for generating an entire random 10-digit number dataset

  • Next, repeat the same process on the Data Analysis window for Column C and Column D.
  • Moreover, you will be able to generate the required dataset in Excel.

📕 Read More: 8 Easy Tricks to Generate Random Data in Excel


📄 Important Notes

🖊️  All the data formats must be formatted properly. Otherwise, Excel may auto-format to something inappropriate for the user.

🖊️  Before and after using the Fill Handle icon, your random numbers will change for the source formula cell.

🖊️  Finally, you need to enable Macro Workbook for the VBA to run on your workbook.


📝 Takeaways from This Article

The article lets the readers understand the variety of options available to generate a random 10-digit number in Excel.

📌  Primarily, you can use the RAND function for 0 to any number you want.

📌  Also, you can apply the RANDBETWEEN function for a specific range. It helps you to generate a random 10-digit number in Excel.

📌  Here, you can combine the LEFT and RANDBETWEEN functions to get a formula.

📌  You can utilize the RANDARRAY function in Microsoft 365 as well. You can try it in Microsoft Excel 2021 version as well.

📌  Again, you can employ the VBA feature to generate any random 10-digit number in Excel. You need to use a User-Defined function here.

📌  Finally, you can make use of the Data Analysis ToolPak option.


Conclusion

Six methods are common to generate a random 10-digit number in excel. You can use the RAND, the RANDBETWEEN, and the RANDARRAY functions for the case in four ways. Rest two methods are based on the VBA Macro Code and the Data Analysis ToolPak option. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.


Related Article

(Visited 38 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo