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.
