Power Query Problems

How to Remove Unused Data Model Connections and #REF Tables in Excel

Bushra Parveen Oct 9, 2026 869 views

Question details

The user needs to permanently remove persistent unused tables and #REF entries from Excel Existing Connections or the Data Model.

How to Remove Unused Data Model Connections and #REF Tables in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Cleaning up a workbook by deleting old queries and connections that leave behind hidden metadata in the Data Model.
Observed behavior: Unused tables or #REF entries remain visible in Existing Connections or the Data Model even after the associated queries and connections have been deleted from the standard interface.

Before you start

Verify that no existing PivotTables or PivotCharts are currently relying on the connections you intend to delete, as removing them will break these visual elements.

Solution 1Recommended

Delete Unused Items via Queries & Connections and Manage Data Model

This is the primary method to clear out ghost connections and tables from the Excel interface and the Power Pivot window.

Often, deleting a query from the right-side pane is not enough. You must also dive into the Data Model management window to completely strip the backend metadata associated with the removed tables.

Open Queries & Connections

Navigate to the Data tab on the Excel ribbon and click on 'Queries & Connections' to open the side pane. Right-click and delete any obsolete queries.

Access the Data Model

Still on the Data tab, look for the Data Tools group and click the 'Manage Data Model' icon (the green icon with a database symbol).

Remove the Source Connection

In the Power Pivot for Excel window, switch to the Diagram View or Data View, locate the unused table or #REF connection, right-click its source name, and select 'Delete'.

Delete Unused Items via Queries & Connections and Manage Data Model

Hidden References: If right-clicking the source name does not show an associated worksheet or table, the metadata might be corrupted and require further administrative intervention.

Solution 2

Create a Support Request via Microsoft 365 Admin Center

Use this solution if standard deletion methods fail and the #REF tables persist, indicating deeply embedded workbook metadata corruption.

Free Microsoft Office alternative

Try WPS Office for a Streamlined Spreadsheet Experience

If you frequently encounter complex metadata errors, stubborn #REF tables, or broken Data Model connections in Microsoft Excel, consider switching to WPS Office. It provides a lightweight, highly compatible alternative for managing your daily spreadsheet tasks without the overhead of hidden metadata issues.

1. Download and Install: Get the free WPS Office suite from the official website and install it on your device.
2. Open Your Workbooks: Launch WPS Spreadsheets and open your existing .xlsx files directly without any formatting loss.
3. Manage Data Easily: Use the streamlined data tools in WPS Office to manage connections, tables, and charts efficiently.

Fully compatible with Microsoft Excel (.xlsx) formatsAvoids heavy background metadata overhead for simple tasksLightweight application that runs smoothly on most devicesFree to use with a familiar tabbed interfaceSeamless migration of your existing spreadsheet files

microsoft office alternative - wps office

Frequently Asked Questions

Why do #REF tables appear in my Data Model?

When a source table is deleted or renamed in the main Excel worksheet without first being removed from the Data Model, Excel loses the reference link. This creates a #REF error in the connections list because the backend is still searching for the original table name.

Where is the Manage Data Model button located?

You can find the 'Manage Data Model' button on the Data tab under the Data Tools group. Alternatively, if you have the Power Pivot add-in enabled, you can access it via the Power Pivot tab on the Excel ribbon.

Will deleting a Data Model connection affect my PivotTables?

Yes. Any PivotTables or PivotCharts built using that specific Data Model connection will break, display errors, or lose their data fields entirely if the underlying connection is deleted.

Can I hide unused connections instead of deleting them?

While you cannot technically 'hide' connections from the Manage Data Model window, you can ignore them if they aren't causing performance issues. However, deleting them is best practice to keep file size small and prevent future workbook corruption.

Excel & Data Problems