Excel Data Model: Can You Turn It Off?

How-to basics · Updated November 18, 2026

Excel has no single switch that turns off a workbook’s Data Model. To stop a Power Query query from adding data to it, go to Data > Queries & Connections, right-click the query, choose Load To…, select Only Create Connection, and clear Add this data to the Data Model. If the model already exists, those steps do not automatically erase every table or dependency in it.

What “turn off the Data Model” can mean

The phrase can describe several different goals, and the right fix depends on which one you have. You might want to stop future imports from entering the model, remove model data already stored in a workbook, hide the Power Pivot tools, or simply make a PivotTable that does not use the model. These are separate actions: hiding the tools does not remove the model, and changing one query’s load setting does not necessarily remove other model tables.

Excel’s Data Model is an internal collection of related tables in a workbook. It can support relationships, measures, calculated columns, and PivotTables that combine fields from different tables. It is not the same thing as a worksheet table, a Power Query query, or a normal PivotTable. A query can exist as a connection without loading its results to a worksheet or to the model.

  • Stop future model loading: change the load destination for the relevant query.
  • Remove stored model content: remove model tables and check for dependent reports and calculations.
  • Hide Power Pivot commands: turn off the Power Pivot add-in, if it is enabled.
  • Avoid the model for a new report: create a PivotTable from a worksheet range or table instead.

How to stop a query from loading into the model

In Excel for Microsoft 365 on Windows, open the workbook and select Data > Queries & Connections. In the pane, right-click the query you want to change and select Load To…. In the Import Data dialog, choose Only Create Connection and make sure Add this data to the Data Model is not selected. Confirm with OK.

This keeps the query definition in the workbook, so it can still be refreshed or used as a source for another query, but it does not load the query’s result into the model. If you still need the query results visible on a sheet, choose a worksheet destination instead and clear the model checkbox. Review the choices in the dialog carefully: selecting connection only changes whether the result is loaded to a worksheet, while the separate checkbox controls loading to the Data Model.

You can also use the query editor’s Home > Close & Load > Close & Load To… option when setting a query’s destination. The exact options can depend on how the query was created and its current load state. If Load To… is unavailable or the dialog does not show the expected checkbox, check whether you selected the query itself in the Queries & Connections pane and whether the workbook is open in desktop Excel.

Removing data that is already in the model

Changing a query’s load destination is not the same as clearing every existing item from a workbook’s model. First identify what is in the model and what depends on it. On Windows, use Power Pivot > Manage to open the Power Pivot window, if the Power Pivot tab is available. The window shows model tables; inspect their names and relationships before deleting anything. A table may have been added from a query, an Excel table, or another source.

To remove a table, select it in the Power Pivot window and use the table deletion command there. Deleting a table can also affect relationships, measures, calculated columns, and PivotTables that use its fields. Excel may warn that related objects will be affected, but a workbook can still contain reports that no longer work as intended. Save a separate copy before making broad changes, then check each relevant PivotTable and formula afterward.

There is no general “remove the whole Data Model” button that safely resolves every dependency in one action. If the goal is a clean workbook without model features, removing the model’s tables and rebuilding affected reports may be necessary. For a workbook with many model objects, first make an inventory of table names, relationships, measures, and PivotTables rather than deleting tables at random.

What happens to PivotTables and reports

A PivotTable built from the Data Model can use fields from multiple related tables and model measures. A regular PivotTable built from a worksheet range or Excel table has a different source and does not automatically inherit those relationships or measures. If you remove a model table or stop loading it, a report that depends on its fields may lose those fields or fail to refresh.

For example, imagine a model with an Orders table and a Products table linked by a product ID. A PivotTable might show sales by product category even though the category is stored only in Products. Removing Products or its relationship can break that view. A normal PivotTable from the Orders worksheet table cannot simply reproduce the category breakdown unless the category information is also available in its source data.

