Yes. In Excel for Microsoft 365 on Windows, you can combine tables in the Data Model by adding them to the model and creating relationships between matching columns; this lets one PivotTable analyze related tables without physically merging their rows. If the tables have the same columns and you want to stack their rows, append them with Power Query instead.
What combining tables in the Data Model means
Excel’s Data Model is a collection of tables and connections stored in a workbook. Rather than putting every field into one large worksheet table, you can keep information in separate tables and tell Excel how they are related. For example, a Sales table can contain transaction dates, product IDs, and amounts, while a Products table contains product IDs, names, and categories.
A relationship connects a column in one table to a corresponding column in another. A PivotTable can then use fields from both tables as if they belong to one reporting structure. The records remain in their respective tables; creating a relationship does not copy product names into every sales row or append the tables together.
This distinction matters because “combine” can describe several different operations. A relationship links tables for analysis, an append stacks records, and a merge joins columns into a new table. Choosing the right operation depends on what the tables contain and what result you need.
When to use relationships instead of merging
Use Data Model relationships when tables describe different but connected things. A common arrangement is a fact table of events or transactions linked to lookup tables that describe customers, products, dates, or locations. The transaction table can have many rows for each product, while the Products table has one row per product ID.
For instance, imagine 40,000 sales records and a product list containing 300 products. A relationship lets a PivotTable group sales by product category without repeating the category in all 40,000 records. You can update the product description in its own table, and the report can use that descriptive field alongside sales amounts.
A relationship is usually the better choice when you want to preserve clear, separate tables, avoid unnecessary repetition, and build reports that draw fields from more than one table. A merge may be appropriate when you specifically need a single flattened output table—for example, to export a combined list to a system that cannot use relationships.
Prepare the tables before adding them
Before creating a relationship, check that each table has a header row, one field per column, and no blank header names. Convert worksheet ranges to Excel tables with Ctrl+T on Windows, confirm that My table has headers is selected, and give each table a meaningful name on the Table Design tab. Names such as Sales, Products, and Customers are easier to manage than generic names such as Table1.
Identify the column that connects each pair of tables. In the example, Sales[ProductID] must contain values that can be found in Products[ProductID]. The lookup side—Products in this case—should have one row per ID. Check for duplicate IDs and inconsistent values such as trailing spaces or different codes for the same product.
- Use matching data types for the two relationship columns. Text IDs should be text in both tables; numeric IDs should be numeric in both.
- Make sure lookup IDs are unique. A product list with multiple rows for the same ProductID cannot serve as the unique side of a standard one-to-many relationship.
- Keep descriptive fields, such as ProductName and Category, in the lookup table, and keep repeated transactions in the fact table.
Add tables to the Data Model
In Excel for Microsoft 365 on Windows, add each table to the model through Power Query. Start with a table in the workbook, select a cell inside it, and choose Data > From Table/Range. Power Query Editor opens. You can check the column types and remove columns you do not need, then choose Home > Close & Load > Close & Load To.
In the Import Data dialog, select Only Create Connection and check Add this data to the Data Model, then confirm. Repeat for each table you want to include. Depending on how the workbook is set up, the Close & Load To command may appear directly as a menu choice; the important settings are to create a connection and add the query output to the model.
If the tables are external files or databases, start with the relevant command under Data > Get Data, choose the source, and select the tables in Navigator. In the load options, add the selected queries to the Data Model. For an existing query, right-click it in Queries & Connections, choose Load To, and adjust its load settings.
Some Windows installations may not show every Power Pivot command. The Power Pivot add-in can be enabled through File > Options > Add-ins, selecting COM Add-ins in the Manage box, choosing Go, and checking Microsoft Power Pivot for Excel if it is available. Availability depends on the Excel edition. The Data Model and Power Pivot authoring experience are not the same on every platform.
Create relationships between tables
After loading the tables, create a relationship for each pair that should work together in reports. On Windows, choose Data > Relationships, select New, and specify the table and column on each side. For the sales example, connect Sales.ProductID to Products.ProductID, then confirm the relationship. You can also inspect and manage model relationships in Power Pivot > Manage, using Diagram View when available.
In a typical one-to-many relationship, the lookup table is on the one side because its key has unique values, and the transaction table is on the many side because the same key can appear in many records. Excel needs compatible columns and a valid unique key on the lookup side. A blank or unmatched key in the transaction table may not find a corresponding description.
Excel relationships connect one column to one column; they are not a general-purpose way to match on several fields at once. If a real-world match depends on multiple fields, create a suitable key in Power Query or redesign the model so there is a unique identifier. For more complex many-to-many situations, a bridge table containing the relevant keys is often needed. Avoid creating multiple ambiguous paths between the same tables, since they can make reports confusing or prevent the model from resolving a filter correctly.
Build a PivotTable from related tables
Once the tables are in the model and related, create a PivotTable and use the workbook’s Data Model as its source. In the PivotTable creation options, choose Use this workbook’s Data Model if it is offered. The PivotTable Fields pane can then show fields from the model’s different tables.
For example, place Products[Category] in Rows and the sales amount field from Sales in Values. Excel can group transactions by category through the ProductID relationship. Add a date field from a related calendar table or a customer field from a customer table to build additional views, provided those tables have valid relationships.
A Data Model PivotTable is useful when you want to analyze multiple related tables without first making a giant worksheet range. If you later refresh the source data or queries, refresh the model and its PivotTables so the report reflects the updated records. A relationship does not automatically fix bad source data: missing IDs and inconsistent categories still need to be addressed.
Relationship model, Power Query merge, or append?
The three approaches solve different problems, so a useful decision is based on the shape of the data and the desired output:
- Relationship: Connects tables in the Data Model for analysis. Choose it when separate tables have a shared key and you want a PivotTable to use fields from each.
- Append: Stacks rows from tables with similar columns. Choose it for monthly files containing the same kind of transactions—for example, January sales and February sales—with matching field names.
- Merge: Matches rows from two queries and adds selected columns to one query result. Choose it when you need a flattened table, such as adding a product category to each transaction before exporting the result.
In Power Query Editor, append is available from Home > Append Queries, while joining columns is done with Home > Merge Queries. A merge requires choosing matching columns and a join kind, such as Left Outer. That is different from adding a relationship: a merge produces a query result with columns from both sides, while a relationship leaves the tables separate in the model.
Do not append a product list to sales transactions simply because both are tables; their rows represent different things. Likewise, do not merge two monthly transaction files if your goal is simply to stack their records. A clear model starts by asking whether you need to link, stack, or join.
Common problems and how to diagnose them
If a relationship cannot be created, first check the two key columns. The most frequent causes are a duplicate value on the lookup side, mismatched data types, or selecting the wrong columns. A code stored as the number 123 in one table and the text 123 in another may look identical on screen but still fail to match as intended.
If a PivotTable shows blank labels or an unexpected blank category, inspect the transaction keys for values absent from the lookup table, as well as empty keys. If categories repeat or totals appear unexpectedly high, confirm that the lookup key is unique and that the relationship points to the intended columns. Also verify that the Values area is summarizing the intended numeric field.
When totals seem wrong, check whether the source contains duplicate transactions, whether Power Query has filtered out rows, and whether the relationship direction and table roles make sense. A relationship connects data; it does not deduplicate records or decide which business rule should govern them. For a more complicated model, test the report with a small set of known records whose expected totals you can verify manually.
Windows, Mac, and Excel for the web
The steps above target Excel for Microsoft 365 on Windows, where the Data Model and Power Pivot authoring tools are available in supported editions. Excel for Mac does not provide the same Power Pivot model-building and relationship-management interface, so do not expect the Windows menu paths to appear there. If you need to create or change a Data Model, use Excel for Windows.
Excel for the web also does not offer the full Windows Data Model and Power Pivot authoring workflow. A workbook containing a model may have limited report interaction in supported circumstances, but creating and managing the model is a desktop Windows task. If you collaborate across platforms, confirm that the features your report depends on are available to the people who need to edit it, not just view it.
Frequently Asked Questions
Can I combine two Excel tables without copying them into one sheet?
Yes. Add both tables to the Data Model and create a relationship between their matching key columns. A PivotTable can then use fields from both tables while the source tables remain separate.
Does creating a relationship merge the tables?
No. A relationship links the tables for analysis but keeps them as separate tables. Use Power Query Merge if you need a new table that contains columns from both sources.
Can I relate tables when the matching column contains duplicates?
The column on the lookup, or one, side must have unique values for a standard one-to-many relationship. Repeated values are expected on the many side. If both tables repeat the key, consider a distinct lookup table or a bridge-table design.
Can I use a Data Model with a normal PivotTable?
Yes. Create a PivotTable using the workbook’s Data Model as its source. This allows the field list to include tables in the model, rather than only one worksheet range.
Should I append or relate tables with the same columns?
If they are separate batches of the same kind of records, append them to stack the rows. If they represent different kinds of information that share a key, keep them separate and relate them.
Do I need Power Pivot to create every relationship?
On Windows, you can create relationships through Data > Relationships, and Power Pivot provides additional tools for managing and viewing the model. The available commands can vary by Excel edition and platform.
Choosing the right approach
For related information such as sales and products, add the tables to the Data Model and connect them through a clean, unique lookup key. For matching monthly or departmental lists with the same structure, append their rows. For a single output that needs columns from both sources, merge them in Power Query. Keeping those three operations distinct makes the model easier to refresh, troubleshoot, and explain—and helps ensure a PivotTable answers the question you actually intended to ask.
Related
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Shortcut Keys to Hide and Unhide Rows and ColumnsIn Excel for Microsoft 365 on Windows, press Ctrl+9 to hide selected rows and Ctrl+0 to hide selected…
- Excel Scatter Plot With Dates: Setup and FormattingTo make an Excel scatter plot with dates, put real Excel dates in one column and the corresponding…
- Create an Email Distribution List in Outlook 365To create an email distribution list in Outlook 365, open People and choose New contact list in new…
- Google Sheets Add Item to Dropdown: Steps and TipsTo add an item to a Google Sheets dropdown, select a cell that uses the dropdown, open Data…
- How to Insert a Fillable Line in Microsoft WordTo add a fillable line in Word, place a text control inside a one-cell table and give the…
- How to Lock Cells in Excel and Protect Your Sheet SafelyTo lock cells in Excel, first unlock any cells people should be able to edit, then protect the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.