Quick, clean, and to the point

This video is part of Core Formula, video training for Excel Formulas.

How to generate random values

In this video, we'll look at a few ways to generate random values with the RANDBETWEEN function. You can use this approach to generate random numbers, random prices, random dates, and even random text values. This is a great way to create dummy data to test formulas and worksheets.
Video Transcript 

In this video, we'll look at a few ways to generate random values with the RANDBETWEEN function.

The RANDBETWEEN function is a simple function you can use to generate random numbers. For example, I can enter RANDBETWEEN with a bottom value of 1 and a top value of 100:


When I press enter, I get a random value between those values.

So, I can just drag the fill handle to get get 10 random numbers between 1 and 100. 

You can extend RANDBETWEEN to do all kinds of clever things. To get random prices between 10 and 50 dollars, I can again use RANDBETWEEN, and then format the result as Currency.

To get more natural prices, I can just change the formula to subtract 5 cents:


I'm using the shortcut control + enter here to enter all formulas at once.

You can also use RANDBETWEEN to get generate random dates.

For example, I can use the DATE function inside RANDBETWEEN to generate random dates between January 1st and June 30:


I could also put these dates on the worksheet and then point to those cells inside RANDBETWEEN. I need to make these making the reference absolute with F4 to prevent changes. This makes it easier to change the dates being used by RANDBETWEEN.


I can even combine this approach with the the WORKDAY function to generate random working days.


Finally, you might wonder how you can generate random text values?

A simple way to do this is to combine RANDBETWEEN with the CHOOSE function. 

Let's say I want to assign this list of a thousand people to 4 random groups: fox, bear, otter, and moose.

I can start off using CHOOSE with a hardcoded index of 1, followed by the group options.

When I press enter I get fox, since fox is the first item in the list. And if I change index to 2, I get bear.

=CHOOSE(1,"Fox", "Bear", "Otter", "Moose") // Fox
=CHOOSE(2,"Fox", "Bear", "Otter", "Moose") // Bear

Now I can simply replace the hard-coded index with RANDBETWEEN. Since we have 4 groups, the lower value is 1 and the upper value is 4.

=CHOOSE(RANDBETWEEN(1,4),"Fox", "Bear", "Otter", "Moose")

When I double click the fill handle to send the formula down, all groups are assigned.

Remember that RANDBETWEEN is a volatile function. It'll recalculate whenever you change the worksheet.

Once you have random values assigned, you'll probably want to stop this recalculation. The easiest way to do this is to use Paste Special to overrite formulas with values.

Dave Bruns

Related shortcuts