Before changing a model, check:

  • Which PivotTables use the Data Model as their source.
  • Whether measures or calculated columns refer to the table you plan to remove.
  • Whether Power Query queries reference one another or load to multiple destinations.
  • Whether colleagues or downstream processes rely on the workbook’s current reports.

Turning off the Power Pivot add-in is different

If your aim is only to hide Power Pivot commands, you can disable its add-in in Excel for Windows. Go to File > Options > Add-ins. At the bottom, set Manage to COM Add-ins, select Go, clear Microsoft Power Pivot for Excel, and choose OK. If that entry is not listed, the add-in may not be available or enabled in your Excel installation.

This changes the available interface; it does not delete tables from the workbook’s Data Model, remove model-based PivotTables, or convert the file into a workbook without a model. Re-enabling the add-in can make the Power Pivot tools available again. Therefore, this is appropriate when you want to hide or stop using the modeling interface, not when your goal is to reduce workbook contents or make a report independent of the model.

Why people choose to stop using a model

Some workbooks do not need relationships or model measures. If all the data for a report is already in one well-structured worksheet table, a regular PivotTable may be simpler to maintain and easier for users to understand. Keeping a query as a connection can also avoid placing a second copy of its results on a worksheet or in the model, depending on how the workbook is designed.

However, the model can be useful when you need to analyze several related tables without merging them into one large worksheet, or when a PivotTable needs measures defined in the model. Removing it just to make a workbook seem simpler can mean rebuilding those relationships and calculations elsewhere. Decide based on what the workbook’s reports require, rather than treating the model as an optional display setting.

Workbook size and compatibility considerations

A Data Model can contribute to workbook size because its data is stored with the workbook. Removing unnecessary model tables may reduce the amount of stored data, but the size change is not guaranteed to match the table’s apparent worksheet size: model storage and compression differ from worksheet cells. Queries, worksheet copies, PivotTable caches, images, and other workbook content can also affect the final file size.

Excel’s Power Pivot and Data Model authoring tools are primarily a Windows desktop feature. Excel for Mac does not provide the same Power Pivot model-management interface, and Excel for the web does not offer the same model-authoring controls as desktop Excel for Windows. If you need to inspect or remove model objects, use a supported Windows desktop version of Excel. Opening a workbook on another platform is not a reliable way to remove its model.

Common problems and safe checks

If the model checkbox seems to return or the query still appears in the model, confirm that you changed the load setting for the correct query. A workbook can contain several queries, including staging queries that feed other queries. Also distinguish between a query’s presence in the Queries & Connections pane and its presence as a model table; those are not the same thing.

If a PivotTable stops working after a change, restore the saved copy or revise the report’s source and fields. Do not assume that deleting a query removes every model object it previously created, or that disabling Power Pivot removes the underlying data. Before sharing a modified file, refresh the reports that matter, inspect their field lists, and verify that the workbook opens without missing-source or refresh errors.

Frequently Asked Questions

Can I turn off the Data Model for just one workbook?
There is no simple workbook-level on/off switch. You can prevent particular queries from loading to the model and remove existing model tables, but you need to account for reports and calculations that depend on them.

Will disabling Power Pivot delete my Data Model?
No. Disabling the add-in hides or disables its tools in the Excel interface. It does not remove the model’s tables or convert model-based PivotTables into regular PivotTables.

Can I use Power Query without the Data Model?
Yes. A query can be loaded to a worksheet, or kept as a connection, without selecting Add this data to the Data Model. Choose the load destination that matches how you plan to use its results.

Can I remove the Data Model in Excel for Mac?
Excel for Mac does not provide the same Power Pivot model-management tools as Excel for Windows. For model inspection and removal, open the workbook in desktop Excel for Windows and check dependencies before changing it.

Does removing a model table reduce file size?
It can, but the amount varies with compression and with other workbook content. Check the saved file size after removing unneeded data, and make sure no required PivotTables or calculations were lost.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.