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 two values.
So, I can just drag the fill handle down to 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 just format the result as Currency.
To get more natural prices I can modify 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 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 references 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 WORKDAY function to generate random working days.
Finally, you might wonder how you can generate random text values?
The Excel WORKDAY function takes a date and returns the nearest working day n days in the future or past. You can use the WORKDAY function to calculate things like ship dates, delivery dates, and completion dates that need to take into account...