How to Remove Table from Data Model in Excel (2 Quick Tricks)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I will show you 2 quick tricks to remove a table from the data model in Excel. These methods are simple and do not require the use of any complex formulas. Also, in the last section, I will walk you through some simple steps to add a table to a data model in case you don’t have one.


What Is a Data Model?

A data model in Excel is a collection of data tables where the tables have relationships between them through one or more data series. This collection of data tables forms a larger database that can access the individual tables from different worksheets. To establish a relationship, the tables inside the data model need to have a common column. This type of relationship can help to create a Pivot Table more easily and generate summarized reports from there.


How to Remove Table from Data Model in Excel: 2 Quick Tricks

We have taken 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


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

The Queries & Connections feature in Excel gives you all the information and relevant commands that you need to work with external data and data models. This can help us to remove a table from an existing data model. Let us see how to do that.

Steps:

  • First, go to the Data tab and from there select Queries & Connections.

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

  • Now, the Queries & Connections pane will appear on the right-hand side of your window.
  • Here, you should see the existing connections. In my case, there are 2 connections.
  • Next, select the connection that has the name of the current workbook included.

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

  • Now, right-click on the selected connection name and click on Delete.
  • As a result, Excel will remove the selected table from the data model.

Read More: How to Use Data Model in Excel


2. Deleting Table from Power Pivot Window

We can remove a table from a data model directly from the Power Pivot tool in Excel. But if you have many tables to delete, then this method might take some time as this is a manual process. So if you want to use this method, follow the steps below.

Steps:

  • To begin with, navigate to the Power Pivot tab and select Manage.

excel remove table from data model

  • Immediately, the Power Pivot window will open, and here you will see the table that is inside the data model.

excel remove table from data model

  • Now, at the bottom of the Power Pivot window, you should see a tab with Table1 or any similar name.

excel remove table from data model

  • Then, right-click on this tab and select Delete.

  • Finally, you should see that Excel has removed the table from the Power Pivot data model and it is now empty.

Read More: How to Update Data Model in Excel


How to Add Table to a Data Model in Excel

In the previous two methods, I assumed that you already have a data model inside the Power Pivot tool. But, if do not have a data model, then you can follow the steps below to quickly add a database table to a Power Pivot data model.

Steps:

  • To start with, go to the worksheet that has the table to add to the data model.
  • Now, once you are at the desired worksheet, go to the Power Pivot tab and click on Add to Data Model.

How to Add Table to a Data Model in Excel

  • Next, in the Create Table window, enter the cell range where your table exists.
  • Then, check the My table has headers option and click OK.
  • Note that, in some cases, you might not see the Create Table Rather clicking on the Add to Data Model will directly take you to Power Pivot.

How to Add Table to a Data Model in Excel

  • Consequently, the Power Pivot tool will add the table to a new Data Model and you can follow the same process to add more tables to the data model and establish relationships between them.

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, all the relations that it had with other tables, will not exist anymore.
  • But you can add the removed table back into the data model if needed.

Download Practice Workbook

You can download the practice workbook from here.


Conclusion

I really hope that you were able to apply the above methods on how to remove a table from the data model in Excel. You can use these methods no matter how many tables you have inside your data model. I would suggest choosing the method that best suits your situation. Also, download the workbook that I have provided and practice with it. If you have any queries, please let me know in the comments.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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