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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

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.

Dataset for Excel Repeat Rows a Specified Number of Times


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.

Applying Fill Handle Feature to Repeat Rows a Specified Number of Times in Excel

  • 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


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.

Using Fill Down Feature to Repeat Rows a Specified Number of Times in Excel

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

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

Read More: Repeat Text in Excel Automatically


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.

Use of VLOOKUP Function to Repeat Rows a Specified Number of Times in Excel

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
  •  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.

Excel Repeat Rows a Specified Number of Times


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.

Inserting VBA to Repeat Rows a Specified Number of Times in Excel

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

Inserting Module to Repeat Rows a Specified Number of Times in Excel

  • 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.

Running Code to Repeat Rows a Specified Number of Times in Excel

  • 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.


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.

Combining Functions to Repeat Rows a Specified Number of Times in Excel

  • 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.

Read More: How to Make a Pattern Repeat in Excel


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)))

Using TRIM, TEXTJOIN, ROW Functions to Repeat Rows a Specified Number of Times in Excel

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)

Aplying XLOOKUP Functions to Repeat Rows a Specified Number of Times in Excel

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.


Download Practice Workbook

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


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

4 Comments
  1. 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

  2. Agree with STEEN

  3. 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.

  4. Mind Blowing, I’m surprised and this formula wawo

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo