To add data to the Excel Data Model, select a cell in your source range, choose Insert > PivotTable, and select Add this data to the Data Model in the PivotTable creation dialog before choosing where to place the report. Use this option when you need one PivotTable to work with multiple related tables, distinct counts, or measures; a standard PivotTable is simpler for a single flat table.
What the Data Model Does
The Data Model is Excel’s built-in way to store and connect multiple tables for analysis. Instead of joining every column into one large worksheet, you can keep information in separate tables—such as Orders, Products, and Customers—and define relationships between them. A PivotTable can then draw fields from those connected tables.
For example, an Orders table might contain an order date, product ID, quantity, and sales amount. A Products table could hold the product ID, product name, and category. Relating the tables by product ID lets you summarize sales by category without repeating the category name on every order row. The model uses Power Pivot technology and can also support DAX calculations, including measures designed for analysis.
Adding a source to the Data Model does not automatically create a useful report or fix inconsistent data. You still need suitable columns, valid relationships, and a PivotTable layout that answers a question.
When to Add a PivotTable to the Data Model
Choose the Data Model when the analysis needs more than a straightforward summary of one table. It is particularly useful when a workbook has related data at different levels of detail. A sales table may have many transaction rows, while a store table has one row per store. Keeping those tables separate avoids duplicating store attributes across every sale.
- You need fields from multiple tables. Relate tables using a shared key, then use their fields together in the PivotTable.
- You need a distinct count. The Data Model enables the Distinct Count summary option in supported PivotTable setups, useful for questions such as how many unique customers placed orders.
- You need reusable calculations. Measures can calculate results over the model’s data and be reused in reports, rather than relying only on a PivotTable’s basic Sum or Count summaries.
- Your data exceeds a worksheet’s row limit. A worksheet can contain at most 1,048,576 rows. The Data Model can hold more than that, although practical capacity depends on available memory, data shape, and the Excel environment.
For a tidy table of a few thousand rows with no related tables, a regular PivotTable is often easier to understand and maintain. The Data Model adds capabilities, but it also adds decisions about relationships, refreshes, and model design.
How to Create a PivotTable in the Data Model
These steps apply to Excel for Microsoft 365 on Windows. Start with a clean source range: one header row, a single header for every column, and no merged cells or completely blank rows within the data. Converting the range to an Excel table with Insert > Table can make source expansion and refreshes more dependable.
- Select a cell in the source range or Excel table.
- Choose Insert > PivotTable.
- In the creation dialog, confirm the source and select Add this data to the Data Model.
- Choose whether the PivotTable goes on a new worksheet or an existing worksheet, then select OK.
- In the PivotTable Fields pane, drag fields into Rows, Columns, Values, and Filters to build the report.
If the workbook already has a Data Model and you want to build another report from it, use Insert > PivotTable and select the option to use this workbook’s Data Model, when offered. The field list should show the model’s tables. If you only see one source table’s fields, check that the desired tables were actually loaded into the model.
To add an existing Excel table to the model, select a cell in that table and use Power Pivot > Add to Data Model if the Power Pivot tab is available. You can also load data through Power Query and choose to load it to the Data Model in the Load To options. The exact import choices depend on the source and connection.
Set Up Relationships Between Tables
Loading two tables into the model is not the same as connecting them. A relationship tells Excel how a value in one table corresponds to a value in another. For example, a product ID in Orders can connect to the product ID in Products. Without a relationship, a PivotTable using fields from both tables may show unexpected totals or fail to summarize as intended.
In Windows Excel, use Power Pivot > Manage to open the model. In Diagram View, you can inspect tables and create a relationship by dragging a key column from one table to its matching column in another. You can also use the relationship controls in the Power Pivot window. In a conventional one-to-many relationship, the lookup table’s key must identify one row per item; the transaction table can contain that key many times.
Before creating a relationship, check that both key columns represent the same kind of identifier and have compatible data types. Remove duplicate keys from the lookup side, and look for blank or mismatched values. A product code stored as text in one table and as a number in another may not match reliably. Relationships also do not make differently named but similar-looking columns equivalent; you must connect the intended fields.
Measures, Distinct Counts, and the Field List
A model-based PivotTable can use regular fields as row labels, column labels, filters, or values. It can also use measures: named calculations evaluated in the context of the report. For example, a measure can define total revenue from order-line values, while a different measure calculates average revenue per order. A measure is useful when the same business calculation will appear in several PivotTables or needs behavior beyond a basic sum.
Measures are written with DAX, Excel’s data-analysis expression language, rather than as ordinary worksheet formulas. In the Windows Power Pivot window, measures can be created from the measure commands, with the exact ribbon placement depending on the window layout. If you only need to total a numeric column, dragging it into Values and choosing Sum may be enough; you do not need to create a measure just because the data is in the model.
For a unique-customer count, use a model-based PivotTable and select Distinct Count for the relevant field’s value summary, if that option is available. A regular PivotTable may offer Count, which counts nonblank records rather than unique customers. That difference matters: if the same customer appears on five orders, Count can include that customer five times while Distinct Count includes the customer once.
Data Model PivotTable vs. a Standard PivotTable
A standard PivotTable is generally based on one range or table and is a good choice for routine summaries, such as total units by month from a single clean source. The Data Model is more appropriate when separate tables need to work together, when distinct counts are required, or when reusable measures are valuable.
There are trade-offs. Model-based workbooks can be more complex to maintain, and a user may need to understand table relationships and refresh behavior. The model is compressed, but that does not mean it is unlimited or always faster: memory, high-cardinality columns, and the size and structure of the data all affect performance. A flat source may be easier to troubleshoot if the report has no real need for multiple tables.
One common misconception is that checking Add this data to the Data Model automatically converts every existing PivotTable in the workbook. It does not. If an existing PivotTable was created from a normal range or table, create a new model-based PivotTable and arrange its fields there. You may need to reproduce the old layout and formatting.
Common Problems and How to Fix Them
- The Data Model checkbox is missing. Confirm that you are using desktop Excel for Windows and that your Excel edition and installation include the relevant Data Model features. The Power Pivot tab may need to be enabled through File > Options > Add-ins; under Manage, select COM Add-ins and choose Go, then check Microsoft Office Power Pivot for Excel if it is listed. Availability varies by Excel edition.
- Fields from a second table do not work as expected. Verify that both tables are in the model and that a relationship connects the intended key columns. Check for duplicates on the lookup side and incompatible data types.
- Totals appear multiplied or incorrect. Review the relationship and the level of detail in each table. Combining fields from tables without a suitable relationship can produce misleading results. Confirm that the value being summarized is at the intended grain, such as order line rather than order header.
- New rows are missing. Refresh the model and PivotTable. If the source is a plain range, new rows may fall outside the original source; using an Excel table usually helps it expand as rows are added. Also verify that the query or connection includes the new records.
- The workbook becomes large or slow. Remove columns that are not needed, avoid importing unnecessary detail, and consider whether a high-cardinality text column belongs in the model. Large models can consume substantial memory even when their data is compressed.
Refresh, Save, and Platform Notes
After source data changes, refresh the relevant connection or model and then refresh the PivotTable if necessary. Use Data > Refresh All to update workbook connections and reports together, or right-click a PivotTable and choose Refresh. Refreshing can take time when a query reads from a database or a large file. Check that credentials and source locations remain available when sharing the workbook.
Excel for Microsoft 365 on Windows provides the full Power Pivot authoring workflow described here, subject to the Excel edition and enabled features. Excel for Mac does not offer the same Power Pivot and Data Model authoring experience, and Excel for the web has limitations for creating or managing these models. A workbook created on Windows may open on another platform, but do not assume all model-building or editing features will be available there. For cross-platform work, confirm the recipient’s Excel version and test the actual workbook workflow.
Frequently Asked Questions
Can I add an existing PivotTable to the Data Model?
A standard PivotTable generally cannot be switched to a model-based PivotTable in place. Add the source table to the model, create a new PivotTable from the model, and rebuild the layout. Keep the original until the replacement has been checked.
Do I need the Data Model for multiple worksheets?
No. Multiple worksheets alone do not require a Data Model. It is useful when you need to analyze related tables together; if the data is already in one clean table, a standard PivotTable may be sufficient.
Does adding data to the model make the workbook unlimited in size?
No. It can analyze data beyond a worksheet’s row limit, but the model is still constrained by memory and practical performance. There is no single useful capacity guarantee for every workbook because data types, compression, and the computer all matter.
Why is Distinct Count unavailable?
Distinct Count is associated with a PivotTable built using the Data Model. If the option is absent, check whether the PivotTable was created from the model rather than directly from a worksheet range, and confirm that your Excel version supports the feature.
Can Excel for Mac create a Data Model PivotTable?
Mac Excel does not provide the same full Power Pivot workflow as Excel for Windows. For creating and managing a workbook’s Data Model, use a supported Windows desktop version and verify feature availability in the Excel edition being used.
Related
- Excel Data Validation Rules: How They Work and When to Use Them
- Excel Data Validation Restrictions and Their Limits
- Data Validation Process: Steps, Checks, and Best Practices
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Slicer Link to Different Pivot Tables: How It WorksYes. In Excel for Microsoft 365 on Windows, one slicer can filter multiple PivotTables when those PivotTables share…
- Move a Pivot Chart to Another Sheet in ExcelIn Excel for Microsoft 365 on Windows, select the PivotChart, choose Chart Design > Move Chart, select Object…
- Google Sheets Chart Editor: Create and Customize ChartsThe Google Sheets Chart editor is the sidebar for creating and changing charts in a spreadsheet. Select the…
- Pivot Chart: Remove Filter Labels in ExcelTo remove the filter labels from an Excel PivotChart, select the chart and choose PivotChart Analyze > Show/Hide…
- Automated Gantt Chart in Excel: Setup and LimitsYes. In Microsoft Excel for Microsoft 365 on Windows, you can make a Gantt chart update automatically by…
- Excel Dashboard AI Free? What You Can Use at No CostYes, you can use AI at no cost to help plan an Excel dashboard, suggest formulas, or explain…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.