A Comprehensive Guide to Creating a Data Model in Excel

Power Pivot is a free add-in for Excel and available in Excel 2010 and later versions.

Data Model using Power Pivot

1. Users click on the Manage button to open the Power Pivot window.
2. You can choose From Other Sources button to import data from external sources.
3. Also, from Excel button, you can import data from an Excel workbook.
4. You will select the data you want to import and click on the Open button.
5. Now click on the Close and Load button at the end.

ables and Relationships

After importing data into the data model, you can start creating tables and relationships.

1. You should start with clicking on the Design tab.
2. Then, you will click on the Create Table button.
3. Here, you enter a name for the table.
4. Now, you will define the columns for the table.

To create a relationship between tables, you can do it like below.

1. After getting to Design tab again, you can now click on the Create Relationship button.
2. So, you will select the primary and foreign keys for the tables.
3. Also the type of relationship.

Creating Hierarchies

Hierarchies can organize data into levels. For example, you can create a hierarchy for a product category with subcategories and products.

This time you will click on the Create Hierarchy button and define the levels for the hierarchy.

Creating Calculated Columns

Calculated columns can create new columns based on existing columns. It means you can create a calculated column. And it can sum up the total sales for a product.

To create a calculated column in Power Pivot, you should find Calculated Column button in Design tab.

Creating Measures

Measures can calculate data based on aggregation. Such as a sum or average. It is possible to create a measure to calculate total sales for a product category.

Adding Data from External Sources

You can add data from external sources to your data model. Such as as SQL Server, Access and SharePoint. For that, you should click on From Other Sources button in Design tab. Then, you select the type of data source and  follow the prompts.

Creating PivotTables and PivotCharts

PivotTables and PivotCharts can  analyze and summarize data. To create a PivotTable or PivotChart, basically you go and click on the Insert tab. Then, you will click on PivotTable or PivotChart button and select your range. As well as pivot table type.

Creating Dashboards

Dashboards in Excel can display key performance indicators (KPIs) and other important data. To create a dashboard, you should first create a PivotTable or PivotChart. And then, you can customize the design of the PivotTable or PivotChart. Also it is possible to add slicers and other interactive elements.

Best Practices for Data Modeling in Excel

When creating a data model in Excel, there are several best practices to follow like we listed below.

Naming Conventions

You should use consistent naming conventions for tables, columns and other elements in the data model.

Documentation of data modeling in excel

You should document the purpose, structure and relationships between tables. This helps others understand data model.

Data Security

You should protect the data model by limiting access to sensitive data. For example, customer information or financial data.