I want to generate a column which will have random string values in any order. Those string values have to be from ('New', 'Mint', 'Very Good', 'Good', 'Acceptable', 'Poor') strings? Can anyone let me know about how can I make use of RANDBETWEEN function to populate my columns with the mentioned data in brackets?

  • Can you repeat those strings or can they only appear once?
    – Andi Mohr
    Nov 12, 2015 at 15:46
  • I have to repeat those strings
    – Ashutosh
    Nov 12, 2015 at 15:50

2 Answers 2


If you only have a few strings

If your strings are in the first column you can use the CHOOSE() function like this:


RANDBETWEEN(1,6) will randomly pick a number n from 1 to 6 - CHOOSE then counts through all the parameters you've listed (your strings in column A) and displays the nth term.

enter image description here

But, if you have many strings

If you have more than a few items to CHOOSE from, maintaining this formula becomes unwieldy.

In which case it's probably a good idea to insert a Table (let's call it MyStrings), create an ID column in column A using =ROW()-1 and switch our formula to a VLOOKUP. We can use MIN & MAX and structured references to dynamically work out how many terms there are in your table, for use in the RANDBETWEEN:


enter image description here

Careful Now

RANDBETWEEN is a volatile function, which means that it recalculates every time any other cell changes (anywhere in your workbook). If you need to keep the randomly-generated strings fixed as they are once you've produced them, you'll need to copy and Paste Special as Values.

If you use a lot of volatile functions, it may slow your worbook down, but there are things you can to do alleviate this.


You can use INDEX together with RANDBETWEEN to do this.

=INDEX({"New","Mint","Very Good","Good","Acceptable","Poor"},RANDBETWEEN(1,6))

Just fill this formula down your column.

  • 1
    is this volatile? Nov 12, 2015 at 18:37
  • @Raystafarian yes, because of RANDBETWEEN the value will change each time the sheet is calculated.
    – Excellll
    Nov 12, 2015 at 18:54
  • I thought so, but nobody mentioned it to the OP. Nov 12, 2015 at 18:55
  • Good point. I'll edit my post to make that point as well.
    – Andi Mohr
    Nov 13, 2015 at 9:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .