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.
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.
- 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.
- 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.
- Immediately, the Power Pivot window will open, and here you will see the table that is inside the data model.
- Now, at the bottom of the Power Pivot window, you should see a tab with Table1 or any similar name.
- 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.
- 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.
- 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.
