How to Transform Customer Products into Separate Columns in Excel
Question details
The user needs to reshape source data containing multiple rows of customer-product pairs into a single row per customer, splitting the products into separate columns (e.g., Product 1, Product 2).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Restructuring a database export or raw customer list to make the data more readable for reporting and analysis.
- Observed behavior
- Data is currently listed vertically with repeated customer IDs across multiple rows, but the goal is to view one unique customer per row with their respective products spread horizontally.
Ensure your raw source data is formatted as an official Data Table (Insert > Table) so that any future rows you add will be automatically included in your data transformation.
Use Power Query to Group and Pivot Data
Power Query is the most efficient and scalable method for transposing multiple product rows into distinct, sequentially numbered columns per customer.
This method requires adding an index to your grouped data so Power Query knows exactly how to number the columns (Product 1, Product 2, etc.) during the pivot operation.
Select any cell in your source table and navigate to Data > From Table/Range on the ribbon to open the Power Query Editor.
Select the Customer ID column, click Transform > Group By. Choose 'Customer ID' for the grouping and add a new column operation named 'AllData' with the operation set to 'All Rows'.
Add a custom step or use the advanced editor to add a 1-based index within each customer's nested table. This identifies the first, second, and third products for every customer.
Expand the newly indexed data, select the Index column, and click Transform > Pivot Column. Select your Product Name column as the Values Column, and under Advanced Options, set the Aggregate Value Function to 'Don't Aggregate'.
Click Home > Close & Load to output the reshaped, multi-column customer data back into a new Excel worksheet.
Update an Existing Power Query Setup
If you are using a prepared template or workbook that already has the Power Query transformation configured, you only need to refresh the data.
Organize and Summarize Customer Data Seamlessly with WPS Spreadsheet
WPS Spreadsheet offers powerful built-in data analysis tools, including advanced PivotTables, array formulas, and intuitive data filtering, making it incredibly easy to summarize and restructure complex customer data into clear, actionable reports.
- 1. Open your dataset: Launch WPS Spreadsheet and open your customer product file.
- 2. Insert a PivotTable: Select your entire data range, then navigate to Insert > PivotTable on the ribbon.
- 3. Configure rows and columns: In the PivotTable fields panel, drag 'Customer ID' to the Rows area, and drag 'Product' to the Columns or Values area depending on your layout preference.
- 4. Format and export: Adjust the formatting for better readability, and save your document natively as an .xlsx file to share with other stakeholders.
Frequently Asked Questions
Can I separate the product columns using standard Excel formulas instead of Power Query?
Yes, you can achieve this using modern Dynamic Array formulas like FILTER and TRANSPOSE in newer versions of Excel, or complex combinations of INDEX, MATCH, and COUNTIFS in older versions. However, Power Query is generally more stable and easier to maintain for large datasets.
Will the product columns update automatically if I add new data?
When using Power Query, the results do not update in real-time. You must manually go to the Data tab and click 'Refresh All' for the new customer rows to be processed into separate columns.
How do I rename the column headers from '1', '2', '3' to 'Product 1', 'Product 2'?
Before performing the Pivot step in Power Query, you can add a custom prefix to your Index column. Select the Index column, go to Transform > Format > Add Prefix, and type 'Product '. Then proceed with pivoting.
