How to Create a Pivot Table from Names in Multiple Columns
Question details
The user needs to create a pivot table to count individual assignments when people's names are distributed across multiple job-role columns.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Counting and consolidating names stored in several different columns to generate a summary count, and generating detail sheets upon double-clicking the summarized data.
- Observed behavior
- A standard pivot table cannot accurately count or group the names because they are not contained within a single unified column, and trailing spaces in the raw data cause duplicate entries.
Ensure your source data has clear column headers and no merged cells before you begin transforming it into a single column.
Use Power Query to Unpivot Data and Create a Pivot Table
Transform your multi-column data into a single column using Power Query, which cleans the text and makes the dataset compatible with standard Pivot Table grouping.
A standard pivot table summarizes data based on individual columns. When your target data (names) is spread across multiple columns, you must first "unpivot" the data. This reshapes the dataset from a wide format into a long format, consolidating all names into one column so the Pivot Table can group them correctly.
Select your entire dataset, go to the Home tab, and click 'Format as Table', or press Ctrl+T to convert your data range into an official Table.
Navigate to the Data tab and click 'From Table/Range'. This action will open the Power Query Editor with your selected table data.
Select the columns containing the names. Right-click the column headers, hover over 'Transform', and click 'Trim'. This ensures names with invisible extra spaces are not treated as separate people.
Select all the job-role columns containing the names, right-click any of the selected headers, and choose 'Unpivot Columns'. This consolidates the names into a single column alongside an attribute column showing their role.
Click 'Close & Load To...' in the top left corner. Select 'PivotTable Report' and click OK. Use the newly created 'Value' column (which contains the names) in the Rows and Values area of your Pivot Table to count assignments.
In your newly created Pivot Table, simply double-click any count number. A new sheet will automatically be created containing all the detailed job records for that specific individual.
Seamlessly Analyze Complex Data with WPS Spreadsheet
WPS Spreadsheet provides powerful data analysis tools, including advanced Pivot Tables, allowing you to consolidate complex datasets, analyze job assignments, and drill down into data details effortlessly.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open the Spreadsheet application, and load your .xlsx or .csv data file.
- 2. Consolidate your data: Format your scattered name columns into a single column list so that they can be grouped properly by the Pivot Table engine.
- 3. Insert a Pivot Table: Navigate to the 'Insert' tab on the top ribbon and click 'PivotTable'. Highlight your unified data range and choose to place the PivotTable on a new worksheet.
- 4. Analyze and view details: Drag the unified names field to both the 'Rows' area and the 'Values' area to get an accurate assignment count. Double-click any calculated value to automatically generate a detailed report sheet.
Frequently Asked Questions
Why can't I just make a Pivot Table directly from multiple columns?
A standard Pivot Table summarizes data based on specific fields (columns). If names are spread across multiple fields, the Pivot Table treats each column as a separate category rather than a single unified list, resulting in fragmented and inaccurate counts.
What does 'unpivoting' data mean in spreadsheets?
Unpivoting is the process of transforming data from a 'wide' format (where categories are spread across many columns) into a 'long' format (where all related data points are collapsed into one column). This format is required for proper database grouping and Pivot Table analysis.
Why did my Pivot Table create a completely new sheet when I clicked a number?
This is a built-in analytical feature called 'Show Details'. When you double-click a summarized value in a Pivot Table, the spreadsheet automatically creates a new worksheet displaying all the underlying source rows that make up that specific number.
How do trailing spaces affect Pivot Tables?
Pivot Tables read text literally. If one entry is 'John Smith' and another is 'John Smith ' (with a space at the end), the Pivot Table will treat them as two completely different people. Using the Trim function fixes this issue.
