Excel Power Query: Use Multiple Customer Fields in PivotTable Rows
Question details
Split a comma-separated customer field into individual customer rows so quoted amounts can be summarized by customer in a PivotTable.
- Product
- Microsoft Excel Power Query and PivotTable
- Device & OS
- not provided
- Scenario
- A worksheet stores multiple customer names in one field, separated by commas, while each record also contains a related job and quoted amount.
- Observed behavior
- A standard PivotTable cannot separately summarize each customer name while multiple customers remain combined in one source cell.
Make sure the source data has headers, including the customer field and quoted amount field. Confirm whether the full quoted amount should be counted for every listed customer, because splitting into rows repeats that amount for each customer.
Split the customer field into rows with Excel Power Query
Use this method when customer names are comma-separated in a single cell and each customer needs its own PivotTable row while retaining the related job and amount.
Power Query normalizes the source list by creating one record per customer. Unlike Text to Columns, splitting by delimiter into rows keeps the related job and quoted amount alongside every resulting customer record.
Select any cell in the source range, then choose Insert > Table. Confirm that My table has headers is selected, and click OK.
Click any cell in the new table, then select Data > From Table/Range to open the Power Query Editor.
Select the Customer column, choose Home or Transform > Split Column > By Delimiter, select Comma as the delimiter, open Advanced options, choose Split into Rows, and click OK.
With the Customer column selected, choose Transform > Format > Trim to remove leading spaces after commas. Remove columns that are not needed for the PivotTable by selecting them and choosing Home > Remove Columns.
Choose Home > Close & Load To, load the query as a table, then select Insert > PivotTable. Drag Customer to Rows and Quoted Amount to Values; keep Job in Rows, Filters, or Columns if needed.
Refresh the PivotTable after customer data changes
Use this after adding or editing customer names, jobs, or quoted amounts in the original Excel table.
Analyze Customer and Quote Data with WPS Spreadsheet
If you need a free, lightweight spreadsheet application for organizing customer lists and building PivotTables, WPS Office provides a familiar interface and seamless migration for common Excel workbooks. Open your existing files, clean customer data with spreadsheet tools, and create summaries without changing your usual workbook format.
Frequently Asked Questions
Why should I split multiple customer names into rows instead of columns?
A PivotTable summarizes records vertically. In Power Query, choose Split Column > By Delimiter and select Split into Rows so every customer becomes a separate record with its related quoted amount.
Will splitting customers into rows duplicate the quoted amount?
Yes. Power Query repeats the original quoted amount on every customer row created from that source record. This is correct only when each listed customer should receive the full amount in the summary.
How do I remove spaces after splitting comma-separated customer names?
In Power Query Editor, select the Customer column and choose Transform > Format > Trim. This removes spaces before or after each customer name and prevents duplicate-looking PivotTable labels.
Can I keep the job number when I split the customer field?
Yes. Leave the Job column in the query. When the Customer column is split into rows, Power Query retains the job number and other existing fields on each new customer row.
How do I update the PivotTable when new customer records are added?
Add the new record to the original Excel table, then choose Data > Refresh All. Click the PivotTable and select PivotTable Analyze > Refresh if it does not update automatically.
