VBA & Macro Problems

Repeat Excel Cell Values with a Formula or VBA

Rana GarciaRana Garcia Nov 20, 2026 870 views

Question details

Repeat each value in an Excel column on several consecutive rows using a formula or VBA.

Repeat Excel Cell Values with 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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

For source values in A2:A4, click B2 or another empty cell outside the source range.

2
Enter the formula

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.

3
Fill the formula down

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.

4
Keep fixed values if needed

Select the result cells, copy them, and use Home > Paste > Values to replace the formulas with their results.

Repeat values in a separate column with an INDEX formula
Avoid filling too far: The formula returns an error if it is filled beyond the number of source cells multiplied by the repeat count.
Repeat values in WPS Spreadsheet

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. 1. Open the workbook: Open WPS Spreadsheet and use File > Open to select your Excel workbook.
  2. 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. 3. Fill down: Drag the fill handle from B2 through B13 to display each of the three source values four times.
  4. 4. Save the result: Use File > Save As and choose an Excel workbook format such as XLSX.
Repeat names or other values by changing the formula's repeat countKeep source data intact in a separate columnOpen and save Microsoft Excel XLSX workbooksUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.