How to generate random value from a given list in Excel?

To generate random number is easily done by the function =RANDBETWEEN(range), but have you ever tried to generate random value from a given list as below screenshot shown?

Generate random value from a given list by formula

---

Generate random value from a given list by formula

Actually, there are some easy formulas that can help you to quickly generate random value from a given list.

Select a blank cell that will place the random value, type this formula =INDEX($A$2:$A$16, RANDBETWEEN(2, 16)), then press Enter key. See screenshot:

In the formula, A2:A16 is the given list you want to use, 2 and 16 are the starting row number and ending row number of the list.

If you want to generate several random values from the list, you can drag the formula down as you need. See screenshot:

You can press F9 key to refresh the values.

Note: If you want to generate random number based on a list, you can use this formula =INDEX($I$2:$I$7, RANDBETWEEN(1, 6)), and press Enter key.

---

Relative Articles:

• How to generate random number without duplicates in Excel?
• How to keep/stop random numbers from changing in Excel?
• How to generate random Yes or No in Excel?
• How to generate random value based on assigned probability in Excel?

---

Easily Insert Random Data without duplicates in a range of cells

If you want to insert random and no duplicate integer numbers, dates, times or strings, even custom lists into a range of cells, the forumula maybe hard to remember. But Kutools for Excel's Inser Random Data can quickly handle these jobs as easily as possible. Click for full features 30 days free trial!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

---