Choose Random Values or Cells from a List in Excel

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

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.

CHOOSE() function

RANDBETWEEN() function

Question? Ask it in our Excel Forum

Excel Function: CHOOSE(), RANDBETWEEN()

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...
Tutorial Details
Excel Function: CHOOSE(), RANDBETWEEN()