Overcome Excel's 65,536 Limit: Create Pivot Tables from Access Data
Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada. The tip in this show is from Pivot Table Data Crunching.
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. For the BEST TV show on technology, check out Call for Help. This tip was originally published on October 10, 2005 and aired in Canada and Australia on November 3, 2005. It premieres in the USA on December 9, 2005. The permanent URL for this page is http://www.mrexcel.com/tip102.shtml. If you are looking for show notes from another episode, visit my complete list of TechTV appearances.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
Excel is a registered trademark
of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.
All contents Copyright 1998-2008 by MrExcel Consulting.