Access Power Pivot Add-In: Use Access Data in Excel

You cannot add Power Pivot to Microsoft Access: Power Pivot is an Excel add-in, not an Access feature. To analyze Access records with Power Pivot, open Excel for Microsoft 365 on Windows, connect to the Access database, and load its tables or a query into Excel’s Data Model.

What Power Pivot and Access Each Do

Microsoft Access is a database application for storing and managing relational data, building forms and reports, and running queries. Power Pivot is a data-modeling and analysis feature in Excel. It lets you combine tables, define relationships, and create calculations using Data Analysis Expressions, usually shortened to DAX. The two products can work together, but Power Pivot does not run inside Access.

The practical distinction is where the analysis happens. Access remains the source of the records; Excel retrieves a copy or connection to those records and uses its Data Model for analysis. For example, a small organization might keep customers, orders, and products in Access, then build an Excel PivotTable that summarizes sales by customer and month. You do not need to move the database into Access in a different way or install a Power Pivot component in Access.

Access already supports relationships and queries of its own. Those database features are useful for maintaining data and preparing a selection of records. Excel’s Data Model is a separate model that can bring together Access data and other sources, such as Excel tables or files. A relationship defined in Access does not necessarily become a usable Power Pivot relationship automatically, so check the model after importing.

How to Enable the Power Pivot Add-In in Excel

First check whether your Excel edition includes Power Pivot. In Excel for Microsoft 365 on Windows, the add-in is generally enabled from the COM Add-ins list when it is available with your Office license. Availability depends on the Office edition and license; not every installation exposes the feature.

Open Excel and choose File > Options.
Select Add-ins in the Excel Options window.
At the bottom, set Manage to COM Add-ins, then select Go.
Check Microsoft Power Pivot for Excel or the similarly named Power Pivot entry, and select OK.

If enabled, a Power Pivot tab should appear on the ribbon. Choose Power Pivot > Manage to open the model window. If the add-in is not listed, do not look for it in Access: verify your Excel edition and organization’s license, and ask your Microsoft 365 administrator whether the feature is included. Repairing or reinstalling Office is not the first step when the license itself does not provide Power Pivot.

Connect Excel to an Access Database

With the add-in available, connect from Excel rather than from the Access ribbon. In Excel for Microsoft 365 on Windows, use Data > Get Data > From Database > From Microsoft Access Database. Browse to the .accdb or .mdb file, select it, and choose Import. In the Navigator window, select the table or saved query that contains the records you need. You can preview the data before loading it.

For a simple import, select Load. To put the records in the model used by Power Pivot, use the load settings: select Transform Data if you need to clean or filter the data in Power Query, then choose Close & Load To. Select Only Create Connection and check Add this data to the Data Model, then confirm. The exact presentation of the load options can vary slightly between Excel builds.

Choose tables or queries deliberately. A saved Access query can be a useful way to restrict rows or fields before they reach Excel, while importing separate related tables gives you more flexibility to analyze the data in the model. Avoid importing every table by default: unnecessary fields and records make the workbook harder to understand and can increase its size. A useful first example is an Orders table plus a Customers table, with only the fields required for reporting.

Build a Model from Imported Tables

After loading, select Power Pivot > Manage to inspect the tables. Check that columns have sensible names and data types. A date stored as text, for example, may not group or filter like a real date. In Power Pivot’s model window, you can review the tables in Data View or use Diagram View to see how they relate.

For a relationship, the tables need compatible key columns. If Orders contains a CustomerID for each order and Customers contains one row per CustomerID, create a relationship between those fields. In the model window, use Diagram View and connect the matching columns, or create the relationship through the relationship controls. Confirm that the lookup table has unique key values; duplicate customer IDs on the Customers side can cause an incorrect or impossible one-to-many relationship.

Once the tables are related, create a PivotTable from the Data Model, then place fields from the different tables into its rows, columns, filters, and values areas. A sales total can use an amount field from Orders while the row labels come from Customers. This works because the model relationship tells Excel how the records correspond. If the result is blank or unexpectedly large, check the key values, data types, and relationship direction before changing the report layout.

Refreshes, File Paths, and Data Maintenance

