Why excel just have 65536 lines

[Hawk1979]

Hi ,

i would like to know why does excel have only 65536 , and how can i use more than 65536, i hear about a Vba Spread sheet with more than 65536



for your responses thaks ...

 

[DRJ]

256 columns and 65536 rows is the limit and probably will be for ever since a lot of the code in Excel would probably blow up if these were increased.

You can get Quattra Pro which has a million rows and 16000 some odd columns or you can use Access and import smaller amounts of data into Excel.

 

[Chitosunday]

Excel was made specifically as a worksheet and for small database. For large database, use access. It is more efficient and organized for handling large data.

 

[parry]

Youve probably asked the #1 question on the Excel "why isnt there..." list, I cannot think of a good reason why MS have not expanded the number of rows/columns (it has been this way for a long time).

Cynical people may suggest its because they have a 95% share of the spreadsheet market so theres little incentive to make large changes. I guess theres a fair bit of work in redeveloping Excel to accomodate more rows/columns and that could be a contributing factor.

Personally, if I am approaching 65K rows then Excel starts becoming decidely sluggish so I dont think I would use more rows even if they were availabale. You would have to start thinking of a database with 60k+ records but it depends upon the task you are doing.

 

[dk]

Hi,

In addition to what's been said, if you really need to use Excel then you might take a look at the Office Web Components Spreadsheet Control. It has 262,144 rows and 18,278 columns. If you decide to use this then there are plenty of people here who can help you if you get stuck.

HTH,
Dan

 

[DRJ]

Like Parry said even if you could get more rows excel would be very slow so you wouldn't even want then. Access is the perfect program to compliment Excel. Store all the data in Access. Querry Access for the data you want, export to Excel to do calculations as needed.

I don't mind the rows limit since there are plenty imo, but I would really like at least 512 columns for data.

 

[Ekim]

For the Techies:

Computers operate using a base-2 number system (Binary) - data is either a 0 or a 1. There are 8 bits in a byte. With 8 bits in a byte you can represent 256 binary combinations (from 0=00000000 to 256=11111111). The number of rows and columns is a by-product of this 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 or 256*256 (256*2).

For the conspiracy theorists (sighted on Net):

If you look at the number 256:
2 = B = Bill
2+5 = G = Gates
2+5+6= M = Microsoft

For the cynic:

Limiting Excel means that some people are compelled to use MS Access. A viable Access market means Microsoft can lay claim to being in the “database business”, in addition to spreadsheets, word processing, project management (MS Project), web browsers etc. etc. etc.

For the hopeful:

Increasing the current row and column limits will apparently require a complete re-write of the MS Office suite, not just Excel. Don't hold your breath

Regards,

Mike

 

[darkulti]

Ekim, Thanks for your excellent explanation.

 

[Zack Barresse]

Mike, you never cease to amaze me!

 

[Ekim]

In all the excitement of addressing the OP’s question, nobody asked what the OP was doing. So, Hawk, what are you doing that requires more than 65,536 rows? Here are a few options if you are importing a text file:

Importing Text Files Larger Than 16384 Rows
http://makeashorterlink.com/?Q20B13408

Written for Excel 95 (limit of 16,384 rows) but the macro includes a note to change the row limit to 65,536.

If the text file is > 65,536 rows, the macro imports the data into the first sheet to row 65,536 and then automatically adds new sheets, as required, to import the rest.

Or:
Use a text editor and split the text file into smaller pieces. Then import the data into Excel, bit by bit, into different sheets.

Or:
A nifty way using Excel’s Text Importer (notes posted by S Barnett):
http://makeashorterlink.com/?B66B25408


Regards,

Mike