Yes. You can save an Excel workbook that contains a Data Model in a SharePoint document library; the model is stored inside the workbook, not as a separate SharePoint object. SharePoint stores and shares the file, while Excel for Microsoft 365 on Windows is the safest place to create, edit, and refresh the model.
What “Excel Data Model in SharePoint” Means
The phrase can describe two different arrangements. Most often, it means an Excel workbook with a built-in Data Model saved in a SharePoint document library. The workbook may contain imported tables, relationships, measures, and PivotTables that summarize those tables. A second possibility is using SharePoint data, such as a list, as a source for a model in Excel. In either case, SharePoint does not automatically become the model itself.
A Data Model is an embedded data layer inside an Excel workbook. It can connect several tables through shared fields—for example, a Sales table linked to a Products table by ProductID and to a Calendar table by Date. You can then create a PivotTable that uses fields from all three tables. Without a model, combining data from separate sources often requires putting everything into one flat worksheet table or using lookup formulas.
A SharePoint document library is a file location with features such as access controls, version history, and sharing links. It holds the workbook, much as a local folder would, but it does not make the workbook’s model independently available as a SharePoint list or database. If a colleague downloads the workbook, its embedded model travels with the file.
How the Model and Workbook Work Together
When you load data into a Data Model, Excel stores it in the workbook and keeps the model’s structure: tables, column types, relationships, and measures. PivotTables and PivotCharts can use that model to summarize data without requiring every source table to be displayed on a worksheet. This can be useful when a workbook draws on several tables or has more rows than are practical to manage as ordinary worksheet ranges.
For example, imagine a workbook that contains a SharePoint-maintained product list and sales records exported from another system. The model can relate the tables using ProductID. A PivotTable can then show total sales by product category, even though Category is in the product list and the sales amounts are in the sales table. The relationship is part of the Excel model; it does not merge the source files or change the data in SharePoint.
Saving the workbook to SharePoint saves the model along with the rest of the workbook. There is no separate model file to upload. The workbook’s file format matters: use a modern Excel workbook format such as .xlsx rather than the older .xls format, which does not support the modern Data Model in the same way.
Create a Model and Save It to SharePoint
In Excel for Microsoft 365 on Windows, build the model in the desktop application, then save the finished workbook to the appropriate SharePoint library. A typical process is:
- Open Excel and import or connect to the tables you need. Depending on the source, use the relevant commands under Data, such as Get Data, or paste a properly structured table into a worksheet.
- When loading a Power Query result, choose Load To in the load options and select Add this data to the Data Model. For a suitable worksheet table, use the available Data Model command in the Power Pivot or Data tools.
- Load the other tables and create relationships using a matching key, such as ProductID or CustomerID. Check that the key has compatible data types and that the lookup side has unique values.
- Create a PivotTable using the workbook’s Data Model as its source, then check that fields from the related tables work together as expected.
- Save the workbook to the SharePoint document library, or upload it there. Confirm that the saved file opens and that the intended colleagues have permission to access it.
The exact import choices vary with the source and Excel build. If you do not see Power Pivot, check whether the feature is available in your Microsoft 365 edition and enabled. In Windows Excel, the COM add-in can be checked through File > Options > Add-ins; choose COM Add-ins in the Manage box and select Go. If the add-in is not offered, your organization’s license or installation may not include it.
Refresh the Data After Uploading
Saving a workbook to SharePoint does not keep its model synchronized with its sources. The model contains the data from the last successful load or refresh. If the source changes, someone must refresh the relevant connection and save the updated workbook. In desktop Excel, Data > Refresh All is a common way to refresh workbook connections, although available commands and behavior depend on how each connection was created.
Refresh also depends on access to the source. A user may have permission to open the workbook in SharePoint but lack permission to read the database, file, SharePoint list, or other source used by the model. Credentials may need to be supplied or configured, and an organization’s security policies may prevent a connection from refreshing. Test refresh with the account and environment that will actually maintain the workbook rather than assuming that the owner’s access applies to everyone.
Excel for the web has capabilities for working with some workbook connections and Power Query sources, but browser support is not identical to desktop Excel. In particular, do not assume that the browser can perform every Data Model task, manage relationships, or refresh every connection that feeds a model. If reliable model refresh is important, use desktop Excel for the refresh, verify the results, and save the workbook back to SharePoint. Check current Microsoft documentation for the specific source and web features your organization uses.
Sharing, Permissions, and Version History
SharePoint makes it easier to give people access to the same workbook, but a link is not a substitute for permissions. Library or file permissions determine who can view or edit the workbook. Source-system permissions are separate. For example, a colleague might be able to open a model containing previously loaded sales figures but be unable to refresh those figures from the database.
Version history can help recover an earlier saved copy if a workbook is overwritten or a model change causes a problem. It is not a replacement for a deliberate backup or review process. Before changing queries, relationships, or measures in a workbook used for reporting, keep a known-good version and agree who is responsible for refreshing and publishing updates.
Several people may be able to access or coauthor a workbook, but that does not mean simultaneous changes to its model design are a good workflow. Coordinate changes to connections, relationships, and calculations, especially when reports depend on stable field names. A user who changes a column name or removes a table can affect PivotTables used by other people.
Excel for the Web Compared with Desktop Excel
A workbook stored in SharePoint can often be opened in a browser, which is convenient for viewing and many ordinary worksheet tasks. However, the Data Model is a specialized Excel feature. The web app does not provide the same complete model-authoring experience as Excel for Windows, and availability of particular PivotTable or refresh actions can depend on the workbook and Microsoft’s current web support.
Use desktop Excel when you need to create or change model relationships, inspect model tables, configure advanced connections, or troubleshoot a refresh. Use the browser when the task is simply to review workbook contents or make a supported worksheet edit. If a command is missing online, opening the file in the desktop app is generally the practical next step, not evidence that SharePoint has removed the model.
Excel for Mac also differs from Excel for Windows in some Power Pivot and Data Model capabilities. If a team has mixed operating systems, establish a Windows desktop user or process for model maintenance and confirm that Mac users can complete the specific tasks they need. Excel for the web can help with access, but it should not be treated as a full replacement for desktop model tools.
Common Problems and How to Diagnose Them
- The model appears empty or a PivotTable shows old figures: the workbook may not have been refreshed, or the refresh may have failed. Open it in desktop Excel, use Data > Refresh All, review any connection prompts or errors, and save after confirming the new results.
- A relationship will not work: check that the linking columns contain compatible values and data types. The column on the lookup side generally needs unique values; duplicate keys there can prevent the intended one-to-many relationship.
- A colleague cannot refresh: check that the person has permission to the source as well as the SharePoint workbook. Also review credentials, network access, and whether that connection type supports refresh in the user’s Excel environment.
- The browser does not show a model command: open the workbook in desktop Excel. Browser editing is not equivalent to Power Pivot or full Data Model management.
- The workbook is slow or unwieldy: review unnecessary columns, repeated data, and the number of rows being loaded. A model can compress data, but it still uses computer resources and can become large. Keep only fields needed for analysis and consider whether a managed database or reporting platform is more appropriate.
One common misconception is that putting a workbook in SharePoint turns it into a live report. It does not. Unless the workbook is refreshed, its model normally reflects the data available at its last load. Another misconception is that a SharePoint link gives every recipient access to every connected source. Workbook access and source access should be checked separately.
When to Use a SharePoint List or Another Tool
Use an Excel Data Model in a SharePoint workbook when the main goal is analysis in Excel, the data volume and refresh needs are manageable, and the people maintaining the file can use the required desktop features. It is a practical option for a department report, periodic analysis, or a workbook that combines a few related tables for PivotTables.
A SharePoint list may be a better place to maintain structured records that people need to enter and edit collaboratively. A list is a source of records, not a direct replacement for Excel’s analytical model. Excel can connect to list data where the connector and organization’s setup permit it, then load that data into a workbook model. For larger shared datasets, complex access rules, scheduled refresh requirements, or many report consumers, a database, a governed Power BI semantic model, or another centrally managed data service may be more suitable.
Choose based on who owns the source data, how often it changes, who must refresh it, and whether users need to edit records or only analyze them. If the workbook is the only place where important data exists, SharePoint version history helps protect the file but does not provide the same data-management controls as a purpose-built database.
Frequently Asked Questions
Can I store an Excel Data Model in SharePoint?
Yes. Save or upload the Excel workbook containing the model to a SharePoint document library. The model is embedded in the workbook; SharePoint stores and controls access to that file.
Can Excel for the web create or edit a Data Model?
Excel for the web does not offer the full desktop Data Model and Power Pivot authoring experience. Use desktop Excel for model design and advanced maintenance, and check current web support for the specific workbook tasks you want to perform.
Will the Data Model refresh automatically when a SharePoint file changes?
Not simply because the workbook is stored in SharePoint. Refresh depends on the connection, source permissions, credentials, and Excel environment. Set up and test an appropriate refresh process.
Can I use a SharePoint list as a Data Model source?
Often, yes, if the appropriate Excel connector and permissions are available. Load the list data into the workbook’s model, then create relationships to other tables as needed. The list remains a separate source; loading it does not turn it into part of the workbook’s storage.
Why can I open the workbook but not refresh its data?
Opening the workbook only confirms access to that file. Refresh may require separate permission to the source, valid credentials, network access, and a supported connection in the Excel version being used.
Related
- PowerPoint Table Diagonal Split Cell: Practical Methods
- Data Validation and Quality Assurance in Excel
- Power Query Remove Leading Zeros Without Losing IDs
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Compare Two Columns of Names in Excel: Find MatchesTo check whether names in one list appear anywhere in another, put the first list in A2:A6 and…
- Excel Keyboard Shortcut to Hide a ColumnIn Excel for Microsoft 365 on Windows, select one or more column headers and press Ctrl+0 to hide…
- Excel Row Auto Height: How to Fit Text AutomaticallyIn Excel for Microsoft 365 on Windows, select the row or rows, then choose Home > Format >…
- Identify Circular References in Excel: A Step-by-Step GuideTo identify a circular reference in Excel for Microsoft 365 on Windows, open Formulas > Error Checking >…
- Excel Data Analysis: How to Build a Useful ModelYes—Excel can model data for analysis using structured tables, formulas, PivotTables, charts, and, where available, the Data Model.…
- SUMIF Negative Values in Google Sheets: ExamplesIf your values are in A2:A7, enter =SUMIF(A2:A7,"<0") to add only the negative numbers; for example, -12, 8,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.