An imported model is not a live view that instantly changes whenever someone edits Access. It contains data retrieved from the source, and you refresh the Excel connection to retrieve updates. Use Data > Refresh All in Excel, or refresh an individual query from its connection options. If the database has moved, Excel may still be looking at its former path; edit the query’s source or reconnect to the file at its new location.

Plan refreshes around how the database is stored and shared. A path on one person’s local computer may not work for another workbook user. A shared network location can be more appropriate if everyone with refresh access can reach it and has permission to read the database. For a file stored in a synced folder, confirm that the required file is available to the person refreshing the workbook. Refreshing data does not grant database permissions or bypass a password.

It is also worth deciding whether the workbook should keep a copy of the imported data or rely on a refreshable connection. A workbook containing model data can be useful for offline analysis, but it includes a snapshot of the source records. Treat that copy according to the same privacy and access rules as the database, especially if it contains customer or employee information.

Common Problems and Practical Checks

No Power Pivot tab: Check the COM Add-ins list and confirm that the Excel edition and license include Power Pivot. It cannot be enabled from Access.
The Access connector is missing or the file will not open: Check that you are using Excel for Windows and that Office and any database drivers are installed correctly. A mismatch between 32-bit and 64-bit Office components can matter in some setups; follow your organization’s Office installation guidance rather than installing random drivers.
Excel cannot find the database: Confirm the file path, that the file is accessible, and that you have permission to read it. If it was moved or renamed, update the query source.
Some rows or fields are absent: Check whether you selected a query that filters records, or selected only some tables or columns in Navigator. Also check the query’s transformations in Power Query.
Relationships produce wrong totals: Look for duplicate values in the lookup table, inconsistent key formats, blank keys, and columns that have different data types. A number stored as text will not always match the same digits stored as a number.

When a connection fails, test with one small table or query and note the exact error message before changing several settings. That makes it easier to separate a file-access issue from a model-design issue. If the database is password-protected, use the legitimate credentials and connection method supplied by its owner or administrator.

Windows, Mac, and Excel for the Web

The instructions above are for Excel on Windows because Power Pivot’s model-management add-in is a Windows Excel feature. Excel for Mac and Excel for the web do not provide the same Power Pivot add-in experience for creating and managing the model. A workbook containing a model may have some viewing or refresh capabilities depending on the app and connection, but do not assume that you can build or edit the model there as you can in supported Windows Excel.

Access itself is also a Windows desktop application. If you use a Mac or need browser-based collaboration, coordinate with someone who can prepare the model in supported Windows Excel, or use a different service designed for your platform. Merely having an .accdb file does not make the Access connector and Power Pivot tools available in every version of Excel.

When to Use an Alternative

Power Pivot is a good fit when you want a reusable Excel model with relationships and PivotTable analysis. It is not the only way to work with Access data. For a one-time extract, Access can export a table or query to Excel through its external-data export options. That is often simpler when the data is modest and you only need a flat worksheet, but an exported file does not automatically keep itself synchronized with later database changes.

Power Query is useful for importing, cleaning, and combining data before analysis; it is part of Excel’s Get Data workflow and can be used without building a sophisticated DAX model. Access queries may be better for selecting and joining records close to the database. Choose based on the task: use Access to maintain and query the source, Power Query to shape incoming data, and Power Pivot when relationships and repeatable analysis across tables are important.

Frequently Asked Questions

Can I install Power Pivot in Microsoft Access?
No. Power Pivot is an Excel add-in. Use Excel to connect to Access data and create the Data Model.

How do I add an Access table to Power Pivot?
In Excel for Windows, choose Data > Get Data > From Database > From Microsoft Access Database, select the table, and load it with Add this data to the Data Model checked.

Why can’t I see Power Pivot in Excel?
The add-in may be disabled, or your Excel edition or license may not include it. Check File > Options > Add-ins and the COM Add-ins list; if it is absent, verify your license with your administrator.

Can Power Pivot refresh Access data automatically?
It can refresh a configured connection when you request a refresh, but it does not update the model instantly as Access records change. The source file must remain reachable and you must have permission to read it.

Does Power Pivot work in Excel for Mac?
The Power Pivot model-management add-in described here is for Excel on Windows. Excel for Mac and the web do not offer the same authoring experience, so use supported Windows Excel to build and manage the model.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.