In my excel spreadsheet, I am trying to randomly sort a large list. I am doing this by adding in a new column to my table. This table is assigned a random integer between 1 and 10. This is done by =FLOOR(10 * RAND(), 1). Then I want to sort this list of numbers from smallest to largest.

All of this is working except the sorting. When I try to sort it, excel changes the order of the numbers slightly, but it is not from smallest to largest.

Any ideas on how to fix this?


  • The rand() function recalculates itself. You need to copy and paste values, then sort. – Raystafarian Jun 9 '15 at 18:15
  • If you're getting your data sorted based on the first set of random numbers, isn't that good enough?  Do you need to maintain a record of the random values that were the basis for the sort? – Scott Jun 10 '15 at 4:14

Your rows are sorted by the previous values of =RAND().

After sorting, the =RAND() function is called again.

Turn off Automatic Calculation from Formulas (ribbon), Calculation Options, Manual.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.