Lost data when saving from .xlsx doc to .xls format

[SalDog]

I have a client who was working on a .xlsx document in Microsoft Excel
2008 that contained 300,000+ rows of data. After spending hours
working on it, they saved the file with the .xls extension (my
suggestion) and it seems they only have 62,00o rows of data. I have
made it a policy at my client's company to save all files as .xls
files due to the compatibility issues they run into when sharing with
the outside world.

Can someone tell me if a .xlsx document that contained well over 300K
rows would drop most of the rows if you saved the file as an .xls file
format?

Please let me know. Thank You.

 

[CyberTaz]

Well, certainly it would - the .xls format only supports 65,536 rows per
sheet, whereas the .xlsx format supports 1,048,576. However, when the file
was saved there would have been a message appear advising the user of what
was going to happen if they confirmed the save. They either did not
understand it or chose to disregard it.

I'm not sure what "compatibility issues" you're trying to avoid, but there
is most likely a better solution than using the .xls format. I'm afraid you
can't have the 'best of both worlds'

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

I have a client who was working on a .xlsx document in Microsoft Excel
2008 that contained 300,000+ rows of data. After spending hours
working on it, they saved the file with the .xls extension (my
suggestion) and it seems they only have 62,00o rows of data. I have
made it a policy at my client's company to save all files as .xls
files due to the compatibility issues they run into when sharing with
the outside world.

Can someone tell me if a .xlsx document that contained well over 300K
rows would drop most of the rows if you saved the file as an .xls file
format?

Please let me know. Thank You.

The .xls format is limited to 65536 rows. this can not be altered and is
why your data is truncated.

 

[Jim Gordon Mac MVP]

I have a client who was working on a .xlsx document in Microsoft Excel
2008 that contained 300,000+ rows of data. After spending hours
working on it, they saved the file with the .xls extension (my
suggestion) and it seems they only have 62,00o rows of data. I have
made it a policy at my client's company to save all files as .xls
files due to the compatibility issues they run into when sharing with
the outside world.

Can someone tell me if a .xlsx document that contained well over 300K
rows would drop most of the rows if you saved the file as an .xls file
format?

Please let me know. Thank You.

Hi SalDog,

If you're aiming for backwards compatibility with older versions of
Office, you'll have to split your data into many more sheets. As others
have told you, the maximum number of rows in .xls is 65,536 - but
there's no limit on the number of sheet tabs.

If you're working with large data sets, I say forget about .xls
altogether. You might find saving in the new Excel binary format .xlsb
to your liking, as the files may be considerably smaller than even .xlsx
and save and open faster, too.

-Jim