2

I have a long list of entries that I want to sort randomly. I highlighted a column, typed in =RAND(), and then pressed CTRL+SHIFT+ENTER, and voilà, my column was filled with random numbers. I then wanted to sort the column by smallest value to largest, to randomize my list. When I attempted this, I was given some sort of message saying that Excel cannot sort an "array."

So, I said to myself, Fine. I'll just make another column, paste those random numbers as values and continue on. Unfortunately, that did not solve the problem. Instead of telling me it cannot sort an array, it now pauses a bit and then erases all information in columns B and C and returns some different, yet still random numbers in column A. I even attempted to copy the data and repaste it into a another sheet to see if that would solve the problem, but it continues to do the same thing.

I did not find anything that really addressed this on the web or here in this forum, but I could have missed something, so I will continue to search for a solution, but if any of you Super User experts know why Excel does this and how to solve this problem, I'd love to hear what you have to say.

By the way, I am using Excel 2007, and I have used Excel's RAND function before and conducted successful sorts with this version of Excel, but I think I filled the cells the old-fashioned way -- grabbing the autofill crosshair and dragging it down through the cells. Those were for smaller sort jobs, however (maybe 100 or so records). I am now trying to do the same thing with 5,000 records and really don't want to use that method for all the obvious reasons.

Thanks in advance for any guidance you can give me on this.

Addendum: After posting this, I know why the cells in column B and C are blank. When I press CTRL+SHIFT+ENTER, Excel fills the entire column, but I only have 5,000 records. The easiest work around to this (off the top of my head) is to simply sort by column B or C, find the last entry and delete the rest of the rows. Having said that, what is the easiest way to fill a long list of cells with random numbers for Excel 2007? Is there some way that I can specify the exact cells I want it to fill with random numbers?

Improve this question

1 Answer 1

Reset to default
4

Whenever you press Ctrl+Shift+Enter, you're working with arrays, which have special rules. If you only want to fill a bunch of cells with a certain formula and avoid creating an array, enter your formula in one cell, then drag that cell's bottom-right grip to copy it to as many cells as you like.

Still, we have the problem that sorting the values causes a recalculation, and since the =RAND() formula is still in the cells, they'll be re-randomized and un-sorted. Therefore, you want to only generate the random numbers once, by converting the formulas to values once the randomness happens. Select the whole range of cells, then press Ctrl+C, Ctrl+V, Shift+F10, then just V. That copies, pastes, right-clicks, and sets the paste mode to "values only." It even works on the array you already created.

You can then sort the random now-static values as normal.

Improve this answer
4
  • I like your answer, @Ben N, even though it isn't exactly the one I was hoping for. To be honest, for what I'm doing right now, the recalculation bit isn't such a problem for me. I was hoping to be able to create some sort of formula such as =RAND(),A1:A5000, and then press enter and be done with it. But your answer does at least confirm for me that pressing CTRL+Shift+Enter automatically creates an array. Plus, you gave me some cool short cuts. Speaking of which, how do you create the cool formatting that you made for the short cuts? I like it a lot.
    – Lisa Beck
    Jul 14, 2016 at 20:08
  • 1
    I used the <kbd> tag. You can use that copy/paste trick to un-array something without turning formulas into values, though.
    – Ben N
    Jul 15, 2016 at 2:54
  • TY, @Ben N. Upon reviewing my answer, I see that I tack on an additional question to the end. I did some searching and found my answer for it, but can't remember where I found the information. It was some random web page I found. Be that as it may, if anyone has a similar question, you'll find the answer in this thread: Fill a large range with a formula in Excel, without mouse-dragging to extend. The top answer explains it quite well and even includes an image.
    – Lisa Beck
    Aug 6, 2016 at 16:02
  • 1
    I am going to mark Ben's answer as most helpful more for the fact that he took the time and effort to answer it than anything else. (After all, after two weeks it has no other contenders vying for that position.) Looking back over my initial question, I realize some faulty assumptions on my part regarding what was actually going on with the sort muddied the waters a bit. Nevertheless, I think some who stumble upon this question and answer may find it to be of some benefit, so rather than revamping it from scratch, I'm leaving it as is and giving Ben the checkmark.
    – Lisa Beck
    Aug 6, 2016 at 16:12

You must log in to answer this question.

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