Analyzing Data in Excel

Before you can analyze Access data in a workbook, you must convert it to an Excel file. You can either use the Analyze It With MS Excel command in Access to export data as an Excel table file or use the PivotTable And PivotChart Report wizard in Excel to use the Access data as a PivotTable , a table you can use to perform calculations with or rearrange large amounts of data. Once you determine what fields and criteria you want to use to summarize the data and how you want the resulting table to look, the wizard does the rest. You can also copy and paste or drag Access database objects (tables, queries, and so on) from the Database window to an Excel worksheet.

Export an Access Table into an Excel Workbook

In the Access Database window, click the table you want to analyze.

Click the OfficeLinks button on the Database toolbar.

Click Analyze It With Microsoft Office Excel to save the table as an Excel file, start Excel, and then open the workbook.

Use Excel commands to edit and analyze the data.

Create an Excel PivotTable from an Access Database

In Excel, click the Data menu, and then click PivotTable And PivotChart Report.

Click the External Data Source option button.

Click Next to continue.

Click Get Data, if necessary click Yes to install Microsoft Query, and then follow the wizard instructions to select the Access data.