Key takeaways
- Random decimal numbers: If you want to generate random decimal numbers between 0 and 1 in Excel, use the RAND function.
- Random whole numbers: If you want to generate random whole numbers within a specified range in Excel, use the RANDBETWEEN function.
- Structured data collection: If you’re looking to gather structured data in spreadsheets, use Jotform to create online forms for data collection, which integrate natively with Excel.
Need to generate random numbers for sampling, testing, assignments, simulations, or various spreadsheet workflows? It’s fairly simple to do in Microsoft Excel using the tool’s built-in function RAND.
In this article, we’ll break down how to generate random numbers in Excel, including how to choose the right function for decimals, whole numbers, ranges, and nonrepeating values.
And if you’re looking for a way to collect structured data before you work with it in Excel, we’ll introduce you to Jotform, a leading online form builder that helps teams collect clean data. Jotform integrates directly with Microsoft Excel; using the tools together can enhance and streamline your Excel workflow.
Random number formulas in Excel
Excel has a built-in function for random number generation. Here are the two random number generator formulas you should be aware of:
- RAND()
- RANDBETWEEN()
Using each is simple. Select the cell where you want to generate the random number, type in the Equals sign (=) and the correct formula, and press Enter. Excel will automatically generate the random number based on the parameters of the formula. We’ll provide more detailed instructions on how to use the formulas in the following sections.
Pro Tip
Want to master Microsoft Excel? Check out our Microsoft Excel tutorial for details on formulas, functions, visualizing data, and much more. It’s a great starting point, whether you’re new to Excel or an experienced user.
Which random number formula should you use in Excel?
Excel’s two random number generator functions are distinct for different types of numbers. Here’s when to use each one:
Use RAND for random decimals
Excel’s RAND function is for generating random numbers that are larger than 0 but less than 1 (decimal numbers). You do not need to specify a range of numbers in the brackets because the function will automatically use 0 and 1 as the parameters, but you still need to include the empty brackets as part of the formula.
The RAND function is appropriate for a number of use cases, such as scenario modeling, random sampling, and dummy data creation. It can also be used in gamification, such as for random prize draws, or any other time you want to generate a random decimal in Excel.
Note that the RAND function in Excel is a volatile one, meaning that the random number is not fixed. It regenerates a new random number every time the Excel sheet changes. If you want to freeze the random number you’ve generated, you have to copy and paste it into another cell as a value.
Use RANDBETWEEN for random whole numbers
RANDBETWEEN in Excel is used to generate random whole numbers within a specific range.
The RANDBETWEEN function comes in handy for generating identification numbers or reference values, grouping and distribution, and creating realistic mock test data. It is also useful when you need random numbers within specific constraints.
Like the RAND function, RANDBETWEEN is also volatile, which means it will regenerate a random number anytime there is a change in the sheet. To keep it fixed, copy and paste the number into another cell as a value.
How to generate random numbers in Excel, step-by-step
If you’re not familiar with using the random number generator in Excel, it can feel intimidating. Follow these step-by-step instructions to get the results you want.
1. Select the cell where you want to generate the random number
Simply put your cursor in the specific cell and click once.
2. Enter the applicable random number generation formula
This is the most important step. Type the Equals sign (=) and the random number generation formula into the cell.
The RAND() formula will automatically generate a number that’s greater than 0 and less than 1, but if you’re using the RANDBETWEEN() formula, you will need to specify the lower and upper limits of number selection, separated by a comma. For example, if you wanted to generate a number between 1 and 100, the formula would be =RANDBETWEEN(1,100).
3. Press Enter to generate the result
This triggers the random number generation.
4. Copy the formula to generate more random numbers
If you want to generate more random numbers, drag the fill handle from the bottom right corner of the cell down to a different cell, or copy and paste the formula into a different cell.
How to generate random whole numbers in Excel
There are many reasons why you may need to generate whole numbers in Excel within a specific range. For example, if you’re picking a lottery winner, you can set the range based on the number of entrants. If you’re simulating attendance numbers for an event, you can specify a range based on the number of people you expect.
Example using RANDBETWEEN
In the RANDBETWEEN() formula, the numbers in the brackets specify the lower and upper limits of number generation. For example, for this formula:
RANDBETWEEN(1,999)
Excel will generate a random number between 1 and 999.
As another example, if you want to simulate a die roll, the formula would be:
RANDBETWEEN(1,6)
The range of the function is inclusive on both ends, which means the function may generate 1 or 6 as the random number in addition to the numbers in between.
How to change the minimum and maximum values
Changing the minimum and maximum values of the RANDBETWEEN function is simple. Just decide on the minimum and maximum values and enter them into the function in between the brackets, separated by a comma.
Pro Tip
The RANDBETWEEN function may generate duplicate values. This is because each cell calculates random numbers independently and is not constrained by other cells. This can be problematic if you’re looking to calculate several random numbers without duplicates, such as for unique identification numbers. If you want to generate random numbers in Excel without duplicates, you’ll need to use a different method, such as the SORTBY, SEQUENCE, and RANDARRAY functions in combination with each other.
How to generate random decimal numbers in Excel
Generating random decimal numbers is ideal for financial projects, sales forecasting under uncertain conditions, probability-based decision models, and random assignments.
Example using RAND
If you want to generate a random number between 0 and 1, use the RAND() formula without specifying any range in the brackets.
However, you can customize the range as well. For example, if you want to generate a random decimal number that’s greater than 0 but less than 100, you can multiply the formula by 100. This will look like:
=RAND()*100
How to generate decimals in a custom range
In some use cases, you may need to generate random decimal numbers in a custom range. The formula for this is =MIN + (MAX – MIN) * RAND(). For example, if you want to generate a random number that is between 5 and 20, the formula will be:
=5 + (20 – 5) * RAND()
Pro Tip
If your workflow includes collecting, organizing, or reviewing structured data, it’s best to use a dedicated form builder like Jotform in conjunction with Excel. It enables you to collect and feed clean data into Excel from the start, improving your results.
Cleaner data makes Excel workflows easier
Working with random number formulas in Excel is straightforward. However, sometimes you need to collect data as part of your workflow, perhaps in addition to using random numbers. For this, there’s Jotform.
Jotform is a free online form builder that is ideal for collecting structured, spreadsheet-ready data that can be analyzed or randomized in Microsoft Excel. Here are some of the reasons people use Jotform:
- Thousands of templates for online forms: Jotform comes with over 20,000 templates for forms, surveys, polls, questionnaires, and more across a range of industries, departments, and use cases. Simply choose a template that works for you and customize it based on your needs with the drag-and-drop form builder.
- A built-in collaboration and data management tool: Jotform comes with Jotform Tables, a spreadsheet-powered database that is ideal for organizing and managing your form submission data. It’s highly collaborative, so many users can work on the same table at once.
- Native integration with Microsoft Excel: If you like the functionality of Excel for working with your data, you can use the Jotform Microsoft Excel integration to connect the two tools. Jotform integrates natively with Excel, so there’s no complex workflow automation or coding required. You can integrate the two tools in minutes from the form builder.
For clean data collection and easier spreadsheet management, use Jotform and Excel together. You can gather the data you need and use random number generators in the spreadsheet when required.
Generate in Excel, collect better with Jotform
Excel is a great tool for generating random numbers. But when you want to collect structured data from internal or external audiences, Jotform is the way to go. You can gather information in forms, surveys, polls, and questionnaires and analyze the data in Excel, thanks to the Jotform Excel integration.
Explore Jotform today and see how well it works with your Excel workflows!
FAQs about random numbers in Excel
The RAND and RANDBETWEEN functions are volatile functions, which means they generate a new random number every time the spreadsheet changes. To keep the random numbers static, copy and paste the number into a different cell as a value. You can also manually replace the formula with its static value.
This article is for Excel users, students, analysts, teachers, and business users who need to generate random numbers for sampling, testing, simulations, assignments, or spreadsheet workflows.
Send Comment: