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.


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.

Quick Tricks to Remove Table from Data Model in Excel


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

Steps:

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

Remove Table from Data Model Using Queries & Connections Option in Excel

  • 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.

Remove Table from Data Model Using Queries & Connections Option in Excel

  • 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.

excel remove table from data model

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

excel remove table from data model

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

excel remove table from data model

  • 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.

How to Add Table to a Data Model in Excel

  • 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.

How to Add Table to a Data Model in Excel

  • 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


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

Get FREE Advanced Excel Exercises with Solutions!
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo