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.

`=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.

**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(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.

**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:

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.

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

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

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

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

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

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: How to get the name of a day from a date in Excel. This returns, for example, "Tuesday" fo...