Excel: Create a Pivot Table from Access Data

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: I have 10 kazillion records in an Access table. I would like to create a pivot table for this data.

Strategy: You can create a connection to the Access table and build the pivot table in Excel. Follow these steps:

  1. Start with a blank Excel workbook.
  2. Select Data, From Access.
  3. Browse to your Access database and click Open.
  4. The Select Table dialog shows a list of all the tables and queries in the database. The Type column says VIEW for queries and TABLE for tables. Choose the desired query or table and click OK.
  5. In the Import Data dialog that appears, choose to create a pivot table report and click OK.

Results: Excel will display the PivotTable Field List dialog, with all the fields from your table or query.