# 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.

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.

Excel Function: CHOOSE(), RANDBETWEEN()

#### Question? Ask it in our Excel Forum

##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

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...
Formula to Get the Last Value from a List in Excel
Tutorial: Formulas that you can use to get the value of the last non-empty cell in a range 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 the Day of Week 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...
Tutorial Details
Excel Function: CHOOSE(), RANDBETWEEN()