Choose Random Values or Cells from a List in Excel

Add to Favorites
Author: | Edits: don

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")

ff0976a319351b7ae5cd358a9b39cf91.png

Result

c1f7f5715e70c69d0f7596d270b40c33.png

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)

5130ecd8741114b03235d86ee2b95069.png

Result

7d5f2e196945e9890ea1a170a81e0e5f.png

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.

Question? Ask it in our Excel Forum


Excel Function: CHOOSE(), RANDBETWEEN()
Downloadable Files: Excel File

Similar Content on TeachExcel
Delete All Empty Rows or Blank Cells from a Range in Excel
Tutorial: How to quickly delete all empty cells or rows from a range in Excel.  This allows you to ...
Extract the Last Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
Macro: This UDF (user defined function) extracts the last word or characters from a cell in Excel...
SMALL Function - Get the Lowest Values from a List in Excel
Tutorial: Return the smallest values from a list, or any of the smallest values, in Excel. This inc...
LARGE Function - Get the Highest Values from a List in Excel
Tutorial: Return the largest value from a list, or any of the top values, in Excel. This method use...
Get Day Name from a Date in Excel
Tutorial: How to get the name of a day from a date in Excel. This returns, for example, "Tuesday" fo...
Count the Number of Unique Values in a List in Excel
Tutorial: Use a formula to count the number of unique values that are contained within a list in Exc...