How to Convert Journal-Style Sales Data into One Row per Customer in Excel
Question details
The user needs to consolidate a sales transaction table where each product is on a separate row into a wide-format report with one row per customer, putting individual product prices into separate columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Transforming transactional journal-style data into a summarized matrix report to easily track which customer bought which products on specific dates.
- Observed behavior
- Currently, the data has multiple rows per customer (one for each product). The goal is to pivot the product categories into columns and aggregate the prices so each customer has exactly one summary row per date.
Ensure your raw sales data is formatted as an Excel Table (press Ctrl+T) and check if you are using Microsoft 365, as the newest array functions depend on this version.
Use the PIVOTBY Function in Microsoft 365
The most efficient, dynamic method to pivot products into columns and aggregate prices, available in the latest versions of Microsoft 365.
The PIVOTBY function is a powerful modern Excel array function designed to instantly group, pivot, and aggregate data without needing a traditional PivotTable.
It automatically updates when new data is added to your source table, ensuring your report is always accurate.
Verify that your data table is named properly, for example, 'SalesTbl', and contains columns for Customer, Date, Product Sold, and Price.
In an empty cell where you want the report to start, type the following formula: =PIVOTBY(HSTACK(SalesTbl[Customer],SalesTbl[Date]),SalesTbl[Product Sold],SalesTbl[Price],SUM)
Press Enter. The formula will spill the results, automatically creating one row per Customer/Date combination and placing Watch, Bike, Jacket, and Pen into separate columns with their respective total prices.
Transform Data Using Power Query
A highly robust alternative for users on older versions of Excel that do not support the newer dynamic array functions.
Consolidate Sales Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet provides powerful data pivoting tools, including highly customizable standard PivotTables, allowing you to transform journal-style data into one row per customer quickly and intuitively—without writing complex array formulas.
- 1. Select your data: Open your journal-style sales dataset in WPS Spreadsheet and select the entire range of cells.
- 2. Insert a PivotTable: Navigate to the Insert tab on the top ribbon and click on 'PivotTable' to create a new report.
- 3. Configure Rows and Columns: In the PivotTable Field List, drag the 'Customer' and 'Date' fields into the Rows area, and drag the 'Product Sold' field into the Columns area.
- 4. Aggregate the Prices: Drag the 'Price' field into the Values area. Ensure the calculation is set to SUM. Your data is now perfectly formatted into one row per customer.
Frequently Asked Questions
Why is the PIVOTBY formula returning a #NAME? error?
This error occurs because your version of Excel does not support the PIVOTBY function. PIVOTBY is a new dynamic array function exclusive to Microsoft 365 subscriptions and Excel for the web. If you use Excel 2019 or older, try using Power Query or standard PivotTables instead.
Can I use standard PivotTables instead of formulas to achieve this layout?
Absolutely. By inserting a standard PivotTable, placing the Customer and Date fields in the Rows area, the Product field in the Columns area, and Price in the Values area (set to Sum), you can instantly achieve the one-row-per-customer layout without any formulas.
Does Power Query automatically update when I add new sales transactions?
Power Query does not update instantly in real-time. However, you can update it in one click: simply right-click anywhere inside the generated Power Query result table and select 'Refresh', or go to the Data tab and click 'Refresh All'.
