How to Remove a Table from a Data Model in Excel (2 Methods)

What Is a Data Model?

In Excel, a data model is a collection of data tables. These tables are interconnected through one or more data series, forming a larger database. The data model allows you to access individual tables from different worksheets. To establish relationships, the tables within the data model must share a common column. This type of relationship simplifies creating Pivot Tables and generating summarized reports.

Dataset Overview

We’ll use the following dataset to explain how to remove a table from the data model in Excel. This dataset contains the marks and grades of 6 students in a particular subject. So, there are in total 3 columns and 7 rows including the header. Note that it is important that you have a header inside your table.

Method 1 – Remove Table from Data Model Using Queries & Connections

Steps

• Navigate to the Data tab and select Queries & Connections.

• The Queries & Connections pane will appear on the right-hand side of your window.
• Identify the existing connections. In my case, there are two connections.
• Select the connection that corresponds to the current workbook.

• Right-click on the selected connection name and choose Delete.
• Excel will remove the selected table from the data model.

Read More: How to Use Data Model in Excel

Method 2 – Deleting Table from the Power Pivot Window

Steps

• Go to the Power Pivot tab and select Manage.

• The Power Pivot window will open, displaying the table within the data model.

• At the bottom of the Power Pivot window, locate a tab named Table1 (or a similar name).

• Right-click on this tab and select Delete.

• Excel will remove the table from the Power Pivot data model, leaving it empty.

Read More: How to Update Data Model in Excel

Adding a Table to a Data Model in Excel

If you don’t already have a data model in Power Pivot, follow these steps to quickly add a database table:

Steps

• Go to the worksheet containing the table you want to add to the data model.
• Navigate to the Power Pivot tab and click on Add to Data Model.

• In the Create Table window, specify the cell range where your table exists.
• Check the My table has headers option and click OK.
• Note that in some cases, you might not see the Create Table option. Instead, clicking Add to Data Model will directly take you to Power Pivot.

• The Power Pivot tool will add the table to a new Data Model, allowing you to establish relationships between tables.

Read More: How to Add Table to Data Model in Excel

Things to Remember

• Data models are automatically created when a database is imported into Excel.
• Once you delete a table from a data model, any relations it had with other tables will cease to exist.
• You can re-add the removed table to the data model if necessary.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Get Data from Data Model in Excel
• How to Use Reference of Data Model in Excel Formula
• Excel Data Model vs. Power Query: Main Dissimilarities to Know

<< Go Back to Data Model in Excel | Learn Excel