Choose Random Values or Cells from a List in Excel

Formula that allows you to choose random text or number values from a list or range of cells in Excel; this includes returning random cell references from a range.

Here, we will use the RANDBETWEEN() function in conjunction with the CHOOSE() function.

Sections:

Choose Random Text Value

Choose Random Cell

Learn More

Notes

Choose Random Text Value

=CHOOSE(RANDBETWEEN(1,3),"Google","Yahoo","Bing")

Result

To test this out, hit F9 on the keyboard to refresh the worksheet and you will see the result switch between Google, Yahoo, and Bing.

To learn how the formula works, look to the following section.

Formula Breakdown

RANDBETWEEN(1,3) this generates a random number from 1 to 3. This is put in for the first argument (index_num) of the CHOOSE function and this is what is used to determine which value to return.

"Google","Yahoo","Bing" these are the three pieces of text that can be returned and displayed. They come after the first argument for the CHOOSE function. If the RANDBETWEEN function returns 1, Google will be displayed; if it returns 2, Yahoo will be displayed; if it returns 3, Bing will be displayed.

You can have up to 254 text values in the formula, though that would be a lot to manage.

The thing to remember is that if you want to display 5 text values, then you need to change the RANDBETWEEN function to be like this: RANDBETWEEN(1,5) and if it's 6 values, like this: RANDBETWEEN(1,6) and so on.

Also, as you may have noticed, the text values are in order from left to right, which means that, in the above example, Google is in place 1, Yahoo in place 2, etc.

Choose Random Cell

=CHOOSE(RANDBETWEEN(1,4),A3,A4,A5,A6)

Result

Hit F9 on the keyboard to see the result switch between the colors in the cells.

To learn how the formula works, look to the following section - this is similar to the above explanation of this formula.

Formula Breakdown

RANDBETWEEN(1,4) this generates a random number from 1 to 4. This is put in for the first argument (index_num) of the CHOOSE function and this is what is used to determine which value to return.

A3,A4,A5,A6 these are the cell references that can be returned and whose contents can be displayed. They come after the first argument for the CHOOSE function. If the RANDBETWEEN function returns 1, the value in cell A3 will be displayed; if it returns 2, the value in cell A4 will be displayed; etc.

You must put the cell references into the formula individually and not as a single range like this: A3:A6.

You can have up to 254 cell references in the formula, though that would be a lot to manage.

Learn More

To learn more about the functions mentioned above, look at these tutorials:

CHOOSE() function

RANDBETWEEN() function

Notes

This tutorial shows you a nice way to combine two separate functions to create a useful formula that can be used in many different situations. Basically, we are creating a new kind of Random function that works with text instead of just numbers.

Make sure to put quotation marks around the text in the CHOOSE function!

You can also put numbers into the CHOOSE function, just don't put quotation marks around the numbers.

Make sure to download the attached sample file to work with the above examples in Excel.