Power Pivot and SharePoint: What Works Today

Yes—Excel Power Pivot can analyze data stored in SharePoint, including SharePoint lists and files, by importing or connecting to that data in Excel for Microsoft 365 on Windows. Sharing the workbook through SharePoint is a separate matter: SharePoint Online can store and display the file, but it does not provide the legacy on-premises Power Pivot server features or let you build and refresh a Power Pivot model in the browser.

What Power Pivot and SharePoint Each Do

Power Pivot is Excel’s data-modeling capability. It lets you bring together tables, define relationships, create measures with Data Analysis Expressions (DAX), and summarize larger or more varied datasets in PivotTables and PivotCharts. The model is part of an Excel workbook; it is not a database hosted automatically by SharePoint.

SharePoint is a collaboration and content platform. A SharePoint site can hold lists, document libraries, and shared files. Excel can use information from those locations as a source, and a workbook can be saved in a SharePoint document library so colleagues can access it. These two uses—reading SharePoint data and storing a workbook in SharePoint—are often confused with running the model on a SharePoint server.

That distinction matters because a workbook may open from a SharePoint link without its model being refreshed or recalculated against its original sources. Storage, browser viewing, data refresh, and model authoring are different capabilities, with different requirements.

Connect Excel to a SharePoint List

For a SharePoint Online list, use Excel desktop on Windows and its Power Query connector. In Excel, choose Data > Get Data > From Online Services > From SharePoint Online List. Connector labels can vary slightly by Excel build. Enter the site URL—the site root or the relevant site address—not the URL copied from an individual list-view page. Sign in with the organizational account that has permission to read the list.

In the Navigator, select the list and choose Transform Data if you need to clean or filter its columns before loading. In Power Query, you can remove unused fields, set data types, rename columns, and filter rows. When the query is ready, use Close & Load To, select Only Create Connection, and select Add this data to the Data Model. If you load it directly to a worksheet first, you can still add it to the model later, but choosing the destination deliberately avoids creating an unnecessary second copy on a sheet.

For example, a project-tracking list might include project, owner, status, planned date, and cost fields. A model can relate that list to a separate department or calendar table, then report project counts by status or costs by department. Check the imported values before building measures: SharePoint choice, person, lookup, and date columns may appear in a form that needs transformation, and a person field may include more than a simple display name.

Use Files in a SharePoint Document Library

When the source is an Excel or text file in a document library rather than a SharePoint list, the SharePoint Folder connector is often the right route. In Excel, choose Data > Get Data > From File > From SharePoint Folder, enter the SharePoint site URL, and authenticate. Power Query returns files the account can access; filter the results to the intended folder and file type before combining or transforming them.

This is useful when a team drops a regularly updated sales or inventory file into a known library folder. A query can be set up to combine files with a consistent structure. The source files should use matching column names and compatible data types. If someone renames a column or changes the workbook layout, the refresh can fail or produce incomplete results, so document the expected file format and avoid mixing unrelated documents into the input folder.

Do not assume that copying a SharePoint sharing link into a connector is equivalent to entering the site address. Sharing links may contain extra parameters or grant access only through a particular sign-in flow. Use the URL format requested by the connector and confirm that the account used for refresh has permission to the site and the files.

Build the Data Model in Excel

After loading the query into the Data Model, open the Power Pivot window from the Power Pivot tab by choosing Manage. If the tab is missing, check File > Options > Add-ins, select COM Add-ins in the Manage box, choose Go, and see whether Microsoft Office Power Pivot is available to enable. Availability depends on the Excel edition and license. Power Pivot model authoring is a Windows desktop feature; Excel for the web and Excel for Mac do not provide the same Power Pivot authoring tools.

In the model, relate tables using columns that contain matching keys, such as a unique DepartmentID in a department table and the corresponding DepartmentID in a projects table. A good model generally has descriptive lookup tables connected to a fact table, rather than many duplicated descriptions in one flat table. Create measures for calculations that should respond to PivotTable filters, and verify totals against a small, independently checked sample before sharing the report.

Power Query and Power Pivot have related but different jobs. Power Query connects to and shapes source data; the Data Model stores related tables and calculations for analysis. A refresh typically reruns the query and then updates the model. DAX measures are not a substitute for correcting bad source data, and a relationship cannot reliably resolve duplicate or mismatched keys without appropriate modeling.

Save and Share the Workbook in SharePoint Online

Save the finished Excel workbook to a SharePoint document library, or upload it there, and share it with people who have the required permissions. A workbook saved in SharePoint is still an Excel workbook containing its own model. The library controls file access and version history; it does not turn the embedded model into a shared, centrally managed Power Pivot service.

People may be able to open the workbook in Excel for the web and view or interact with supported workbook content, including some PivotTables. However, browser support is not the same as desktop Power Pivot: users cannot use Excel for the web to create or edit the model, and external-data refresh is not universally available for Power Pivot workbooks. Behavior depends on the connections and features in the file. If a report is important, test the exact workbook in the browser with an ordinary reader account, not only with the author’s elevated permissions.

For reliable editing or model refresh, users may need to open the workbook in the Excel desktop app. If the workbook contains sensitive or large data, consider whether all recipients should receive the underlying data embedded in the file. Hiding a worksheet or a PivotTable field is not a security boundary: users with access to the workbook may be able to inspect its contents.

