How to Repeat Each Array Value a Set Number of Times in Excel
Question details
The user needs to repeat each number in a given array a specific number of times using non-volatile Excel functions.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Expanding a list of values by duplicating each value a fixed number of times directly below one another in a single column.
- Observed behavior
- The goal is to generate a new list where each original value appears sequentially multiple times (e.g., converting 3, 7, 6 into 3, 3, 3, 7, 7, 7, 6, 6, 6) without using volatile functions like OFFSET that can slow down workbook performance.
Ensure your source data is organized in a single column or row without blank cells, and decide exactly how many times you want each value to repeat before applying the formula.
Use the INDEX and ROUNDUP Formula (Non-Volatile)
This method uses the INDEX and ROUNDUP functions along with ROW to repeat values dynamically without relying on volatile functions.
This approach is highly efficient, especially for older Excel versions. It avoids volatile functions like OFFSET or INDIRECT that recalculate every time the sheet changes, which can unnecessarily slow down workbook performance.
Locate the range of your original values. For this example, assume your source values are in cells A2:A4.
Click on the cell where you want the new repeated list to start and type the formula: =IFERROR(INDEX($A$2:$A$4,ROUNDUP(ROW(A1)/3,0)),"")
Press Enter to generate the first value. Then, click and drag the fill handle (the small square at the bottom-right of the selected cell) down the column until blank cells appear, indicating the sequence is complete.
Easily Manage Arrays and Formulas with WPS Office
WPS Spreadsheet fully supports advanced array formulas, INDEX, and ROUNDUP functions just like Microsoft Excel. It offers a lightweight, highly compatible environment for all your data manipulation needs, completely free of charge.
- 1. Open your data in WPS Spreadsheet: Launch WPS Spreadsheet and input your original array into a column (e.g., A2:A4).
- 2. Apply the INDEX formula: Select an empty cell for your output and enter the =IFERROR(INDEX($A$2:$A$4,ROUNDUP(ROW(A1)/3,0)),"") formula.
- 3. Drag to fill: Press Enter, then click the fill handle on the cell's bottom-right corner and drag it downwards to automatically generate your repeated values.
Frequently Asked Questions
Why should I avoid volatile functions like OFFSET?
Volatile functions recalculate every time any change is made to the workbook. In large spreadsheets, relying heavily on functions like OFFSET or INDIRECT can significantly slow down performance and increase calculation times.
How do I change the formula if my data starts in a different row?
You should keep the ROW(A1) part exactly as it is because it acts as a starting counter (1). Only update the $A$2:$A$4 absolute reference to match the actual range where your source data is located.
Can I use this formula to repeat values across rows instead of down columns?
Yes, but you will need to replace the ROW(A1) function with COLUMN(A1) and adjust your layout so that the formula drags horizontally, pulling the corresponding data across the row.
