Skip to content

Create Random Data From a List

In this article, we will show you how to create a random data set that only contains values from a specific list.

For example, let’s say you want to generate a random list of letters, but you only want to use the letters shown below.

  • A
  • B
  • C
  • D

Your final list could then look something like this.

  • A
  • B
  • D
  • C
  • D
  • C
  • A
  • B

We will show you how to have a spreadsheet do this for you instantly.

The Formula

You will use two functions to create a random dataset that only displays values from a specified list.

The first function you will use is the INDEX function. You can read more about this function in our article, The Mighty INDEX Function.

The second function you will use is the RANDBETWEEN function. You can learn more about this function in our article, RANDBETWEEN Has Its Uses.

As you put these two functions together, the final formula looks something like this.

=INDEX(specified_list, RANDBETWEEN(1, length_of_list)

Let’s break down this formula to see how it works.


The first thing to understand about this formula is the RANDBETWEEN function. This function returns a random numerical value between two values.

For instance, you could set up the RANDBETWEEN function with the first argument as 1 and the second argument as 4. The RANDWETWEEN function will then return either 1, 2, 3, or 4 at random.

The INDEX Function

The second thing to understand about the above formula is the INDEX function. The INDEX function returns a value at a specified position within the specified list.

For example, let’s say you have the following list.

  • Blue
  • Red
  • Orange
  • Yellow

If you set up the INDEX function to return the third value in the list, it will simply return “Orange.”

How the Functions Work Together

The two functions explained above work together in the following way based on the defined formula. Here is the formula once more.

=INDEX(specified_list, RANDBETWEEN(1, length_of_list)

The RANDBWTWEEN function goes first and returns a random numerical value between the number one and the number of items in the specified list.

This value is then passed to the INDEX function, and the INDEX function returns whatever value happens to be in that position.

In essence, the formula is returning a value from the specified list based on a randomly chosen position. Since the position is random, the item returned is also random.

Putting It All Together

Now that you have an understanding of what’s going on, here is the formula applied to an actual spreadsheet. Let’s start with the following list of foods.

You now want to create a random list that contains only foods from the specified list.

  • Apples
  • Bananas
  • Oranges
  • Grapes

You would start by writing the following formula in cell C2.


Now, all you have to do is drag the fill handle down until you are satisfied with the length of your randomized list.


And that’s how you would create a random dataset that only contains values from a specific list.

This Post Has One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top