Refresh, Credentials, and Scheduled Updates

Refreshing a model is not automatic just because the workbook is stored in SharePoint. In desktop Excel, refresh behavior is controlled by the workbook’s connections and the user’s permissions. A user can try Data > Refresh All, but a successful refresh requires a reachable source, valid credentials, and compatible connection settings. Organizational sign-in policies, multifactor authentication, privacy settings, and changes to a site URL can all affect the result.

Refresh options can be managed from Data > Queries & Connections by opening a connection’s properties, though the available settings depend on the connection type. A setting to refresh data when opening a file does not guarantee that a SharePoint-hosted workbook will refresh in a browser or on a schedule. Excel desktop normally refreshes using the signed-in user’s context; it is not a general-purpose unattended refresh service.

If a report needs dependable scheduled refresh for a team, Power BI is usually a better fit. Its refresh configuration, supported connectors, credentials, and any required gateway must be planned for the specific data sources and tenant. A SharePoint file’s location alone does not guarantee that Power BI or Excel can authenticate to every source automatically.

SharePoint Server and the Older Power Pivot Gallery

Older guides describe deploying Power Pivot for SharePoint, using Excel Services, and publishing workbooks to a Power Pivot Gallery. Those instructions refer to particular on-premises SharePoint Server and SQL Server configurations. They involve server-side components, version compatibility, service applications, permissions, and administration—not simply saving an Excel file in a SharePoint library.

That legacy architecture should not be confused with SharePoint Online. Administrators cannot install the older Power Pivot for SharePoint server add-in into Microsoft’s SharePoint Online service. If an organization still runs an on-premises deployment, it must check Microsoft’s support documentation for the exact SharePoint Server and SQL Server versions involved; older instructions may describe products or configurations that are no longer supported for a new deployment.

For a current project, avoid designing around a Power Pivot Gallery unless the organization has a supported, maintained on-premises environment and a clear reason to keep it. A workbook library can still be a useful place to distribute files, but it does not recreate the server-side refresh and browser experience of that older architecture.

Common Problems and Practical Checks

When a connection or report fails, identify which stage is failing: connecting to the site, transforming the data, loading the model, refreshing, or opening the workbook in a browser. That simple separation prevents a permissions problem from being mistaken for a Power Pivot formula problem.

Access denied: Confirm that the account used by Excel has read access to the site, list, or library. Check whether the browser and desktop app are signed in with different work accounts.
Cannot find the list or files: Recheck the site URL, the selected connector, and the folder filter. A list connector and a document-library connector are not interchangeable.
Refresh works for one person only: The workbook may depend on that person’s credentials or permissions. Test with the intended audience and agree on an approved authentication and refresh approach.
Browser shows old data: The workbook may have been saved with its last refreshed results. Open it in desktop Excel and check whether Refresh All succeeds before assuming the browser will refresh it.
Model totals look wrong: Inspect duplicate keys, blank values, data types, relationship direction, and filters. Compare a small group of records back to the SharePoint source.
Workbook is slow or large: Load only necessary columns and rows, remove unused queries from the model, and consider whether a centralized reporting platform is more appropriate than distributing copies of a large model.

Power Pivot or Power BI?

Power Pivot is a practical choice when an analyst needs to build an Excel-based model, use familiar PivotTables, and share a workbook with a group that can open it in desktop Excel. It also suits exploratory analysis where the workbook itself is the deliverable and the source data is manageable for the intended workflow.

Power BI is generally better when many people need a centrally published report, browser-first viewing, managed access, or planned refresh. Power BI can connect to SharePoint sources, but setup still depends on the connector, credentials, and refresh requirements. Excel can also connect to or analyze Power BI data in supported scenarios, so the choice does not have to mean abandoning Excel. For simple one-off analysis, a SharePoint list’s built-in views or an Excel table may be sufficient; Power Pivot is not necessary unless relationships or richer model calculations add value.

Frequently Asked Questions

Can Power Pivot connect directly to a SharePoint list?
Yes. In Excel desktop for Windows, use the SharePoint Online List connector through Data > Get Data > From Online Services. Load the query to the Data Model if you want to build relationships and use Power Pivot features.

Can SharePoint Online refresh a Power Pivot workbook automatically?
Do not assume so. SharePoint Online stores and serves the workbook, but it is not a universal scheduled-refresh service for embedded Power Pivot models. Refresh options depend on the connection and authentication setup; use desktop Excel or a planned Power BI refresh solution when appropriate.

Can I create or edit a Power Pivot model in Excel for the web?
No. Excel for the web does not provide the desktop Power Pivot model-authoring tools. It can display and support interaction with some workbook content, but use Excel desktop on Windows to create or edit the model.

Is Power Pivot for SharePoint the same as SharePoint Online?
No. Power Pivot for SharePoint refers to legacy server-side components used with particular on-premises SharePoint and SQL Server environments. SharePoint Online is Microsoft-hosted and does not allow customers to install those server components.

Should I use Power Pivot or Power BI for a shared report?
Choose Power Pivot when the Excel workbook is the intended analysis and desktop Excel suits the audience. Consider Power BI when centralized browser access, controlled sharing, or scheduled refresh is a core requirement. Confirm source connectivity and permissions before choosing either approach.

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.