Yes. In Excel for Microsoft 365 on Windows, remove tables from the Data Model in Power Pivot: choose Power Pivot > Manage, select a model table, then choose Design > Delete; repeat for every table you want removed and save the workbook. Excel does not offer a single, universal button that safely removes a whole model while preserving every feature that depends on it, so make a backup first.
What the Excel Data Model is
The Data Model is a relational data store embedded in an Excel workbook. It can hold tables from worksheets, databases, text files, and Power Query results, then connect those tables through relationships. A PivotTable can use the model to summarize several related tables without first combining them into one worksheet range.
For example, a model might contain a Sales table, a Products table, and a Calendar table. A relationship between their key columns lets a PivotTable show sales by product category and month. The tables may not all appear as visible worksheet tables: some may exist only in the model. Power Pivot also lets users create measures, calculated columns, hierarchies, and other model objects.
The model is part of the workbook, not a separate worksheet you can delete with a normal sheet command. Deleting a worksheet therefore does not necessarily remove its corresponding model table, and deleting a model table does not necessarily delete the worksheet that supplied its data.
Before removing anything
Save a separate copy of the workbook before changing the model. Removing model objects can disrupt reports, and deleting a table is not the same as clearing its data from a worksheet. A backup lets you restore the original model if you later discover a PivotTable, query, or calculation depended on it.
First work out whether you want to remove one table, stop a query from loading into the model, or eliminate the model altogether. These are different tasks. Check for PivotTables that use the model, relationships, measures, and queries that load data to it. If you are unsure which tables are essential, retain the backup and test changes on a copy.
- Remove one model table: delete that table from Power Pivot, while leaving its source worksheet or file alone.
- Stop a query loading into the model: change that query’s load destination, then check whether its existing model table has been removed.
- Remove the whole model: remove all model tables and review the workbook’s dependent reports and connections. There may be no benefit to doing this if the model is small and causes no problem.
Remove a table in Power Pivot
- Open the workbook in the desktop version of Excel for Windows and select the Power Pivot tab. Choose Manage to open the Power Pivot window.
- In the Power Pivot window, select the tab for the table you want to remove. Take care to select the model table, not a worksheet tab in the main Excel window.
- Choose Design > Delete. If Excel asks you to confirm, review the warning before proceeding. Repeat for each table that should no longer be in the model.
- Close the Power Pivot window and save the workbook. Reopen relevant PivotTables and reports to check what changed.
This removes the selected table from the model; it does not, by itself, mean that the source file or worksheet data has been erased. A worksheet table that supplied the model data can remain available as ordinary Excel data. Conversely, any model relationships or calculations that relied on the deleted table can no longer work as before.
If you do not see the Power Pivot tab, the add-in may be unavailable or disabled in your Excel installation. On Windows, check File > Options > Add-ins. At the bottom, select COM Add-ins in the Manage list and choose Go; if Microsoft Power Pivot for Excel appears, select it and confirm. Power Pivot availability depends on the Excel edition and installation. If it is not listed, you may need a version or license that includes it, or access to Excel on a Windows computer where it is available.
Prevent a Power Query from loading back into the model
Deleting a model table may not be enough if a Power Query is configured to load its results to the Data Model. On refresh, a query with that destination can put data back into the model. Change its load settings when your goal is to keep the query but stop its results being stored there.
- In the Excel window, open Data > Queries & Connections.
- In the Queries & Connections pane, right-click the relevant query and choose Load To….
- In the Import Data dialog, select Only Create Connection if you want to retain the query without loading its result to a worksheet. Clear Add this data to the Data Model.
- Confirm the change. Check the model in Power Pivot and remove any table that remains and that you no longer need.
The exact choices depend on how the query is currently loaded. You can instead load query results to a worksheet table and leave the Data Model option unchecked. That keeps a refreshed worksheet result while avoiding a model table. Changing a query’s destination can affect reports or formulas built around its previous output, so check those dependents before refreshing.
What breaks when you remove model tables
Model-based PivotTables and PivotCharts may lose fields or stop displaying useful results when their source table is removed. A report that combines fields from multiple related tables can be especially affected: removing one table may remove the fields it supplied and make dependent calculations or relationships unusable.
Measures and calculated columns associated with a deleted table are not a substitute for worksheet formulas; they belong to the model and may be lost with the table. Measures stored elsewhere can also refer to the deleted table, so removing a table can affect calculations beyond the table itself. Relationships involving it must also be removed or will no longer be meaningful.
After removal, inspect the workbook rather than assuming it is clean. Check each relevant PivotTable, chart, slicer, and dashboard; refresh queries only after reviewing their destinations; and look for error messages in reports. If users rely on the workbook, tell them that model-based analysis has changed and provide a replacement report or data source where needed.
Removing the entire model versus removing a worksheet
To eliminate the model, remove every table in it and address any query that would load tables back into it. A workbook-level command named Remove Data Model is not the normal Power Pivot workflow. Removing tables one by one gives you a chance to identify dependencies and preserve tables that are still useful.
Do not confuse this with deleting a worksheet. A worksheet may contain source data that you still need, while a model table may be hidden from normal sheet view. Likewise, removing a model table does not automatically remove every connection or query that brought the data into the workbook. Those objects have their own settings and may remain even after the model table is gone.
If your aim is simply to reduce workbook size, identify what is taking up space before deleting useful data. The model can store data efficiently, and a large worksheet or embedded file may be the real cause. Removing the model can also make a workbook less capable without delivering the size reduction you expect. Compare a saved copy before and after the change rather than assuming the model is responsible.
Excel for Mac and Excel for the web
Power Pivot model management is a Windows desktop feature. Excel for Mac does not provide the Power Pivot window and its model-table deletion workflow, and Excel for the web does not provide the same tools for editing a workbook’s Data Model. A workbook containing a model may still be opened in another environment, but that does not mean the model can be fully managed there.
For model removal, use a compatible Windows desktop version of Excel with Power Pivot available. If you only have Mac or web access, ask someone with the appropriate Windows Excel installation to make and verify the change, or rebuild the needed worksheet data and reports in a workbook that does not depend on the model. Keep an untouched copy before transferring or rebuilding anything.
Alternatives to deleting the model
Often, the real goal is not to remove the model but to make the workbook easier to understand or share. Consider a less destructive option first:
- Hide or remove a report: if a PivotTable or chart is the issue, change that report rather than deleting its underlying data model.
- Change a query’s destination: load data to a worksheet or keep only a connection when you no longer want a model table.
- Remove only unused tables: retain the relationships and measures that support active reports, and delete only tables you have confirmed are unnecessary.
- Create a separate reporting workbook: copy the required results as values or rebuild the report from worksheet data. Verify that the new workbook does not still depend on the original model.
Copying a sheet or saving the workbook under another name is not, by itself, a dependable way to strip out the model. Depending on what is copied and how, PivotTables, connections, or other dependencies may carry over or stop working. Inspect the new file in Power Pivot, if available, and test its reports before sharing it.
Frequently Asked Questions
Can I remove the Data Model without deleting worksheet data?
Yes. Deleting a table in Power Pivot removes it from the model, not automatically from its source worksheet. Check the worksheet separately before deleting anything there.
Will my PivotTables still work after I remove the model?
Not necessarily. A PivotTable built from the model depends on its model tables and fields. Removing a table can remove fields, relationships, and calculations that the report needs. Test reports on a copy first.
Why does a deleted table come back after refresh?
A query may still be configured to load its results to the Data Model. Use Data > Queries & Connections, open that query’s Load To settings, and clear Add this data to the Data Model before refreshing.
Can I remove the Data Model in Excel for Mac?
Excel for Mac does not provide the Windows Power Pivot window used to delete model tables. Use a compatible Windows desktop version of Excel to manage the model.
Does deleting a worksheet remove its table from the model?
Not reliably as a general rule. A model table and its worksheet source are distinct workbook objects. Check Power Pivot directly to confirm which tables remain.
Is there a one-click way to delete the whole Data Model?
The usual Power Pivot approach is to remove the model tables and manage queries that load data into it. Save a backup and review dependent reports rather than relying on a single workbook command.
Related
- Excel Data Validation Date Picker: Setup and Options
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Create an Envelope Template in Word: A Practical GuideIn Microsoft Word for Microsoft 365 on Windows, go to Mailings > Envelopes, enter the delivery and return…
- Creating a Flowchart in Word: Steps and Practical TipsTo create a flowchart in Microsoft Word for Microsoft 365 on Windows, choose Insert > Shapes, add shapes…
- Power Query Absolute Value: How to Use Number.AbsIn Power Query, use the M function Number.Abs to return a number’s absolute value: for example, Number.Abs(-25) returns…
- Insert a Yes/No Dropdown in Excel: A Step-by-Step GuideTo insert a Yes/No dropdown in Excel for Microsoft 365 on Windows, select the cell or cells, choose…
- Google Sheets Conditional Formatting: How to Apply RulesYes. Google Sheets includes conditional formatting, which automatically changes a cell’s appearance when its value or a formula…
- Delete an Unwanted Page in Word: Simple FixesTo delete an unwanted page in Word, remove the text or objects on it, or delete the page…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.