Random Cell Values in Excel

[kapzlok]

I was wondering if there is a particular function that can randomize a list of values in a column in Excel? I know that this may be accomplished by generating random numbers in a column next to the column with the desired values and then sorting the column, however, I am looking for simple way to do this with a single function. Any ideas?

 

[Zathras]

Perhaps an example of what you are trying to do would help. If I understand correctly, you might be able to use the RAND function as part of an INDEX expression, but that could result in duplicates.

 

[kapzlok]

Say I have a list that looks like the following:

boy
dog
cat
hat
small
it
lady
run
shout

and I want to randomize the order in which they appear so that a randomized list might look like the following:

lady
shout
boy
it
small
run
dog
hat
cat

Currently, I fill the column to the right of the above column with instances of the rand function to generate random numbers and then I sort the entire spreadsheet by that random number. Is there an easier way to do this with a particular function?

 

[Zathras]

Keep in mind that if you use a "simple function" (assuming one can be found), the list would re-order everytime something changed on the worksheet and the worksheet was recalculated. It doesn't sound like that would be desirable.

As I see it, you have two choices:
1. Continue the way your are, or
2. Use VBA to perform the action whenever it is wanted, either by a keystroke shortcut or by clicking a button.