Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

What Is The Maximum Number Of Rows In An Excel Spreadsheet ?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have a csv file that I try to import into Excel and the number of rows is
larger thatn 65536. The Excel 2000 seemed to be only capable of loading 65536
rows. Is there a way to overcome this ?

View Answers     

Similar Excel Tutorials

Increment a Value Every X Number of Rows in Excel
How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial teaches you how to build ...
Delete All Rows that Contain a Specific Value in Excel
Quickly find all rows in Excel that contain a certain value and then delete those rows. This is a simple technique ...
Delete All Empty Rows or Blank Cells from a Range in Excel
How to quickly delete all empty cells or rows from a range in Excel.  This allows you to quickly clean your data to ...
Hide Data Within a Worksheet in Excel
In Excel you can actually hide data that is stored within a worksheet. This allows you to show data that is useful ...

Helpful Excel Macros

Delete Rows in Excel if Completely Empty
- This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, ru
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Delete Blank Rows in Excel
- This is a macro which will delete blank rows in excel. This version will delete an entire row if there is a blank cell
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Delete Duplicate Rows
- This macro will delete rows that appear twice in a list or worksheet. If two cells are identical, this macro will delete

Similar Topics

Hi guys.

I've been searching through the forums, but I can't seem to find anything on the subject.

I'm using the worksheet funtion "LinEst" in VBA to make regression in a monte carlo simulation. It works just fine with a low number of simulations, but I would like to make 100,000 simulations and then I get a type mismatch error message from the worksheetfunction.linest. I figured out through trial and error that the error occurs, when I try to do more than 65536 simulations. Doing a quick search on google I found out that 65536 was the maximum number of rows in previous versions of Excel. I use 2007, but it seems that the LinEst function hasn't been updated to handle the larger number of rows in Excel 2007. Does anybody know how I can resolve this issue or have any other piece of advice?



I need to import a csv file that has more than 65536 rows of data and 20
columns, is there a way of setting up a vba macro so that once the 65536 has
been reached, the continuation moves onto the next sheet?

the maximum of number of rows that are available in an excel sheet are only
65536,i would like to enhance it to my choice. Plase mail me how can i do it
,either by writing a macro , or other way. please also mail me the entire
procedure, where to correct and where to run the macro etc.


In Excel the maximum number of rows i can get is 65536. Is it possible to
extend this so i can get about 67000 rows?

Kind regards

Allan Bach


I have a 10MB .csv file having some 200,000+ rows and 3 columns.
I obviously can open the file in any other plain text editor.
But when I open this in MS Excel 2003, I get the msg File Not Loaded Completely.
The details are

This error is usually encountered when an attempt to open a file with more than 65,536 rows or 256 columns is made. Excel is limited to 65,536 rows of data and 256 columns per worksheet. You can have many worksheets with this number of rows and columns...

So how would I go abt saving this entire .csv file as .xls ? The latter part (after 65536) is getting truncated.
Is there any solution to this ?


I am working w/Excel 2007, and the maximum number of rows available is defaulting to the older excel 2003 allowable rows of 65536, instead of the over 1 million rows. I'm looking for where the default is maintained, and haven't had any luck. Any ideas?

Hello guys, I need to know is there a way to have Excel Go beyond 65536 when generating from other files? I heard there's a method where it creates a new sheet when the 65536 is filled up but I'm using these data for analysis, so i'd need to plot graphs etc so is it possible to plot 1 graphs for all the data gathered in few worksheet?

Anyone can tell me why Excel limited the max rows to 65536?

hi membes,

i wan t to import data of about 800000 rows from a data base to ms excel but there are just 65536 rows in excel sheet.
is there any way thrrough which i could import data or could increase the row from 65536 to 80000.

hi membes,

i wan t to import data of about 800000 rows from a data base to ms excel but there are just 65536 rows in excel sheet.
is there any way thrrough which i could import data or could increase the row from 65536 to 80000.

hi membes,

i wan t to import data of about 800000 rows from a data base to ms excel but there are just 65536 rows in excel sheet.
is there any way thrrough which i could import data or could increase the row from 65536 to 80000.

hi membes,

i wan t to import data of about 800000 rows from a data base to ms excel but there are just 65536 rows in excel sheet.
is there any way thrrough which i could import data or could increase the row from 65536 to 80000.

I've got several workbooks with more than 65536 rows. When I try to open it in excel, i am informed via pop-up message that it will not open a document with more than 65536 rows. (They are in CSV format). It tells me to try to open it in word and then copy it into excel. Has anyone ever experienced this? Is there an efficient way to circumvent it with Excel 2003 and microsoft word? What should I do?

Thank You,


