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.
=B5+D5
- 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.
=VLOOKUP(E5,$B$4:$C$10,2)
Formula Breakdown
- The VLOOKUP function looks up a certain value in an array based on specified criteria.
- VLOOKUP(E5,$B$4:$C$10,2) → becomes
- Output: Laptop
- Output: Laptop
- 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
Code Breakdown
- Here, we declare Repeat_Data as the Sub.
- We take x_range, in_range, and outx_range as Range.
- We used the FOR...NEXT loop to run the code until the last value is found.
- 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.
=IF(ISBLANK(INDIRECT("Item!B"&ROW(B5))),INDIRECT("Repeat!B"&(ROWS($B$5:B5)-(COUNTA(Item!B:B)-2))),Item!B5)
Formula Breakdown
- The IF function makes a logical comparison between a value we want and the given value.
- The ISBLANK function returns TRUE or FALSE after checking on a cell reference.
- The INDIRECT function finds out the reference that is specified by the text string.
- The ROW function finds the row number of a cell reference.
- The ROWS function determines the number of rows of a range of cells.
- The COUNTA function counts the number of nonempty cells.
- IF(ISBLANK(INDIRECT(“Item!B”&ROW(B5))),INDIRECT(“Repeat!B”&(ROWS($B$5:B5)-(COUNTA(Item!B:B)-2))),Item!B5) → becomes
- Output: Laptop
- 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.
=TRIM(TEXTJOIN(" ", TRUE, REPT(ROW(B5:B100)&" ", C5:C100)))
Formula Breakdown
- The ROW function finds the row number of a cell reference.
- The REPT function repeats the values a number of times.
- The TEXTJOIN function adds a number of text strings.
- The TRIM function trims the text string by removing extra spaces.
- TRIM(TEXTJOIN(” “,TRUE,REPT(ROW(B5:B100)&” “,C5:C100))) becomes
- Output: 5 5 6 6 7 7 7 8
- 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.
=IFERROR(FILTERXML( "<a><b>"&SUBSTITUTE(F4," ","</b><b>")&"</b></a>", "//b"),"")
Formula Breakdown
- The SUBSTITUTE function replaces the current text with another text string.
- The FILTERXML function returns certain XML data by using Xpath.
- The IFERROR function removes any error present in the formula.
- 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.
=XLOOKUP($F$7# ,ROW(B5:$B$100 ),B5:B100)
Formula Breakdown
- The ROW function finds the row number of a cell reference.
- The XLOOKUP function looks up an item in an array.
- 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)
Sorry but apart from the manual do it your self method the rest fails. At least on my laptop here in Sweden.
The VBA give this error Run-time error ‘1004’:
Application-defined error
Agree with STEEN
Hello STEEN and DAV,
I have checked the methods on several laptops. They are working well here.
For your convenience, I have added a new Excel file with the VBA code.
Please download it and check if the methods are working.
Thank you. Have a good day.
Mind Blowing, I’m surprised and this formula wawo