Access Power Pivot Gallery in Excel or SharePoint

If you mean Power Pivot in Excel, open Excel for Microsoft 365 on Windows and select the Power Pivot tab, then choose Manage; if the tab is missing, enable the Power Pivot COM add-in in Excel Options. If you mean a Power Pivot Gallery, that is a SharePoint library in certain on-premises deployments—not a feature inside Microsoft Access or a public online gallery.

What Power Pivot Gallery means

Three related names can cause confusion. Power Pivot is Excel’s data-modeling technology: it lets you bring together tables, define relationships, and analyze larger or more structured sets of data. The Power Pivot window is the Excel interface for inspecting and working with that model. A Power Pivot Gallery, by contrast, is a specialized SharePoint document library used in some supported on-premises SharePoint environments to publish Excel workbooks that contain Power Pivot models.

None of these is a gallery inside the Access desktop application. Access is a database program and can provide data to Excel, but it does not include a Power Pivot tab or a Power Pivot Gallery command. If your goal is to analyze Access tables with Power Pivot, use Excel as the modeling and analysis tool and connect it to the Access database.

The word gallery also does not mean a general Microsoft catalog where anyone can browse downloadable Power Pivot models. A SharePoint gallery is a library within an organization’s site. Whether it exists, and whether you can open it, depends on how that SharePoint environment has been installed and configured.

Open Power Pivot in Excel for Microsoft 365

On Excel for Microsoft 365 for Windows, first look for the Power Pivot tab on the ribbon. If it is there, select Power Pivot > Manage to open the Power Pivot window. That window shows the tables in the current workbook’s data model and provides tools for relationships, calculations, and data import.

If the tab is absent, try enabling the add-in:

Select File > Options.
Choose Add-ins.
At the bottom, set Manage to COM Add-ins, then select Go.
Select Microsoft Power Pivot for Excel and choose OK.
Return to the workbook and check the ribbon for the Power Pivot tab.

The add-in must be available in your installed Excel edition and permitted by your organization. If it is not listed, or the checkbox is unavailable, check your Office installation and license or ask your Microsoft 365 administrator. A missing tab is not evidence that the workbook has no data model; it can simply mean the add-in is disabled or unavailable in that Excel setup.

These instructions are for the Windows desktop application. Excel for the web does not provide the same Power Pivot authoring window, and Excel for Mac does not offer the Windows Power Pivot COM add-in. You may be able to view or work with a workbook in other ways, but use supported Windows desktop Excel when you need to create or manage a Power Pivot model.

Use an Access database as a Power Pivot source

To analyze Access data, open the workbook in desktop Excel and connect to the database. In Excel for Microsoft 365 on Windows, use Data > Get Data > From Database > From Microsoft Access Database. Browse to the .accdb or supported .mdb file, select it, and choose the tables or queries you want in the Navigator. The exact items available depend on the database and your access permissions.

To put the selected data in the workbook’s model rather than only on a worksheet, use the load options. In the import or query workflow, choose Load To, then select Only Create Connection and Add this data to the Data Model. If you load a table to a worksheet first, you can also add it to the data model using the relevant table or Power Pivot command. Once imported, open Power Pivot > Manage to inspect the model.

For example, an Access database might have a Customers table and an Orders table. Import both, then create a relationship using a shared customer key. You can use fields from both tables in a PivotTable without copying the customer name into every order record. Check that the key columns have compatible data types and that the customer-side key is unique; a relationship cannot correct inconsistent or duplicate source data.

Importing data creates a model in the Excel workbook; it does not turn Access into Power Pivot or necessarily keep the workbook synchronized automatically. When the source changes, refresh the workbook’s connection or model. A refresh can fail if the database file has moved, the user no longer has permission, or the connection depends on a path that is not available to the person refreshing it.

Find a Power Pivot Gallery in SharePoint

If your organization uses a compatible on-premises SharePoint Server deployment with the required Power Pivot components configured, the gallery is a library on a SharePoint site. Ask the site owner for the site address and the library name, or look under the site’s contents for a library labeled Power Pivot Gallery. The route and labels differ among SharePoint versions and site designs.

Creating a gallery is not usually something an ordinary site visitor can do from Excel. It depends on the SharePoint farm’s installed components, service configuration, and available library templates. In some classic SharePoint environments, a site owner may be able to add the gallery from the site’s app or library options, but an administrator may need to deploy or configure the underlying services first. If you cannot find the option, ask the SharePoint administrator rather than assuming a hidden Excel setting will enable it.

SharePoint Online is a different service from an organization’s on-premises SharePoint Server farm. Do not assume that instructions for an older Power Pivot Gallery library apply to a modern SharePoint Online site. For online reporting and shared analytics, organizations may instead use supported Excel workbook sharing or Power BI, depending on their Microsoft 365 setup and requirements.

What the SharePoint gallery does

A Power Pivot Gallery stores and organizes workbooks; it is not a separate database engine that replaces the workbook’s model. In a correctly configured environment, the library can provide a more useful presentation of published workbooks than an ordinary file listing. Depending on the SharePoint and Excel Services configuration, users may be able to preview a workbook or interact with its reports in a browser.

The experience depends on the services available to that SharePoint installation, the workbook’s features, and the user’s permissions. A gallery listing does not guarantee that every workbook can refresh or display interactively. For example, a workbook may open but fail to refresh because its original data source is unavailable to the server. Publishing also does not automatically grant access to the underlying database or remove the need to manage sensitive information carefully.

Think of the gallery as a publishing location for supported Excel workbooks, not as the place where you build the model. Model authors generally create and maintain the data model in desktop Excel, save the workbook, and publish it to the library if the organization’s environment supports that workflow.

Why the tab or gallery may be missing

First identify which item you are looking for. A missing Power Pivot tab is an Excel add-in or licensing issue; a missing gallery is a SharePoint site or server configuration issue. Looking in Access for either one will not resolve it.

Power Pivot tab missing: Check File > Options > Add-ins and the COM Add-ins list. Confirm that you are using desktop Excel for Windows and that your edition provides the add-in.
Add-in unavailable: Your Office license, installation, or organization policy may not include or permit it. Contact your administrator if the control is missing or disabled.
Gallery missing from a site: The site may not have the library, the SharePoint deployment may not support the legacy feature, or your account may lack permission to see it.
Workbook opens but will not refresh: Check the source file path, database permissions, and any server-side data-source configuration. Opening a published workbook and refreshing its data are separate operations.

When asking for help, include the application and location: for example, Excel for Microsoft 365 on Windows, a particular SharePoint site, or Access. That detail helps an administrator distinguish a desktop add-in problem from a SharePoint publishing problem.

Power Pivot, Access, and Power BI compared

Access is suited to building and maintaining relational databases, forms, and queries. Power Pivot is suited to modeling and analyzing data inside Excel. Power BI is a separate reporting and analytics platform with its own desktop authoring and online sharing services. They can work together, but they are not interchangeable names for one feature.

A practical choice depends on the task:

Use Access when you need an Access database workflow, such as maintaining records through forms and queries.
Use Excel Power Pivot when you want a reusable data model and PivotTable-based analysis in an Excel workbook.
Use a SharePoint Power Pivot Gallery only when your organization has the compatible on-premises SharePoint environment and you need to publish supported workbooks there.
Consider Power BI when the goal is to build and share interactive reports through the Power BI service, subject to your organization’s licensing and governance.

For a small analysis, importing a few Access tables into Excel may be enough. For a team that needs controlled, repeatable report sharing, ask which platform the organization supports before investing time in a legacy SharePoint gallery workflow.

Frequently Asked Questions

Can I open Power Pivot from Microsoft Access?
No. Access does not have the Excel Power Pivot tab or a Power Pivot Gallery. Open the Access data in Excel and use Power Pivot there if the installed Windows Excel edition supports it.

Where is the Power Pivot tab in Excel?
In Excel for Microsoft 365 on Windows, it appears on the ribbon when the Power Pivot COM add-in is available and enabled. If it is missing, check File > Options > Add-ins, then manage COM Add-ins.

Is Power Pivot Gallery available in SharePoint Online?
The classic Power Pivot Gallery is associated with supported on-premises SharePoint deployments, not a general SharePoint Online gallery feature. Check with your administrator for the reporting and workbook-sharing options supported by your organization.

Can Excel Power Pivot use an Access database?
Yes. In desktop Excel for Windows, connect through Data > Get Data > From Database > From Microsoft Access Database, then load the chosen data into the workbook’s Data Model.

Why can I see a workbook but not refresh it?
Viewing a workbook does not ensure that its data source is reachable for refresh. The source path, server configuration, credentials, and your permissions all affect whether a refresh can succeed.

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.