Hi i need have 50 000 data in one column.. so meaning 50 000 rows flowing down.. excel has the limit to 65536 rows..

I need to plot the normal distribution curve of the 50 000 datas in matlab.. these data are now in separate files in excel.. but i need them to be combined into a file so i can do my curve.. is there any way i can have a flow if 50 000 data in a excel file?




How can we put in data on more than the limit for excel - 65536 rows. I don't want to put them in another worksheet coz I have to link this with another one using vlookup- which is very convenient for a single table

please help.


I cannot find a way to have the following formula automatically increment the row number when I drag it down a column. I do not want to take a different approach. This formula works very well for what I am doing but I have to drag it down 60 rows in a column and do it for several colums and I do not want to hand change it every time. Also, note that I cannot use ROW() because it is reporting data from a different row that the formula is in.


=INDEX($F$3:$F$65536,(MATCH(9.99999999999999E+307,$F$3:$F$65536) -59 ))

What I want is to drag down a column and have it do the following:

=INDEX($F$3:$F$65536,(MATCH(9.99999999999999E+307,$F$3:$F$65536) -59 ))
=INDEX($F$3:$F$65536,(MATCH(9.99999999999999E+307,$F$3:$F$65536) -58 ))
=INDEX($F$3:$F$65536,(MATCH(9.99999999999999E+307,$F$3:$F$65536) -57 ))

Any help would be greatly appreciated as I have been banging my head against the wall on this one. Thanks.

As I know the number of the rows in one sheet in excel is 65536. The question is can we increse the rows of the sheet more than 65536.

Using Excel 2003 I have a macro to run the Text Import Wizard.

How do I code the error trap and reporting prior to terminating the macro when the file to be imported exceeds the maximum allowed number of rows, 65536.

Your help is appreciated.

Hi All

I'm struggling to import data into Excel 2003 from a text file which will produce more than 65536 rows of data...

Normally when this occours I use a routine that has served me well in the past from, but it's not working for me this time.

The format of the file appears to be both TAB and , (comma) delimited, when I try to import the file from the 'Data > Import External Data' and select 'TAB' and / or 'Comma' as the delimeters the file imports fine up to the row limit.

When I tried to use the code linked to above the results where just one row of data upto Column IV and nothing else. (It should only produce two columns of data)

I changed the line;


SplitChar = ","



SplitChar = vbTab

And this gave me an Out of Memory error on the section;


            If Colndx + C_START_COLUMN

I have to generate a piviot table based on excel data,but the data is crossing more than 65536 rows,which is maximum an excel sheet can hold.

Is there any alternative to generate pivot table?

Believe it or not I need and excel sheet with more than the given 65536 rows. Is there a way to get more rows or is that the maximum available?

I am trying to find the number of rows in my worksheet. If I do a CTRL END from Excel it lands on row 42 but when I use the following VB code:
QueryRowCount = Worksheets("Sales").Rows.Count
I get 65536 rows
Please help,

Would someone mind letting me know why the following code falls over once it gets to the cells(i,k).formula =

Sub weekload()

finalrow = Cells(Rows.Count, 2).End(xlUp).Row

j = InputBox("Enter Week Number", "Week Number")

k = Cells.Find("Week " & j).Column

For i = 5 To finalrow

Cells(i, k).Formula = "=IF(ISERROR((IF(VLOOKUP($D5,'Staff Data'!C:U,18,0)"",VLOOKUP($D5,'W:\[Week 23.xls]allstat'!$A$1:$C$65536,2,0)+VLOOKUP(VLOOKUP($D5,'Staff Data'!C$5:U$811,18,0),'W:\[Week 23.xls]allstat'!$A$1:$C$65536,2,0),VLOOKUP($D5,'W:\[Week 23.xls]allstat'!$A$1:$C$65536,2,0)))),0,(IF(VLOOKUP($D5,'Staff Data'!C:U,18,0)"",VLOOKUP($D5,'W:\[Week 23.xls]allstat'!$A$1:$C$65536,2,0)+VLOOKUP(VLOOKUP($D5,'Staff Data'!C$5:U$811,18,0),'W:\[Week 23.xls]allstat'!$A$1:$C$65536,2,0),VLOOKUP($D5,'W:\[Week 23.xls]allstat'!$A$1:$C$65536,2,0))))"

Next i

End Sub

I am hoping this would be ok, what I also need to know is, in my excel formula I am looking up a workbook called 'Week 23' how can I have this change dependant on what is in the input box ?

Thanks in advance



Just wondering if there's a way for us to add rows more than 65536 rows in Excel?

I am trying to write a macro that will allow me to open a .csv file that has more than 65536 rows. Is there a way to open this file in which lines beyond row 65536 will open in a separate tab?