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

While working in Excel we may need to repeat rows a specific number of times. This happens when you are making bills for products or keeping records. Excel has many features by which we can repeat rows a specified number of times. Today in this article, we will demonstrate 5 methods to repeat rows a specified number of times in Excel.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

5 Suitable Ways to Repeat Rows for a Specified Number of Times in Excel

Consider a situation where you are given a dataset containing columns Item, their Grade, and Stock. You have to repeat some of its rows to, make a bill. In this article, we’ll discuss 5 different ways to repeat rows a specified number of times. Here, we used Excel 365. You can use any available Excel version.

Read More: Filling a Certain Number of Rows in Excel Automatically (6 Methods)

1. Using Fill Feature to Repeat Rows for a Specified Number of Times in Excel

In this method, we will use the Fill Feature to repeat rows a specified number of times in Excel. We can use the Fill feature in two ways. Let’s discuss them.

1.1. Using Fill Handle Tool

One of the easiest ways to repeat rows a specified number of times is to use the Fill Handle tool.

Steps

• In the first place, select the whole row that you need to repeat a specified number of times.
• Afterward, hover over your mouse to the bottom right corner of the cell until you see the fill handle icon (+).
• Then, when you see the icon, stop moving your mouse and click and drag the icon to repeat rows.

• After dragging the specified number of cells, stop dragging and release the mouse. The rows are repeated perfectly!

Read More: How to Repeat Rows in Excel at Bottom (5 Easy Ways)

1.2. Employing Fill Feature from Ribbon

The Fill feature from the ribbon of Excel is also useful when you want to repeat the rows. Let’s see how it works!

Steps

• First of all, select the row that is under the row we want to repeat.
• Here, we want the cells B11:D11 to be repeated.
• Therefore, we selected a row under this cell by selecting cells B12:D12.
• Then, go to your Home Tab >> from the Editing group >> click on Fill.
• Furthermore, from the available options, click on Down.

• As a result, you can see the repeated row.

• In a similar way, we repeat rows a specified number of times.

Read More: How to AutoFill Formula When Inserting Rows in Excel (4 Methods)

2. Using VLOOKUP Function

In the following dataset, you can see the Item and Repeat Time columns. In the Repeat Time column, we mention the number of times we want the rows to be repeated. Next, we will use the VLOOKUP function to repeat rows a specified number of times.

Steps

• In the first place, create a new column named the Helper Column.
• Then, we put 1 in cell B5.

• After that, we will type the following formula in cell B6.

• Moreover, press ENTER.
• Therefore, you can see the result in cell B6.
• Furthermore, we will drag down the formula with the Fill Handle tool.

• Therefore, you can see the complete Helper Column.

•  After that, we make another column and name it Column 2.
• Then, we enter 1 to 15 in cells E5 to E20.

• Afterward, we add a column named Repeat.
• Then, we type the following formula in cell F5.

•  At this point, press ENTER.
• Therefore, you can see the result in cell F5.
• Moreover, we will drag down the formula with the Fill Handle tool.

• Hence, you can see the complete Repeat column.

Read More: How to Fill Down to Last Row with Data in Excel (3 Quick Methods)

Similar Readings

• How to Repeat Cell Values in Excel (6 Quick Methods)
• Repeat Formula in Excel for Whole Column (5 Easy Ways)
• How to Repeat Column Headings on Each Page in Excel (3 Ways)
• Select Column A as Titles to Repeat on Each Page
• How to Set Print Titles to Repeat in Excel (2 Examples)

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

In this method, we will insert a VBA code to repeat rows a specified number of times in Excel.

Steps

• In the first place, we will go to the Developer tab >> select Visual Basic.

• At this point, a VBA Editor window will appear.
• Then, from the Insert tab >> we will select Module.

• Then, we will type 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

• After that, we Save the code >> return to our Worksheet.
• Now, to run the code, we go to the Developer tab >> select Macros.

• Moreover, we select the Sub >> click on Run.

• At this point, a prompt box appears where you have to input the range ($B$5:$B$10).
• In addition, click OK to continue.

• Along with that, another prompt box appears >> select a cell where you want to show your output.
• Here, we selected cell E5.
• In addition, click OK to continue.

• As a result, we have got our specified number of repeated rows.

Read More: AutoFill Formula to Last Row with Excel VBA (5 Examples)

4. Combining IF, ISBLANK, INDIRECT, ROW, ROWS, and COUNTA Functions

Here, we will use the combination of IF, ISBLANK, INDIRECT, ROW, ROWS, and COUNTA functions to repeat rows a specified number of times in Excel.

Steps

• First of all, we put the items in the Item sheet.

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

• Furthermore, we will type the following formula in cell B11.

• At this point, press ENTER.
• Therefore, you can see the result in cell B11.
• Furthermore, we will drag the formula towards the right through the Fill Handle tool.

• In addition, we will drag down the formula with the Fill Handle tool.

•  As a result, you can see the number of repeated rows.

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

In the following dataset, you can see the item and Repeat Time columns. Here, we will use the TRIM, TEXTJOIN, IFERROR, FILTERXML, REPT, ROW, SUBSTITUTE, and XLOOKUP functions to repeat rows a specified number of times in Excel.

Steps

• First, we will type the following formula in cell E4.

•  After that, press ENTER.
• Therefore, you can see the result in cell E4.

• Then, we will add a Helper 2 column.
• Furthermore, we will type the following formula in cell E7.

• Afterward, press ENTER.
• Therefore, you can see the complete Helper 2 column.

• Next, we add a Repeated Items column.
• Then, we type the following formula in cell D5.

• Next, press ENTER.
• Therefore, 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.

Conclusion

Here, we show you 5 easy examples to repeat rows a specified number of times in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section. You can visit our website Exceldemy for more related articles.

Related Articles

• How to Count Repeated Words in Excel (11 Methods)
• Find Repeated Cells in Excel (4 Easy Ways)
• How to Repeat Rows in Excel When Printing (3 Effective Ways)
• [Fixed!] Excel Rows to Repeat at Top Feature Greyed Out
• How to Repeat Multiple Rows in Excel (4 Effective Ways)
• Repeat Formula in Every nth Row in Excel (2 Easy Ways)
• How to Repeat Cell Value X Times in Excel (6 Easy Methods)