Overcome Excel's 65536 Limit
October 10, 2005
To try this tip on your own computer, download and unzip CFH274.zip.
Excel offers 65,536 rows of data on a single spreadsheet. What happens when you have more data than this? Many people turn to Access – but the great news is that you can use that Access data and still do Excel Pivot Tables on the data to produce quick summary reports.
You have a table in Access with 83,000 rows of data. This is more data than can be captured in a single Excel 2003 worksheet.
Start with a blank Excel workbook. From the Data menu, select PivotTable. If you are using Excel data, you can usually click right through steps 1 & 2 of the Wizard. In this case, you have to change the settings. From the data menu, select PivotTable and PivotChart.
In Step 1 of the Wizard, select External Data.
In step 2, click the Get Data button.
Choose an Access database.
This ancient-looking Open dialog shows that you are now actually running Microsoft Query instead of Excel. Browse to your database.
A list of tables appears on the left. Choose a table, and press the arrow button to move the fields to the right.
In the next 2 steps, you could filter the data or change the sort order. Just click Next through these. Choose to return the data to Microsoft Excel.
You are finally taken back to Step 2 of the Pivot Table Wizard. Choose Next. Click Finish in step 3 of the Wizard.
You now have a blank pivot table.
You can drag fields from the pivot table field list to various sections of the pivot report to create a summary report.
Storing your data in Access allows you to use Excel's most powerful feature - pivot tables - to analyze your data.
The tip in this show is from Pivot Table Data Crunching.