How to Repeat Rows for a Specified Number of Times in Excel

Consider a dataset containing columns Item, their Grade, and Stock. We have to repeat some of its rows to make a bill.

Method 1 – Using the Fill Feature to Repeat Rows a Specified Number of Times in Excel

Case 1.1 – Using the Fill Handle Tool

Steps

• Select the whole row that you need to repeat a specified number of times.
• Go to the bottom-right corner of the cell and the cursor will change to the Fill Handle icon (+).
• Click and drag the icon down to repeat rows.

• Once you have selected enough rows, stop dragging and release the mouse.

Read More: How to Repeat Rows in Excel at Bottom

Case 1.2 – Using the Fill Feature from Ribbon

Steps

• Select the row that is under the row you want to repeat. We want the cells B11:D11 to be repeated, so selected cells B12:D12.
• Go to the Home tab and, from the Editing group, click on Fill.
• Click on Down.

• You can see the repeated row.

• Repeat until you get enough copies.

Read More: Repeat Text in Excel Automatically

Method 2 – Using VLOOKUP Function

In the following dataset, you can see the Item and Repeat Time columns. In the Repeat Time column, we put the number of times we want the rows to be repeated.

Steps

• Insert a new column B named Helper Column.
• Put 1 in cell B5.

• Use the following formula in cell B6.

• Press Enter.
• Drag down the formula with the Fill Handle tool.

• Here’s the complete Helper Column.

• Make another column E and name it Column 2.
• Enter 1 to 15 in cells E5 to E20.

• Add a column F named Repeat.
• Use the following formula in cell F5.

• Hit Enter.
• Drag down the formula with the Fill Handle tool.

• You can see the complete Repeat column.

Method 3 – Inserting VBA Code to Repeat Rows for a Specified Number of Times in Excel

Steps

• Go to the Developer tab and select Visual Basic.

• A VBA Editor window will appear.
• From the Insert tab, select Module.

• Insert the following code in the Module.

Sub Repeat_Data()
Dim x_range As Range
Dim in_range As Range, Outx_range As Range
xTitleId = "Repeat Rows a specified number of times"
Set in_range = Application.Selection
Set in_range = Application.InputBox(" select Range :", _
xTitleId, in_range.Address, Type:=8)
Set Outx_range = Application.InputBox("location (single cell):", _
xTitleId, Type:=8)
Set Outx_range = Outx_range.Range("A1")
For Each x_range In in_range.Rows
xValue = x_range.Range("A1").Value
xNum = x_range.Range("B1").Value
Outx_range.Resize(xNum, 1).Value = xValue
Set Outx_range = Outx_range.Offset(xNum, 0)
Next
End Sub

• Save the code and return to the Worksheet.
• Go to the Developer tab and select Macros.

• Select the Sub Repeat_Data and click on Run.

• A prompt box appears where you have to input the range ($B$5:$B$10).
• Click OK to continue.

• Another prompt box appears. Select a cell where you want to show your output. We selected cell E5.
• Click OK to continue.

• We have got our specified number of repeated rows.

Method 4 – Combining IF, ISBLANK, INDIRECT, ROW, ROWS, and COUNTA Functions

Steps

• Put the items in the Item sheet.

• In the Repeat sheet, we want these items to be repeated in the empty cells of the following created table.

• Use the following formula in cell B11.

• Press Enter.
• You can see the result in cell B11.
• Drag the formula to the right through the Fill Handle tool.

• Drag down the formula with the Fill Handle tool.

• You can see the repeated rows.

Read More: How to Make a Pattern Repeat in Excel

Method 5 – Merging TRIM, TEXTJOIN, IFERROR, FILTERXML, REPT, SUBSTITUTE, and XLOOKUP Functions

In the following dataset, you can see the Item and Repeat Time columns.

Steps

• Use the following formula in cell E4.

• Press Enter.

• Add a Helper 2 column.
• Use the following formula in cell E7.

• Press Enter.

• Add a Repeated Items column D.
• Use the following formula in cell D5.

• Press Enter.
• You can see the complete Repeated Items column.

Practice Section

You can download the following Excel file and practice the explained methods.

Things to Remember

After getting the repeated rows you can easily copy-paste them into other places.

The VLOOKUP function always searches for lookup values from the leftmost top column to the right. This function never searches for the data on the left.

Download the Practice Workbook

Related Articles

• How to Repeat Rows at Top in Excel
• How to Repeat Rows at Top of Specific Pages in Excel
• How to Repeat Rows in Excel When Printing
• [Fixed] Repeat Last Action Not Working in Excel

<< Go Back to Repeat in Excel | Excel Cell Format | Learn Excel