Farihat
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.
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.
- Here, hit Enter.
- Then, dragging the Fill Handle icon, copy the formula till cell D9.
- Finally, you can generate the dataset 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
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.
- 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.
📕 Read More: Generating Random Number Between Two Numbers in Excel
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.
- 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.
🔨 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
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.
- Then, hit Enter.
- Moreover, Excel returns the dataset to you automatically.
📕 Read More: 8 Ways to Make Random 4 Digit Number Generator in Excel
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.
- Now, close the VBA
- Then, select cell B6.
- Next, enter the following formula.
- 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.
📕 Read More: 7 Ways to Create Random 5 Digit Number Generator in Excel
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.
- 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.
- 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.
