Function Problems

How to Expand Rows by Quantity Across Multiple Excel Columns

Kushani Nimanthika Sep 25, 2026 868 views

Question details

The user needs to duplicate or expand data rows based on a specific 'Quantity' column and apply this technique across more than two source columns.

How to Expand Rows by Quantity Across Multiple Excel Columns

Product: Excel
Device & OS: not provided
Scenario: Expanding dataset records where a single row needs to be replicated multiple times according to a specified quantity value, applied across multiple data columns.
Observed behavior: The user wants to achieve a goal state where each source row is duplicated exactly the number of times specified in the quantity column for all selected source columns.

Before you start

Ensure your dataset is organized with clear column headers, and verify that the 'Quantity' column contains only positive integers to prevent formula errors.

Solution 1Recommended

Use INDEX, MATCH, and COUNTIF Array Formulas to Expand Rows

Apply an array formula using INDEX, MATCH, and COUNTIF functions to dynamically expand records across multiple columns based on a specified quantity column.

This method uses a combination of lookup and counting formulas to check how many times a row has already been duplicated in the new columns. Once the count reaches the designated quantity, the formula automatically moves to the next source row.

Prepare your output columns

Set up new output columns (e.g., F, G, H) corresponding to your source columns (e.g., A, B, C), assuming column E contains the Target Quantities.

Input the formula for the first column

In the first cell of your output column (e.g., F2), enter the formula: =IF(ROW()-1<=SUM($E$2:$E$100),INDEX($A$2:$A$100,MATCH(TRUE,(COUNTIF($F$1:F1,$A$2:$A$100)<$E$2:$E$100),0)),""). Press Ctrl+Shift+Enter to evaluate it as an array formula.

Adjust the formula for additional columns

For the second output column (e.g., G2), adjust the source range to B and the running count to G: =IF(ROW()-1<=SUM($E$2:$E$100),INDEX($B$2:$B$100,MATCH(TRUE,(COUNTIF($G$1:G1,$B$2:$B$100)<$E$2:$E$100),0)),""). Press Ctrl+Shift+Enter.

Copy the formula down

Select the formula cells in your output columns and drag the fill handle down until blank cells appear, indicating that all quantities have been fully expanded.

Use INDEX, MATCH, and COUNTIF Array Formulas to Expand Rows

Array Formulas Requirement: Depending on your spreadsheet version, these specific array formulas must be entered by pressing Ctrl+Shift+Enter simultaneously, rather than just hitting Enter.

Efficient Data Processing in WPS Spreadsheet

Easily Expand Rows by Quantity in WPS Spreadsheet

WPS Spreadsheet provides powerful array functions and a seamless formula experience to help you dynamically expand and manipulate rows based on quantity variables with full compatibility.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing the rows you want to expand.
2. Apply the array formula: Select the first cell in your output column and type the INDEX and MATCH array formula provided in the solution.
3. Evaluate the formula: Press Ctrl+Shift+Enter to evaluate the array formula correctly. WPS will automatically wrap the formula in curly brackets.
4. Expand to remaining rows: Drag the fill handle down to expand the rows for the rest of your dataset across all required columns.

100% compatible with Microsoft Excel formulas and array functionsAdvanced computational capability for complex data manipulationLightweight and fast, remaining responsive even with large datasetsFree to download with a familiar, easy-to-use interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my array formula returning an error or just one value?

Ensure you are committing the formula by pressing Ctrl+Shift+Enter instead of just Enter. This wraps the formula in curly brackets {}, which tells the spreadsheet to process it as an array formula.

Can I expand rows using Power Query instead of formulas?

Yes, Power Query is highly efficient for this. You can add a custom column using the formula {1..[Quantity]}, expand the new column to new rows, and achieve the exact same result without using complex array formulas.

Will these array formulas slow down my spreadsheet?

Array formulas recalculate frequently and can slow down performance on very large datasets. If you have thousands of rows to expand, consider using Power Query or VBA macros to optimize performance.

Excel & Data Problems