You can use Excel formulas to populate a random number or string from a list. You can even build a logic to return numbers without any duplicates. In this article, we are going to show you how to select a random Item from a list in Excel.

Although Excel doesn’t have a built-in feature or function to return a random value from a list, the **RAND** and **RANDBETWEEN** functions can return a random generated number.

We will use both functions in our guides. Be warned that generated values will be updated with each calculation. Thus, you may want to copy the generated items and use ‘paste as value’ (Paste Special > Values).

## Select a random item from a list

Our first approach works well for selecting only one random item from a list. This statement is important because this approach may cause duplicate entries if applied to more than one cell. Next approach will cover a method without duplicate values.

**RANDBETWEEN** function returns an integer number between given lower and upper limits. **INDEX **function can return integer value as row (or column if you have a horizontal list) number of for a vertical list.

Let’s say we have a list in B3:B16. Our formula should be either,

or

**ROWS **and **COUNTA** functions determine the upper limit of the randomly generated numbers. If you are certain about the number of items in the list you can ignore these functions. For example;

On the other hand, this approach essentially removes the dynamic structure of the formula.

## A random item without duplicates

Selecting a random item from a list without duplicate values needs a little more complex structure than first approach. First of all, we need a helper column to use with the **RAND** function. Each cell in this column will return a random number which will help sorting numbers and select consecutively from that order. So, the trick is to sort a list in a random order, and then select items from that list.

Helper column only contains the **RAND** function.

In another column, there will be **INDEX** functions like in the previous approach. The difference is that the rank formulas return the *rank* of a value in a given array of numbers:

Either approach works. We used **RANK.EQ **in this example.

**RANK.EQ** function can check a random generated number’s rank in a list, and return the row number for use in the **INDEX** function like in **RANDBETWEEN**.

This function uses the rank of random generated number in the cell C3. Use other cells in the helper column to select multiple random items from the list.