Forum Posts Feed
OT: Spreadsheet software for very large files
< Next Topic | Back to topic list | Previous Topic >
Posted by dan7000
Oct 1, 2014 at 11:08 PM
Have been looking for this for a few days and just realized maybe the CRIMP community would have some ideas.
I’ve been working with some large spreadsheets lately (100,000 rows, 50 or so columns). The main thing I need to do with them is just sort and filter on different columns. But in Excel, every time I sort or filter on a new column it takes 5 minutes and up. I’ve tried Excel 2010 and 2013, same thing. This seems like something that modern software should do better, but I can’f seem to find any alternatives. I tried uploading to Google Sheets but despite the claims on the Sheets website (claiming to have no size limit), it fails with an error that the file is too large. I tried uploading in sections but you cannot copy and paste the sections together because it hangs attempting to copy/paste large numbers of rows. I guess I could type it all into a new Google Sheet one row at a time if I wanted to kill a week or so.
Any suggestions for online or windows-based software that can work with this size of spreadsheet without hanging?
Posted by moritz
Oct 2, 2014 at 12:58 AM
You should check out Excel 2013 Power Pivot (... typically in combination with Power Query and Power View).
We have done some work for clients to re-platform massive spreadsheets (e.g. 10m cells, 4m formulas) into the new “in memory BI” paradigm.
Results are very impressive: Spreadsheet size came down from 300 MB (same example) to 20MB (!!), number of formulas reduced from 4m down to 40 (by separating out the data transformation part via the tabular model and Power Query transformation rules).
Official limit on number of rows is 2 billion, the engine is quite fast as well (almost everything runs real time).
Powerpivotpro.com is a good resource to get you started.
One downside (could be a deal breaker for you): The PowerPivot capabilities require Excel 2013 “Professional” (or Office 2013 Professional or equivalent Office 365 subscription). Not included with “Home” versions of the software.
Posted by jimspoon
Oct 2, 2014 at 04:02 AM
What’s your hardware, how much RAM? I have used Excel 2007 to filter a spreadsheet with well over 100,000 rows, and it was very fast. Sorting went quickly too. Are the filters and sorts very complex?
I had much better luck with Excel 2007 than OpenOffice Calc.
Posted by Stephen Zeoli
Oct 2, 2014 at 11:15 AM
I suggest giving Panorama Sheets a try:
http://www.provue.com/panoramasheets/
It’s really a database that behaves like a spreadsheet—supposedly. I don’t use the application myself, so I’m not recommending it. I’m just saying it might be an alternative that would work and they have a free trial, so you can give it a go before you buy.
Steve Z.
Posted by dan7000
Oct 3, 2014 at 01:20 AM
Thanks all!! I’ll definitely try powerpivot and panorama sheets and let you all know the outcome. I have a relatively new lenovo laptop with 8GB RAM and Windows 7 So I’m not sure what makes it so slow.