Extend the number of rows in Excel 2003 beyond the allowable 65k

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.

dassouki

Posted 2010-06-18T16:33:08.290

Reputation: 982

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. – Jarvin – 2010-06-18T22:13:12.583

Excel 2010 on my machine topped out at 1,048,576 rows. – Hello71 – 2010-07-19T19:04:03.117

Answers

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.

afrazier

Posted 2010-06-18T16:33:08.290

Reputation: 21 316

What if we have excel 2016 and we need ten million rows? Is there a simple registry key or something we could edit? – wizlog – 2016-07-19T13:45:50.350

@wizlog: No, there's no kind of configuration you can change to make Excel support more rows. Even Excel 2016 x64 is limited to 1,048,576 rows. If you have ten million rows of data, Excel is the wrong tool for the job. It's time for a database and/or more sophisticated analysis software like R or SPSS. – afrazier – 2016-07-19T15:20:07.730

thank you for the reply. I assumed it wouldn't be so hard as data tables can have an unlimited number of rows. – wizlog – 2016-07-19T18:59:14.970

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).

Kenneth Cochran

Posted 2010-06-18T16:33:08.290

Reputation: 2 109

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 – 2010-06-18T17:00:37.257

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 – 2010-06-18T17:46:03.167

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é – 2010-06-19T01:18:05.773

pivot tables make it faster – dassouki – 2010-06-23T14:27:27.637

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?

Eric Petroelje

Posted 2010-06-18T16:33:08.290

Reputation: 403

Nope. Just tried it. OpenOffice has the same limitation. – Kenneth Cochran – 2010-06-18T16:42:36.860

And Google Docs spreedsheet topped out at 30100. – Kenneth Cochran – 2010-06-18T16:45:57.510

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 – 2010-06-18T17:47:39.343

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 – 2010-06-18T18:10:15.450

Graeme, I'm not sure I understood your comment, but MSOffice 2007 does a million lines – dassouki – 2010-06-25T11:04:17.523

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.

Pablo Rodriguez

Posted 2010-06-18T16:33:08.290

Reputation: 316

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.

Aeo

Posted 2010-06-18T16:33:08.290

Reputation: 1 472

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 – 2010-06-18T17:43:53.080

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.

Charles Williams

Posted 2010-06-18T16:33:08.290

Reputation: 31

If you need 400,000 rows in Excel 2003 you can open a flat (.txt, .prn, ...) file that has 400,000 rows in it. Excel will allow and create the 400,000 rows. It will give you a lot of warnings about being able to handle it, but I run some heavy duty macros that place formulas in the cells and I have no problem getting it to work.

Don

Posted 2010-06-18T16:33:08.290

Reputation: 1