Computer Tutoring Logo

Building Data Models with PowerPivot - Access Database

Start Building Data Models

Are you building data models in PowerPivot? Where do you start? This training video will show you how to use PowerPivot to connect to an Access database.

However, if you are wanting to import data from Excel into PowerPivot, you can use this Creating Data Models in PowerPivot step-by-step page.

Steps to Connect to an Access Database and Import Tables

  1. Open Excel and go to the PowerPivot tab.
  2. Launch PowerPivot: Click on Manage to open the PowerPivot window.
  3. Import Data: In the PowerPivot window, click on Get External Data and select From Access.
  4. Choose Database: Browse to the location of your Access database file (e.g., Adventure.accdb) and click Open.
  5. Select Tables: In the Table Import Wizard, select the tables you want to import and click Finish.
  6. Review Imported Data: Once the import is complete, review the imported tables in the PowerPivot window.

Steps to Create a Data Model and Pivot Table

  1. Create Relationships: In the PowerPivot window, go to the Design tab and click on Create Relationship. Define relationships between the imported tables based on common fields.
  2. Switch to Excel: Close the PowerPivot window and return to Excel.
  3. Insert Pivot Table: Go to the Insert tab and click on Pivot Table. In the Create PivotTable dialog box, select Use this workbook’s Data Model and click OK.
  4. Build Pivot Table: In the PivotTable Fields pane, drag and drop fields from the imported tables to build your Pivot Table.
  5. Analyze Data: Use the Pivot Table to analyze your data, applying filters, slicers, and other tools as needed.

By following these steps, you can connect to an Access database, import tables, create a data model, and build a Pivot Table in Excel using PowerPivot. This process will help you manage and analyze your data more effectively.