Last week, I was creating an Excel file with sample data, to use for a few experiments. But don’t worry, they weren’t mad-scientist-type experiments – I was doing Power Pivot experiments, and needed some data to play with.
I needed 2 types of data:
- Numbers: sample test scores in one column
- Text: random Region names and Gender in other columns.
Create Random Numbers
It’s easy to create random numbers in Excel – just use the RANDBETWEEN function. Set a bottom and top number, and a random number from that range is returned. I wanted test scores between 30 and 100, so I used this formula:
=RANDBETWEEN(30,100)
Get Random Text
The RANDBETWEEN function can only return numbers, so it’s a little trickier to create a set of random text data. I’ve tried different ways in the past, and they usually involve multiple steps. It’s nothing too complicated, but it takes longer than just using RANDBETWEEN.
This time, I tested a new method – using RANDBETWEEN inside a CHOOSE formula. Both lists of options were short – 2 genders and 3 regions – so they would work nicely as values in a CHOOSE formula.
Type the List of CHOOSE Values
In my Gender formula, there would be two values, so the RANDBETWEEN function used 1 as the bottom value and 2 as the top.
Then, I typed in the Genders, as the values, putting double quote marks around each value.
=CHOOSE(RANDBETWEEN(1,2),”Male”,”Female”)
Use Cell References as CHOOSE Values
In my Region formula, there are 3 values, so the RANDBETWEEN function used 1 as the bottom value and 3 as the top.
Instead of typing the region names, I used absolute references to the cells which contained the region names. Cell references are a better choice if the values are long, or if they’ll change frequently. Also, it’s quicker than typing!
=CHOOSE(RANDBETWEEN(1,3),$K$4,$K$5,$K$6)
Paste As Values
The final step in setting up the random data is to copy the columns, and paste them as values. That will lock in the values, so they don’t keep changing, as you work in the file.
- I use the mouse to select the columns, then drag to the right, while holding the mouse button.
- Drag the column back to its original location, and let go of the mouse button.
- Then, in the popup menu, click on Copy here as Values Only
You can see that step near the end of the video, below.
Video: Create Random Text With CHOOSE
Watch this video to see the steps for using RANDBETWEEN on its own, and combined with CHOOSE.
Download the Sample File
To download the sample file, go to the Excel Sample Files page on my website, and in the Functions section, look for FN0035 – Create Random Text with CHOOSE Function. The file is in xlsx format, and does not contain macros. One sheet has the formulas, and the other has the data pasted as values.
Create Random Test Data
This video shows how to create random numbers with RANDBETWEEN, then change the formulas to static values.
_________________