Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

A Question about Excel's Column and row limit 4

Status
Not open for further replies.

fabraldr

Technical User
Sep 10, 2003
16
NZ
I know that Excel is limited to 65,536 rows and 256 columns. I was wondering about the reasoning behind this as Quattro pro can have over 18,000 columns and 1 million rows. Is it because microsoft would rather have you use a database instead of excel for data that exceeds this limit? Any help/explanations would be appreciated!!
 
fabraldr,

I can't say what MS's reason for limiting Excel to 256x65536 is. My assumed answer is that a byte can hold 256 discrete values, and a word (2 bytes) can hold hold 65536 discrete values.

I'm sure they could program Excel to handle more rows and columns. However, I think if a spreadsheet approaches either of these limits, it is seriously time to consider using a database anyway.

You do pose an interesting question though. Why stick with 256x65536 when the competition has surpassed you? My guess is, (for better or worse) MS doesn't consider Quattro Pro viable competition.

Steve
 
It's the size I think. Quttro Pro may technically have the rows to handle that amount of data but I wouldn't want to work regularly with a spreadsheet that had > 30000 rows never mind 1 million !!

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question
 
From a web page:

Every Excel worksheet is limited to 256 columns. Despite what must amount to thousands of requests over the years, Microsoft refuses to increase the number of columns in a worksheet. Beginners often discover this limitation when they want to set up a spreadsheet that contains data for each day in a year. If they store the data horizontally, they run out of column in mid-September.

So we're stuck with 256. Why such a weird number? Why not 250? Or 365? The number of rows and columns is a by-product of the binary number system. 256 is 2, raised to the eight power (2^8), which is the maximum value that can be stored using eight bits. The number of rows in a worksheet is 65,536, which is 2^16. Older versions of excel contained only 16,384 rows, which is 2^14 power.

The reason for the 256-column limitation is probably due to the fact that Excel is so old, and it contains lots of code that would be "broken" if the number of columns were increased.

Following are a few related facts:

* Corel's Quattro Pro spreadsheet supports 18,278 columns and up to 1,000,000 rows.
* The spreadsheet in Microsoft's Office Web Components supports 18,278 columns and 262,144 rows


[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...
 
Most of the size limitation is based on available memory. Suppose that you filled every available cell in every available row & column in Quatro Pro with 6 digits. Do the math and you'll see that you need 10 gig of memory just to open the spread sheet. Would anybody every do that? Probably not, but I've known some users who are more "visual" than conceptual and rather than create a database with the requisite input and search forms, they'll fill up and plod over a spreadsheet like accountants used to to do with ledger books. They think that "seeing" all the data on their screen makes it more "real".

Spreadsheets are very memory intensive and, as sfvb pointed out, the larger the spreadsheet, the more attractive a database looks.

There's always a better way. The fun is trying to find it!
 
I would like to say thank you to everyone who has responded so promptly to my posts!!! Keep it up guys!!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top