How to Duplicate Rows Based on a Quantity Column in Excel
An order line says "Widget A ×3", but the label printer, the asset register and the picking list all want one row per unit. Unpacking a quantity column into individual rows is the exact inverse of a SUM — and Excel has no button for it.
Last updated: October 2026
| Item | Qty | Item | Qty | Unit | |
|---|---|---|---|---|---|
| Widget A | 3 | → | Widget A | 1 | 1 |
| Widget B | 1 | → | Widget A | 1 | 2 |
| Widget A | 1 | 3 | |||
| Widget B | 1 | 1 |
Method 1 — Power Query
- Load the table into Power Query (Data ▸ From Table/Range).
- Add a custom column:
= List.Repeat({1}, [Qty]). - Expand the new list column to rows, then remove it and load back.
- It works, and it is four steps in a separate window that has to be redone — or refreshed — every time the data changes.
Method 2 — the 1-click way (Tellsheet)
Expand Rows by Quantity
Select the table, point Expand Rows by Quantity at the quantity column, and every row is repeated that many times on a new sheet — so a result several times taller than the original can never land on top of whatever sits below it. Each copy's quantity becomes 1 by default, so totals still add up; keep the original number instead if you prefer. Optionally number the units 1, 2, 3… within each row for serial numbers and asset tags. Rows whose quantity is zero drop out; rows whose quantity isn't a number are kept once and reported, never silently dropped.
Where this comes up
- Labels and asset tags — one printed label per physical unit.
- Serial or licence numbers — one row to assign each number to.
- Picking and packing lists — a line per item to tick off.
- Statistics — turning a frequency table back into the raw observations a test expects.
- Raffles and sampling frames — one entry per ticket held.
Frequently asked questions
How do I repeat a row N times?
Power Query's List.Repeat, VBA, or Expand Rows by Quantity in one click.
What about a quantity of zero?
The row drops out. A quantity that isn't a number is kept once and reported.
Will my totals still match?
Yes — each copy's quantity becomes 1 by default.
Can the units be numbered?
Yes, with the optional Unit column.
Related Excel guides
One row per unit, in one click
Expand Rows by Quantity unpacks an aggregated table onto a new sheet — with a preview before anything is written.
Get Tellsheet free See pricing