How do you generate random numbers in Excel? Use the ‘RANDBETWEEN’ function in MS Excel to generate a random number between two points you specify. In this blog and embedded video, I show you exactly how the RANDBETWEEN function works as an Excel random number generator and different ways you can use it. From conducting a non-biased prize draw and legitimately choosing a random winner, to impartially deciding who makes the next round of tea. Or put another way, let Excel do the thinking! I even include the free Excel template I made demonstrating the RANDBETWEEN formula in action. I hope this all helps you excel at the office…
How to Use the RANDBETWEEN Function in Excel
Microsoft Excel is unrivalled in its versatility and ability to perform almost any office task. There are a range of functions and formulas within its armoury, with one particularly handy feature being the RANDBETWEEN function.
What is the RANDBETWEEN function in Excel? Put simply, it’s a formula that allows you to generate a random whole number (integer) within a range you specify. Unlike most other formulas, it also recalculates every time you open the workbook or change anything in a spreadsheet. It is expressed as follows, where the ‘bottom’ refers to the lowest number of your range and ‘top’ the highest number:
For the lowest and highest numbers, you can simply enter the numbers in the formula. For example, ‘=RANDBETWEEN(1,100)’ will return a random number between 1 and 100. Alternatively, you can also use cell references for the bottom and top numbers, it’s up to you. I provide and explain both in the free video and Excel template later in this blog.
Note: If you’re instead looking for a random decimal, use the =RAND() function instead. It will return a random decimal between 0 and 1.
Why Use an Excel Random Number Generator
We don’t see things as they are, we see things as we are.Anais Nin
The random number generator has existed since Excel 2003; it is likely to continue for a long time within Excel among the Office 365 suite of apps. Your imagination is the limit for what you might use it for! Here are some ideas on why you might want to generate random numbers in Excel. The first two are included within my video and template below:
- Prize draw: Choose a genuinely random winner from a prize draw from a list.
- Who makes tea: Decide who gets the next round in the office, or elsewhere for that matter!
- Make decisions: If genuinely perturbed by a life or business decision, assign each a number and let Excel do the thinking!
- Choose lottery numbers: Don’t get much luck from the ‘lucky dip’? Try using =RANDBETWEEN(1,59) to generate your next lotto numbers… good luck!
- Assign to groups: Randomly assign experiment participants to predetermined groups.
- Random dates: Choose a random date between two dates.
- Choose from list: Whether it’s products, customers, or other data, there may be many reasons why you might want to randomly select something from a list.
- Check assumptions: As I outline in my ‘team sales tracker’ template, be careful of reading too much into trends; random numbers can also create apparent trends and “performance”!
It’s also handy to create random numbers in Excel as a trusted app already installed on your computor, and being able to use your own parameters, rather than have to find one online. It’s such a popular feature, there are even dedicated websites to generating random numbers in a range. This is probably because people don’t know how to do this in Excel! If you’d like to delve deeper, here are some more considerations and uses of random number generation, including statistical checks, gambling, and randomised design.
Video Explainer and Random Number Generator Excel Template
I hope this how-to blog has made sense so far. Take a look at my video explainer for a run-through how to use RANDBETWEEN in Excel. I demonstrate the use with a random prize draw template, a ‘who makes the tea’ template, and explain the instructions of how it all works:
Here’s a couple of screen grabs from the random number generator template. You can download it for yourself by clicking either image or using the download link below:
I hope you’ve enjoyed this presentation and find the how-to video and slidedeck helpful. Like what I do? Please like and share with others who might find it useful. If you’re so inclined, then you can also support my work to help keep free content flowing. Want fantastic, time-saving templates just for you and your business? Then please get in touch to arrange a bespoke quote. Finally, please feel free to leave your feedback below and/or comment on what tutorials and templates you’d like created.
Kind Regards, Adrian