Function Problems

How to Expand Excel Quantities into Individual Rows

Huma Ashraf Ch Oct 9, 2026 869 views

Question details

The user wants to transform a summarized Excel table by expanding a quantity column so that each unit of quantity is represented as a separate individual row in a new table.

How to Expand Excel Quantities into Individual Rows

Product: Excel
Device & OS: not provided
Scenario: Flattening or expanding a dataset where each item has an aggregated quantity value into individual rows per item for more detailed tracking, scanning, or analysis.
Observed behavior: The data is currently summarized with a single row containing a quantity value, but the desired outcome is a dynamic list where each row is repeated a number of times equal to its quantity.

Before you start

Ensure your version of Excel supports dynamic array functions (such as SEQUENCE, HSTACK, VSTACK, and REDUCE), which are required for the automated formula approach. Also, verify you have enough completely empty space below and to the right of your formula to allow the results to spill without error.

Solution 1Recommended

Use a Dynamic Array Formula

Automatically generate individual rows by combining REDUCE, SEQUENCE, and VSTACK functions to iterate through your table and repeat rows based on the quantity value.

This method uses modern dynamic array functions to iterate over your source table and duplicate rows according to the specified quantity column.

Make sure to adjust the cell references and the total row count in the formula to match your actual data layout exactly.

Identify Source Data Ranges

Note the ranges for your data. For example, assume Products are in B5:B6, Dates in C5:C6, and Quantities in D5:D6.

Select Target Output Area

Select an entirely empty cell where you want the new expanded table to begin. Do not select a range; just a single starting cell.

Enter the Formula

Input the following formula: =DROP(REDUCE("",SEQUENCE(2),LAMBDA(i,n,LET(a,INDEX(B5:B6,n),q,INDEX(D5:D6,n),d,INDEX(C5:C6,n),s,SEQUENCE(q,,,0),VSTACK(i,HSTACK(REPT(a,s),REPT(d,s)+0,s))))),1)

Adjust Row Counts and Execute

Change the '2' in SEQUENCE(2) to match your total number of source rows. Press Enter, and the formula will automatically spill the expanded rows into the adjacent empty cells.

Handling Spilled Array Errors: If you see a #SPILL! error, or simply a 0 instead of the table, ensure there is no hidden data, formatting, or text blocking the area where the formula needs to display its results. The target area must be completely clear.

Solution 2

Expand Rows Using Power Query

A visual, formula-free alternative to expand rows based on quantities using Excel's built-in Power Query editor.

Easily Manage Data with WPS Office

Expand and Organize Data Effectively in WPS Spreadsheet

WPS Spreadsheet fully supports advanced data manipulation and dynamic array formulas, allowing you to seamlessly transform and expand your datasets using the exact same formulas used in Microsoft Excel.

1. Open Dataset: Open your raw dataset in WPS Spreadsheet.
2. Select Destination: Click on an empty cell where the expanded data should begin spilling.
3. Input Formula: Input the dynamic array formula combining REDUCE, SEQUENCE, and VSTACK, making sure to adjust the references for your specific data ranges.
4. View Expanded Data: Press Enter to execute. The data will expand instantly without any manual copying and pasting.

Fully compatible with Microsoft Excel formulas, functions, and formatting.Supports modern dynamic arrays to easily handle complex data manipulation tasks.Lightweight and runs smoothly on Windows, Mac, Linux, iOS, and Android.Free and easy-to-use interface, perfect for both beginners and data professionals.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #NAME? error?

This error occurs if your version of Excel does not support newer dynamic array functions like REDUCE, LAMBDA, or VSTACK. You must be using Microsoft 365 or Excel 2021 and later for these functions to work.

Why am I getting a #SPILL! error when I enter the formula?

A #SPILL! error means there is existing data, invisible text, or even a space character in the destination cells where the formula needs to output its results. You must clear the surrounding cells to fix this issue.

How do I adjust the formula if my source table has 10 rows instead of 2?

Look for the SEQUENCE(2) segment near the beginning of the formula. Change the number 2 to match the exact number of rows in your source data, so it becomes SEQUENCE(10).

Excel & Data Problems