We need to add rows beyond the maximum on an Excel worksheet

Our office is downloading data from our database, but it takes more rows than our Excel worksheet allows.  Is there a way to increase the maximum rows on a worksheet?

Answer
Answer

.xls workbooks have 65,536 rows per sheet.

.xlsx, .xlsm and .xlsb workbooks have 1,048,576 rows per sheet.

These limits are hard-coded and cannot be changed.

---
Kind regards, HansV
https://www.eileenslounge.com

55 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

You can't increase the number of rows in a spreadsheet in Excel 2007. Excel 2010 has the ability to use the Power Pivot Add-in which has no row limit.

Two workarounds if data is exported into a text file (CSV):

1. Pivot table.

2. MS Query

     Export the data into a text file (CSV).

     In Excel 2007 - Data menu, in Get External Data Group select From Other Sources

     From the menu select From Microsoft Query

     From Choose Data Source, select <New Data Source>, click OK

     Enter a name for the data source, i.e. "Test" in the What Name do you want to give your data source?

     In response to Select a driver for the type of database you want to access, select "Driver da Microsoft para arquivos texto (*.txt, *.csv)"

     click Connect

     deselect "Use Current Directory" if the data is in a different path, the click select Directory to navigate to the data.

     click OK

     In response to "Select a default table for your data source (optional), you can select from the drop down selection

     click OK

     make sure that the new datasource is selected in the Choose Data Source dialog box

     click OK

     continue to follow instructions for the MS Query Wizard - eventually you will be able to use filters to reduce the number of rows in your data.

 

 

60 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated February 23, 2021 Views 94,169 Applies to: