What is the maximum number of cells Excel can sort?

[pcvchriskmg]

Hi,

Here is my machine info: Windows 7, 64 bit.

Intel (R) Core(TM)2 Duo CPU T6500 @ 2.10 GHz, RAM 4 Gig

I have a column of 405,205 phone numbers that I am sorting.

I'm using the ALT+DFA to bring up the advanced filter. I am trying to copy the unique values to another column in the spreadsheet.

The little windows circle just goes around and around until I eventually get fed up and have to close Excel via the task manager. I've tried this without running any other applications on my machine, so as to not bog it down.

Is 405,205 cells too large a number for this operation given my system's resources? I've waited up to five minutes, which seems as if it would be enough time to complete the task.

The process goes more smoothly when I use the "remove duplicates" function found in the "Data" section of the ribbon.

If 405,205 values shouldn't be too taxing, are there steps I can take to remedy this?

Thanks in advance,
Chris

 

[EvilPanda]

Is 405,205 cells too large a number for this operation given my system's resources? I've waited up to five minutes, which seems as if it would be enough time to complete the task.

Having never worked with this amount of data in in Excel, I thought it would be a fun experiment to try. I first generated 500k random numbers in a text file, and then performed a sort/unique filter using the text-based "sort" utility (Cygwin). It completed it 0.028 seconds.

I then proceeded to import these 500k random numbers into an empty worksheet, and replicate the procedure using Alt-DFA and copying them to another column. The operation completes immediately (<1s). My system is almost identically specced to yours.

Does your sheet have any data except for this column of numbers? If so, how many columns?

Do the following test please. Right-click the sheet, select "move or copy" then "make a copy". Run the procedure on the copy of the worksheet. Does this affect the outcome at all?

 

[pcvchriskmg]

EvilPanda,

The sheet I was working within did have lots of columns with lots of data. Maybe 15 columns of data all between 50K and 450K cells per column. And there were multiple sheets like that within the file.

So I copied my single column of the data, the original 405,205 cells and placed it in its own file with nothing else.

I ran the advanced filter again, and got the same thing. This time however, I cut the time short, hit ESC after about 10 sec. since it wasn't immediate like your test.

So something must be interfering the with processing speed in Excel.

I'll try this again by using ReadyBoost to boost my system's RAM with a flash disk. Though not confident this is going to do the trick.