Generate Realistic Dummy Data in Excel Using RANDBETWEEN and CHOOSE

This might appear as an unusual hack for data analysts, but there are a couple of reasons you will need to generate randomized data in Microsoft Excel:

• you are building a portfolio to demonstrate your data visualization skills, but you do not raw data to analyse and visualize
• you want to share an Excel template with others, but want to remove confidential or sensitive information without losing the structure of the charts, formulas, etc.
• you want to create a regression or other analytical model that requires thousands of rows of data or data points, but you do not have this data at hand

The star of the randomization operation in Excel is the =RANDBETWEEN function. RANDBETWEEN returns a random number based on the numbers you specify. Its syntax requests for only two arguments: a bottom number - the smallest integer you want the function to return, and a top number - the largest integer you want to be returned.

=RANDBETWEEN(bottom, top)

It is important to note that the RANDBETWEEN function is a volatile function i.e. the value it returns will change every time the worksheet refreshes or Excel recalculates. Therefore, the best way to use this function is to generate the randomized data, then remove the formulas by copying the data and pasting it back as values.

There are different scenarios to use the RANDBETWEEN function to randomize data:

• When you want a randomized set of numbers or values within a specific range (specific range means you have a defined minimum and maximum value)
• When you want a randomized set of string text (or categories) from a specific list

Let's see RANDBETWEEN at work with an example.

Say, for instance, I have a list of 6 car models that can be sold between $23,000 and $40,000 (depending on discounts, etc available), and I want to create a randomized sales performance dataset showing sales of these cars within the period between January to August 2021.

First things first: set up lists containing the 6 car models, and the minimum and maximum values for the sales value and the dates. You can do this on the same sheet or on a separate worksheet: I've done this on the same worksheet for ease of reference.

Next, set up the RANDBETWEEN formula that will generate the randomized data.

For the SALES VALUE column, this will be a simple formula that reads as follows:

=RANDBETWEEN(23000,40000)

or you could refer directly to the values in the created list, and the formula will read as:

=RANDBETWEEN($I$2,$I$3)

For the DATE column, the same steps will apply: type in the RANDBETWEEN formula indicating the minimum and maximum ranges (either hard-coded or by referring to the values from the list). You can select the dates and change their format to long-date format (if you prefer) once the randomized data has been generated

For the PRODUCT column, you will need to include one more function - the CHOOSE function - to generate the randomized data.

The CHOOSE function returns a value or number from a given list based on an index number:

=CHOOSE([index number], [value 1], [value 2], ...[value n])

Here's how it works: if you have list like the following: [bat], [shoe], [flower], [pen], the CHOOSE function will return the item on the list whose relative position corresponds to the index number.

This means that:

= CHOOSE(1,[bat],[shoe],[flower],[pen]) will return the item "bat"

= CHOOSE(3,[bat],[shoe],[flower],[pen]) will return the item "flower"

Putting this together, we can define a range of index numbers for the CHOOSE function with the RANDBETWEEN function, and it will return a random item from the list of car models based on whatever number the RANDBETWEEN function generates. Since we have 6 car models, our bottom value for the RANDBETWEEN function will be 1 while the top value will be 6.

This is what the combination of CHOOSE and RANDBETWEEN functions will look like:

=CHOOSE(RANDBETWEEN(1,6), "Cadillac", "Chevrolet", "Acura", "BMW", "Audi", "Buick")

or, choosing to refer to the list:

=CHOOSE(RANDBETWEEN(1,6),$F$2,$F$3,$F$4,$F$5,$F$6,$F$7)

Note: If you decide to use the first version (hard-coding the car model names into the formula), remember to wrap each name with double quotations, so that Excel recognizes the name as a string.