Creating a data model is not complicated in Excel. It is a "backend database" that integrates multiple tables, supports relational connections, and is used for advanced analytics. To use a data model, first, you need to check the "Add this data to the data model" option when inserting the pivot table. This is the first step in adding data; second, establish a relationship in the correct direction through common fields such as customer ID in Power Pivot or management model; finally, cross-table statistical analysis can be combined with multiple tables, which is often used in sales analysis, costing, performance tracking and other scenarios. It is recommended to enable the Power Pivot plug-in and save it in .xlsx format to retain the model function.

Creating a data model is actually not complicated in Excel, but many people may only focus on basic table sorting and not explore its deeper analytical capabilities. If you want to use Excel to process large amounts of data, do multiple table associations or do dynamic reports, then establishing a reasonable data model is the key first step.

What is a data model in Excel?

To put it bluntly, Excel's data model is a "backend database" that can integrate multiple tables, support relationship connections, and be used for perspective analysis. You can add data tables from different sources, set up their relationships, and then use Pivot tables or Power Pivot for more advanced analysis.

For example, you have two tables: one is the sales order table and the other is the customer information table. As long as they have a common field (such as customer ID), they can be connected in the data model, so that when doing perspective, the fields in the two tables can be used at the same time.

How to add data to model in Excel?

Since Excel 2013, the data model function has been built-in. Adding data to the model is actually very simple, the key is to use the option "Add this data to the data model".

The operation steps are as follows:

• Select your data area or table
• Click PivotTable in the Insert tab
• Check "Add this data to data model" in the pop-up window
• Then click OK and create a preliminary data model

This step is the first step in establishing a multi-table model. If you only have one table, you can add it like this first and then add other tables later.

How to establish relationships between tables in a data model?

When you have multiple tables added to the model, the next step is to establish the relationship between them. For example, sales tables and product tables are connected through "Product ID", and customer tables and order tables are connected through "Customer ID".

The methods for establishing relationships are as follows:

• Open the Pivot Table Fields pane
• Click Manage Models or use the Power Pivot plugin (if enabled)
• Drag fields in relation view to create a connection
• Make sure the relationship is in the right direction (one end and multiple ends)

Note here: Not all fields can establish relationships, they must be matching data types (such as text or integers), and it is recommended to use a unique identification field (such as ID) as the join field.

How to use the data model? What are the common uses?

After building a data model, the most common use is to make pivot tables and perspective views. You can count, summarize, and group across multiple tables without having to merge the data together first.

Common usage scenarios include:

• Sales analysis: Combining multiple tables such as orders, customers, products, and regions
• Cost accounting: Connect cost details, procurement, inventory and other data
• Performance tracking: integrate employee performance, task completion status, and target data to analyze

You can also use Power Query to clean the data and then import the model, so that the entire analysis process will be more efficient.

Tips: Easily overlooked but important details

• Enable Power Pivot plug-in : Although the basic functions of the data model are sufficient, after enabling Power Pivot, you can manage models and write DAX formulas more flexibly.
• The data model cannot be saved directly in normal Excel format : If you use the model function, remember to save it in .xlsx or .xlsm format, otherwise it will prompt that the model data will be lost.
• Model data is not displayed directly in the worksheet : it is hidden in the background and renders the results through a pivot table or chart.

Basically that's it. Using Excel as a data model does not require complex tools. As long as the structure is clear and the relationship is correct, you can exert analytical ability far exceeds that of ordinary tables.

The above is the detailed content of How to create a data model in Excel. For more information, please follow other related articles on the PHP Chinese website!