How to Create a Pivot Table for Tasks Assigned to Multiple People in Excel
Question details
The user needs to create a Pivot Table to track tasks that are assigned to multiple people or units, but the source data currently has assignees split across separate columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing task assignments and building summary reports when individual tasks are handled by more than one person or department.
- Observed behavior
- Filtering and grouping are difficult because the assignees are spread across multiple columns (e.g., Unit 1, Unit 2), preventing the Pivot Table from consolidating all tasks per unit accurately.
Ensure your source data is organized in a clear tabular format without merged cells or blank rows, as these will interfere with the data restructuring process.
Use Power Query to Unpivot Data Before Creating the Pivot Table
Using Power Query is the most efficient and dynamic way to normalize your data, turning multiple assignment columns into a single column for accurate Pivot Table reporting.
Pivot tables work best when each task-owner assignment is stored as a separate row in one owner column. By unpivoting the data, a task assigned to two units will automatically be split into two rows.
Select any cell within your task list, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
Hold the Ctrl key and click the column headers for all the columns containing the assigned people or units (e.g., Unit 1, Unit 2).
Right-click on one of the selected column headers and choose 'Unpivot Columns'. This will collapse the multiple columns into 'Attribute' and 'Value' columns.
Rename the 'Value' column to 'Assigned Unit' or 'Owner', then click 'Close & Load' on the Home tab to output the normalized data into a new worksheet.
Select the newly generated table, go to the 'Insert' tab, click 'PivotTable', and use the new 'Owner' column to easily group and filter all assigned tasks.
Manually Restructure the Source Data Rows
For very small datasets, you can manually duplicate rows to ensure each task has only one unit assigned per row without using external tools.
Easily Manage Data and Pivot Tables with WPS Spreadsheet
WPS Spreadsheet provides powerful data analysis tools, including advanced Pivot Table functionalities and seamless data management features. You can easily restructure your lists and summarize complex assignments in seconds.
- 1. Organize your data: Open your dataset in WPS Spreadsheet and ensure your assignees are consolidated into a single column.
- 2. Insert a PivotTable: Navigate to the 'Insert' tab and click on 'PivotTable'.
- 3. Select range and generate: Confirm your data range, choose where to place the PivotTable, and drag your Task and Assignee fields into the Rows and Values areas to instantly generate your report.
Frequently Asked Questions
Why does my Pivot Table show separate groups for Unit 1 and Unit 2?
Pivot tables group data based on unique values within a single column. When your data is spread across multiple columns, the Pivot Table treats them as entirely separate categories rather than variations of the same attribute.
Can I group multiple text columns in a standard Pivot Table without Power Query?
No, standard Pivot Tables cannot automatically combine multiple dimension columns (like Unit 1 and Unit 2) into one unified group. You must restructure or unpivot the underlying data first.
What does 'Unpivot' mean in spreadsheet data management?
Unpivoting transforms data from a wide format (multiple columns for similar data) into a long format. It consolidates multiple column headers into a single 'Attribute' column and places their corresponding data into a 'Value' column, which is the required format for building effective Pivot Tables.
