Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm stuck with excel 2003 at the office, I was wondering if there is a way to extend the number of rows to at least a million or two.

share|improve this question
Excel does support more than 65k rows of data for data sources feeding pivot tables. The only other way would be to upgrade or split your data up into chunks of less than 65k. – Dan Jun 18 '10 at 22:13
Excel 2010 on my machine topped out at 1,048,576 rows. – Hello71 Jul 19 '10 at 19:04

6 Answers

up vote 4 down vote accepted

Quite simply: You can't get there from here.

You could try to bodge something together by splitting the data across multiple worksheets, but only you will know how well that's going to work.

If you want to go past 65K rows, you either upgrade to Excel 2007 or newer, which supports over 1 million rows (probably 1,048,576 or 2^20) or you quit using Excel.

Edit: I'd like to also reference Pablo Rodriguez's Answer which indicates that Go-OO, an OpenOffice.org variant, also supports 1 million rows. If your macros and formulas can be translated, this might be a worthwhile possibility. Presumably any other OOo variants based on Go-OO (e.g. NeoOffice & OxygenOffice) also benefit from this change.

share|improve this answer
up vote 1 down vote

Sorry, it's just not possible. Your company might not want to pay for an MS Office upgrade, but maybe you could try Open Office?

share|improve this answer
Nope. Just tried it. OpenOffice has the same limitation. – Kenneth Cochran Jun 18 '10 at 16:42
And Google Docs spreedsheet topped out at 30100. – Kenneth Cochran Jun 18 '10 at 16:45
OpenOffice.org v3.2 on my machine tops out at 1,048,576. It's an improvement over MS Excel, but still not going to help you unfortunately. MS Excel only supports >65k as of Office 2007. – ThatGraemeGuy Jun 18 '10 at 17:47
Hmm, I'm using OOo 3.2 as well but it maxed out at 65k. Are you running a 64-bit version by any chance? – Kenneth Cochran Jun 18 '10 at 18:10
Graeme, I'm not sure I understood your comment, but MSOffice 2007 does a million lines – dassouki Jun 25 '10 at 11:04
up vote 5 down vote

I would suggest if you have that much data you need to consider moving to a database rather than a spreadsheet. If your version of Office came with MS Access, this is a reasonable solution for the immediate future. If your data continues to grow you'll eventually need to migrate to a different DB because Access eventually tops out as well (around 2GB of data I believe).

share|improve this answer
That data sits in a PostgreSQL db; however, sometimes I'd like to do some stats on the data, normalization plots, Pearson distributions. usually I do this in R, but sometimes, it's just a lot faster and convenient to do it in my excel templates. It's usually fine in most cases; but if i'm doing a per year analysis, I'll need mullion+rows – dassouki Jun 18 '10 at 17:00
2  
In that case you may want to consider a reporting tool to create repeatable reports that crunch the numbers and handle formatting for you. – Kenneth Cochran Jun 18 '10 at 17:46
What in your Excel templates makes it faster and more convenient? I mean, loading that much data into a spreadsheet and then opening it up in Excel has to be a PITA. I'm not familiar with statistical analysis, but couldn't you program your formulas as stored procedures or something? Either that or use a reporting tool as Kenneth suggests. It seems more direct to just do the stats in the database than to dump all the data into a spreadsheet and use Excel to perform the calculations. – Lèse majesté Jun 19 '10 at 1:18
pivot tables make it faster – dassouki Jun 23 '10 at 14:27
up vote 0 down vote

We had an issue along these lines a year or so back. I realize your post says you are stuck with MS Office, but I do believe that trying to make that work is an exercise in futility.

We ended up using a linux machine with kOffice. It handled our file just fine. I'm not entirely certain it'll handle MILLIONS, but it handled 45-50k rows fine.

share|improve this answer
office 2010 can handle up to 1,000,000 lines ... I do most of my analysis in R; however, sometimes it's just easier to do stuff in excel. – dassouki Jun 18 '10 at 17:43
up vote 1 down vote

Go-oo is an OpenOffice variant that supports 1 million rows. There is a way to hack the code and get 2 million rows, but you'll have to recompile.

As stated here some Linux distributions of OpenOffice come with the 1 million row limit instead of the lower default.

share|improve this answer
up vote 0 down vote

You can read a million or more rows of data (depends on the number and type of the columns) into an Excel 2003 Pivot Table (limit is a 1 Gigabyte working set), even though the Worksheet limit is 65K rows.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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