How to Create Excel PivotTable Relationships Between Teams and Categories
Question details
The user needs to connect two separate tables (one with unique labels and categories, another with labels, people, and teams) to calculate category percentages by team for a pie or doughnut chart.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating pie or doughnut charts that display category percentages broken down by team, which requires pulling data from two separate mapping tables.
- Observed behavior
- Data is currently split across two tables, meaning they must be connected or merged before a PivotTable can accurately calculate totals and percentages for charting.
Ensure both data sets are formatted as official Excel Tables (Insert > Table) and that both tables share a common column containing exact matching data, such as a unique label or ID.
Create a Relationship Using the Excel Data Model
Using the Data Model is the most robust way to link tables for a PivotTable without physically merging or altering your source data.
The Excel Data Model allows you to integrate data from multiple tables, effectively building a relational database inside your workbook. This method is highly recommended for larger datasets as it keeps your file size smaller and avoids complex, processing-heavy formulas.
Select your team data, press Ctrl+T, and click OK. Name this table 'TeamTable' in the Table Design tab. Repeat this process for your category data, naming it 'CategoryTable'.
Click anywhere inside 'TeamTable', navigate to the Insert tab, and click PivotTable. In the dialog box, ensure you check the box at the bottom that says 'Add this data to the Data Model', then click OK.
In the new sheet, look at the PivotTable Fields pane on the right. Click on the 'All' tab at the top of the pane to view both 'TeamTable' and 'CategoryTable'.
Drag 'Team' from the TeamTable and 'Category' from the CategoryTable into the PivotTable areas. A yellow warning box will appear stating 'Relationships between tables may be needed'. Click the 'Create' button.
In the Create Relationship dialog, select 'TeamTable' as the Table and choose the shared column (e.g., 'Label'). Select 'CategoryTable' as the Related Table and choose its corresponding 'Label' column. Click OK to finalize the connection.
Merge Tables Using Lookup Functions (VLOOKUP / XLOOKUP)
If you prefer working with a single flat table, you can pull the category data directly into the team table using Excel lookup formulas.
Easily Manage Multi-Table Data with WPS Spreadsheet
WPS Spreadsheet provides powerful data processing tools, including advanced lookup functions and dynamic PivotTables, making it incredibly easy to analyze complex datasets and create stunning pie or doughnut charts without performance lag.
- 1. Open your Workbook: Launch WPS Spreadsheet and open the file containing your team and category tables.
- 2. Merge Data with VLOOKUP: Add a new column to your team table and use the VLOOKUP or XLOOKUP function to pull the category names based on the shared label field.
- 3. Insert a PivotTable: Select your combined table, navigate to the 'Insert' tab, and click 'PivotTable' to create a new summary table.
- 4. Summarize Data: Drag the 'Team' field to the Rows area and the 'Category' field to the Values or Columns area to calculate the percentages.
- 5. Insert Your Chart: With the PivotTable selected, go to the 'Insert' tab and choose a Pie or Doughnut chart to visualize your category percentages by team.
Frequently Asked Questions
Why is my PivotTable not showing data from both tables?
If you haven't added the tables to the Data Model or established a relationship via a common column, the PivotTable cannot cross-reference the fields. You must either create a relationship using the Data Model or merge the tables using a lookup function first.
Can I create a pie chart directly from two unconnected tables?
No, standard charts require the underlying data to be unified. You must either consolidate the data into a single table using lookup formulas or establish a relationship in the Data Model before summarizing the data for a chart.
What is the difference between merging with VLOOKUP and using the Data Model?
VLOOKUP physically adds the data to your existing table, which increases file size but makes the data easy to view in one place. The Data Model links the tables in the background, which is far more efficient for large datasets and complex multi-table analysis.
Does WPS Office support VLOOKUP and XLOOKUP for merging tables?
Yes, WPS Spreadsheet fully supports standard Excel lookup functions, including VLOOKUP, HLOOKUP, and XLOOKUP, allowing you to easily bring category labels into your main team dataset.
