Convert an Order Items List into Excel Order-Line Rows
Question details
Convert a cell containing multiple order items into a normalized table with one item per row.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- An order report needs separate rows for each item, including its quantity, unit price, and line total.
- Observed behavior
- Multiple item types and quantities are stored together in one cell, making the orders difficult to report on.
Check whether items use a consistent separator, such as a plus sign, and how each quantity and unit price is associated with its item. Identify the Order Number column and confirm that unit prices are available.
Split consistently formatted order items into rows with Power Query
Use this method when item entries follow a consistent pattern and each item's quantity and price can be identified.
For example, an items cell formatted as `Widget|2|3.50 + Cable|1|5.00` can be split first on `+` and then on `|`. Use your actual separators; do not assume a format if the source data differs.
If quantities or prices are stored separately, match them to each order item using reliable identifiers before calculating totals. Splitting two separate lists into rows independently can produce incorrect combinations.
Select the source range, choose Insert > Table, and then select Data > From Table/Range to open it in Power Query.
Select the items column and choose Home > Split Column > By Delimiter. Enter the item separator, such as `+`, expand Advanced options, select Split into Rows, and choose OK.
Select the resulting items column and choose Transform > Format > Trim. If each entry contains item, quantity, and price separated by another delimiter, use Split Column > By Delimiter again to split it into columns, then rename them Item, Quantity, and Unit Price.
Set Quantity and Unit Price to appropriate numeric types using their column headers. Choose Add Column > Custom Column and enter `[Quantity] * [Unit Price]`; name the new column Line Total.
Choose Home > Close & Load to load the order-line table. To calculate order totals, create a PivotTable from the loaded table, place Order Number in Rows, and place Line Total in Values using Sum.

Build an Excel order-line table manually
Use this approach for a small order list or when item details do not follow a consistent format.
Create an order-line table in WPS Spreadsheets
Use WPS Spreadsheets to enter one item per row, calculate line totals, and sum them by order number.
- 1. Open the workbook: Open WPS Spreadsheets and use File > Open to select the order workbook.
- 2. Create the order-line table: Add a worksheet and enter Order Number, Item, Quantity, Unit Price, and Line Total as headers in columns A through E.
- 3. Enter item details: Enter one item per row, repeating its order number in column A and placing its quantity and unit price in columns C and D.
- 4. Calculate line totals: Enter `=C2*D2` in E2 and drag the fill handle down for the remaining rows.
- 5. Sum each order: On a summary sheet, enter an order number in A2 and use `=SUMIF(Sheet2!A:A,A2,Sheet2!E:E)` to total its lines. Replace `Sheet2` with the actual order-line sheet name.

Frequently Asked Questions
How do I split a cell into multiple rows in Excel Power Query?
Load the range with Data > From Table/Range, select the items column, and choose Split Column > By Delimiter. Under Advanced options, select Split into Rows.
What if quantities are in a separate cell from the item names?
First determine how each quantity corresponds to an item. Match the values using reliable order and item identifiers; splitting separate lists independently can create incorrect item-quantity combinations.
How do I calculate an order total from the separate rows?
Multiply Quantity by Unit Price for each line, then use Insert > PivotTable to group by Order Number and sum Line Total. You can also use SUMIF against the order-number and line-total columns.
Will splitting an item list automatically find unit prices?
No. Power Query can separate prices already present in a consistent item entry, but prices stored elsewhere must be matched to the correct items before line totals can be calculated.




