Repeat Excel Cell Values with a Formula or VBA
Question details
Repeat each value in an Excel column on several consecutive rows using a formula or VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to display each name or other cell value four times down a column, either in a separate result column or by inserting rows.
- Observed behavior
- Each source value appears on the specified number of consecutive rows.
Identify the source range and decide how many times each value should appear. The examples below assume nonblank values start in A2; the VBA option inserts entire worksheet rows and can affect data in other columns.
Repeat values in a separate column with an INDEX formula
Use a formula when you want repeated values without inserting rows into the source data.
For source values in A2:A4, click B2 or another empty cell outside the source range.
In B2, enter =INDEX($A$2:$A$4,ROUNDUP(ROWS($B$2:B2)/4,0)) and press Enter. Change $A$2:$A$4 to your source range and 4 to the desired repeat count. If starting in another cell, replace both B2 references with that cell.
Drag the fill handle from B2 down to B13. For a different range, fill down for the number of source cells multiplied by the repeat count.
Select the result cells, copy them, and use Home > Paste > Values to replace the formulas with their results.

Insert rows and repeat values with VBA
Use this method when the repeated values need to occupy newly inserted worksheet rows.
Repeat column values with a WPS Spreadsheet formula
Use an INDEX formula in WPS Spreadsheet to display each source value multiple times without inserting rows.
- 1. Open the workbook: Open WPS Spreadsheet and use File > Open to select your Excel workbook.
- 2. Enter the formula: If the source values are in A2:A4, click B2 and enter =INDEX($A$2:$A$4,ROUNDUP(ROWS($B$2:B2)/4,0)). Adjust the source range and repeat count as needed.
- 3. Fill down: Drag the fill handle from B2 through B13 to display each of the three source values four times.
- 4. Save the result: Use File > Save As and choose an Excel workbook format such as XLSX.

Frequently Asked Questions
How do I change the number of times each value is repeated?
In the formula, replace the 4 in ROUNDUP(ROWS($B$2:B2)/4,0) with the desired count. Fill down for the number of source cells multiplied by that count.
How can I keep the repeated values without formulas?
Select and copy the formula results, then use Home > Paste > Values to replace the formulas with fixed values.
Does the VBA macro duplicate the rest of each row?
No. It inserts entire worksheet rows but fills only column A. Data in other columns shifts as rows are inserted, so check the worksheet layout before running it.
Why does the formula show an error at the bottom?
The formula can return an error when filled beyond the source range. Limit the result rows to the number of source cells multiplied by the repeat count, or expand the source range.
What file format should I use for the VBA macro?
In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) to retain the VBA code.




