How can I process million rows of data in Excel?

Question

I understand that Excel can only process 65,536 rows of data. If I'm trying to analyze 7 million rows of data (say, plot a histogram distribution of 7 million values), is there any workaround?

Can I still sort all 7 million values even if only 65K are displayed?

I'm using Excel 2011 for Mac.

Solution

the following excel restrictions per worksheet apply:

• excel 2003: can handle 65k rows
• excel 2007 (and later): can handle 1M rows

to process data which exceeds this row restrictions you have to handle them in multiple worksheets (which can get quite complex, depending on the data you have to analyze...)

note:
sorting this amount of data in excel is no fun... you should consider to process the data in a DB and then use the result(s) in excel (if really needed...)

Comments

[user5249203]

See stackoverflow.com/questions/5119687/…

[user5249203]

The really great thing about pondering superuser questions like this is that occasionally it leads you to irrelevant but fascinatingly weird stuff like metaphysicaldeveloper.wordpress.com/2011/07/31/… :-)

[Linker3000]

+1 The data has outgrown a spreadsheet and needs a database - perhaps Access with an Excel front end

[surfasb]

It amazes me how many people try to force Excel to be database.