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

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.


Download Practice Workbook

You can download the practice workbook from here.


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.


2 Quick Tricks to Remove Table from Data Model in Excel

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 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: Remove Data Model from Pivot Table in Excel (with Easy Steps)


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 Create Calculated Field in Pivot Table Data Model


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 Create a Data Model in Excel (3 Handy Ways)


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.

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. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo