Re configuring an excel sheet for less columns and more rows

Copper Contributor
Hello, I don't post in here often, but there is one question I've always wondered about with Excel. I use Excel to process RAW DNA data from direct to customer testing companies like Ancestry, 23andme, FTDNA etc. I would like to make a combination of several kit results which will easily potentially have 2-3 Million rows, which exceeds what seems to be allowed even in 64bit Excel. So a basic sheet consists of millions of cells, and as the standard configuration is abt. 1.2M rows and I don't know how many columns? My application uses only 6-10 columns, so is there any way to better use the memory allocated to a sheet by restricting the column count so that the row count can be significantly extend 2-3 M ? The current work round is to process the combining by splitting the rows into several sections of abt. 250K rows, thus splitting the chromosome results into 1- 5, 6-10 10-15 and 16-22, or even by individually CHR1 through CHR22, finally using a text editor as in Notepad ++ to assemble the final results in .CSV format.
 
 
4 Replies

@MikeABarry 

An Excel worksheet has 1048576 rows and 16384 columns. This is hard-coded and cannot be changed. So you'll have to keep on splitting the data into multiple sections.

@Hans Vogelaar 

Thank you Hans, maybe Microsoft would consider making Excel more flexible and allow the user to define the boundaries, I did read that a work sheet is only saved using the formatted cells to save memory so maybe there is a mechanism!?  It's a very inefficient method to work through these sheets and re assemble the whole thing at the end.  I have made tools in Python before , so back to the learning curve to make this a straight forward exercise that others can make use of without in depth knowledge.

@MikeABarry 

You can submit a suggestion to Microsoft from within Excel, by selecting File > Feedback > Send a suggestion, or by voting for The maximum row of excel is 1048576. Yes. Can you increase the number of rows? 

Thank you for the link I have made a comment