How to Create Table from Data Model in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. There are many default Excel functions that we can use to create formulas. Many educational institutions and business companies use Excel files to store valuable data. Sometimes, we need to create Data Model in Excel. In Data Model, multiple data tables stay connected through common data series. However, it’s also equally important to create a table from the data model whenever it’s necessary. This article will show you the step-by-step procedures to Create a Table from Data Model in Excel.


How to Create Table from Data Model in Excel: With Easy Steps

MS Excel is a very useful program. You can easily create tables using datasets. Sometimes, it’s necessary to access one table from another table through a common data series. In that case, we have to form a relationship between those tables. Data Model is a system where we link up multiple Excel tables. In this article, we’ll show you the complete procedures to form a data model. Steps to creating a table from that data model will follow afterward. To illustrate, we’ll use the following dataset. For instance, we have 3 datasets and their common column is Salesman ID.


STEP 1: Insert Table

  • First, we need to create tables using our datasets.
  • For this purpose, select the range B4:C7.
  • Then, go to Insert ➤ Tables ➤ Table.

  • As a result, a dialog box will pop out.
  • Press OK there.

  • Thus, you’ll get the desired table.
  • Now, rename the table in the Table Design tab.
  • In this example, we type ‘Name’ in the Table Name.

  • Similarly, follow the above steps to create 2 other tables.
  • Give the table names as Product and Sales.
  • See the below picture where all the 3 tables are demonstrated.

Read More: How to Manage Relationships in Excel


STEP 2: Create Data Model

create table from data model excel

  • Consequently, a dialog box will appear.
  • Here, click New.

create table from data model excel

  • Subsequently, another dialog box will emerge.
  • There, choose the Name table from the Table drop-down.
  • Next, choose the Product table in the Related Table.
  • Then, select Salesman ID in both the Column and Related Column sections.
  • After that, press OK.
  • The following figure will help you understand better.

create table from data model excel

  • Follow the above steps to link the Name table and the Sales table.
  • Thus, it’ll return the following dialog box after building the data model.
  • Press Close.

create table from data model excel

Read More: How to Create Relationship in Excel with Duplicate Values


STEP 3: Create Pivot Table from Data Model

Often, we have to analyze table objects from multiple tables. That’s easier to carry out if we have a data model in excel. In this step, you’ll see how to create a pivot table from a data model. Therefore, follow the process below.

  • First of all, select Insert ➤ Tables ➤ PivotTable ➤ From External Data Source.

create table from data model excel

  • As a result, a dialog box will emerge.
  • Then, click Choose Connection.

create table from data model excel

  • Another dialog box will follow.
  • Subsequently, go to the Tables tab.
  • Press Workbook Data Model.
  • After that, click Open.

create table from data model excel

  • Check the circle for New Worksheet.
  • Press OK.

create table from data model excel

  • Thus, it’ll return a new sheet where PivotTable Fields are present.
  • You can perform numerous operations using the PivotTable tools.

create table from data model excel

  • Check the box for Names and drag it to place in the Filters.
  • Afterward, check the Net Sales box and drag it to place in the Values field.
  • Hence, you’ll see the total Net Sales.
  • In this way, we can create a table and look for any data in the data model.

create table from data model excel

Read More: [Fixed!] Excel Data Model Relationships Not Working


STEP 4: Convert Pivot Table Data into a Typical Table

Lastly, we will create a standard table in Excel using the previously created pivot table data.

Follow these steps:

  • Select the previously created pivot table.
  • Right-click on that to display the context menu.
  • Click on Copy.

Select the pivot table and right-click to display the context menu

  • Now, choose cell A8.
  • Right-click to show the context menu.
  • Click on the Paste as Values from the Paste Option.

Select cell A8, right-click to display the context menu and paste as values

  • Select the range A8:B11 and press Ctrl+T.
  • Check My table has headers.
  • Hit OK.

Select range staring from A8 to B11 and press Ctrl plus T

  • Thus, we have created a table from a data model.

Convert Pivot Table Data into a Typical Table


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to create a table from the data model in Excel following the above-described steps. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Create Relationships in ExcelData 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.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo