How to Repeat Rows in Excel Based on Cell Value (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

It is possible to repeat rows in Excel based on cell value through the use of many features. If you are looking for some special tricks to repeat rows based on cell value in Excel, you’ve come to the right place. There are four ways to repeat rows based on cell value in Excel. This article will discuss every step of these methods to repeat rows based on cell values in Excel. Let’s follow the complete guide to learn all of this.


How to Repeat Rows in Excel Based on Cell Value: 4 Easy Methods

In the following section, we will use four effective and tricky methods to repeat rows based on cell values in Excel. Here, we use four effective methods: VBA code, using the VLOOKUP function, applying the IF function, and finally copying and pasting the cells. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Embedding VBA Code

Working in Excel may require repeating rows a specified number of times. When you make product invoices or keep records, this happens. If you want to repeat rows in Excel based on cell value, you need to use the help of VBA. Microsoft Visual Basic for Applications (VBA) is Microsoft’s Event Driven Programming Language.

To use this feature you first need to have the Developer tab showing on your ribbon. Click here to see how you can show the Developer tab on your ribbon. Once you have that, follow these detailed steps to repeat rows in Excel based on cell value.

📌 Steps:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developers tab on your ribbon. Then select Visual Basic from the Code group.

Embedding VBA Code to Repeat Rows in Excel Based on Cell Value

  • VBA modules hold the code in the Visual Basic Editor. It has a .bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

insert module

  • As a result, a new module will be created.
  • Now select, the module if it isn’t already selected. Then write down the following code in it.
Sub RepeatData()
'Repeat Rows
Dim use_range As Range
Dim input_range As Range, output_range As Range
xTitleId = "Repeat Rows in Excel"
Set input_range = Application.Selection
Set input_range = Application.InputBox("Range :", xTitleId, input_range.Address, Type:=8)
Set output_range = Application.InputBox("Output to (single cell):", xTitleId, Type:=8)
Set output_range = output_range.Range("A1")
For Each use_range In input_range.Rows
            y_value = use_range.Range("A1").Value
            w_num = use_range.Range("B1").Value
    output_range.Resize(w_num, 1).Value = y_value
            Set output_range = output_range.Offset(w_num, 0)
Next
End Sub
  • After that, you can close the VBA window.
  • Then go to the Developer tab again. This time, select Macros from the Code group. Now select the Macro name you have just entered. Our macro name was RepeatData. So we have selected that.
  • After that, click on Run.

run macro

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

  • Select a cell where you want to show your output ($E$5). Click OK to continue.

  • We have got repeated rows based on cell values in Excel.

Repeat Rows in Excel Based on Cell Value

Read More: How to Repeat Multiple Rows in Excel


2. Applying VLOOKUP function

Here we will demonstrate how to repeat rows in Excel based on cell values using the VLOOKUP function. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. The following dataset shows different person’s names and their repeat times.

Applying VLOOKUP function to Repeat Rows in Excel Based on Cell Value

Let’s walk through the following steps to repeat rows in Excel based on cell value.

📌 Steps:

  • First of all, create three new columns named Column 1, Column 2, and Repeat.
  • In the Repeat Time column, you mention the number of times you want the rows to be repeated.
  • In Column 1, we will add a formula for the VLOOKUP function to use.
  • In the B6 cell of Column 1, insert the following formula.

=B5+D5

  • Then, press Enter.

perform sum in cell

  • Next, repeat the same formula to the end of the cells.
  • Therefore, you will get Column 1 as shown below.

get the Column 1 value

  • Then, make another column and name it Column 2.
  • Enter 1 in E5 of Column 2 and fill the number by using the Fill Handle feature to 13 which is the total number of times mentioned in Column 2.
  • Then, insert a new column named Repeat. In cell F5 of the Repeat, apply the VLOOKUP After inserting the values into the function, the final form is,

=VLOOKUP(E5,$B$5:$C$9,2)

  • Here lookup_value is E5, lookup_array is $B$5:$E$9 and col_Index_num is 2.
  • Then, press Enter.

get the Repeat of first Row in Excel Based on Cell Value

  • Now apply the same formula to the rest of the cells. The Rows are repeated based on cell value as shown below.

Repeat Rows in Excel Based on Cell Value by using VLOOKUP Function

Read More: How to Repeat Cell Value X Times in Excel


3. Using IF Function

Here we will demonstrate how to repeat rows in Excel based on cell values using the IF function. The following dataset shows different person’s names and their repeat times.

Let’s walk through the following steps to repeat rows in Excel based on cell value.

📌 Steps:

  • First of all, you have to use the following formula to repeat rows based on cell value.

=IF(D5<$D$8,$B$5,"")

  • Then, press Enter.

Using IF Function to Repeat Rows in Excel Based on Cell Value

  • Next, drag the Fill Handle icon to fill other cells with the formula.
  • Therefore, you will be able to repeat cell C5 three times in the Repeat column.

  • Next, you have to use the following formula.

=IF(D8< $D$10,$B$6,"")

  • Then, press Enter.

apply if function for cell E8

  • Next, drag the Fill Handle icon to fill other cells with the formula.
  • Therefore, you will be able to repeat cell C6 three times in the Repeat column.

  • Next, you have to use the following formula.

=IF(D10<$D$14,$B$7,"")

  • Then, press Enter.

apply if function for cell E9

  • Next, drag the Fill Handle icon to fill other cells with the formula.
  • Therefore, you will be able to repeat cell C7 three times in the Repeat column.

  • Next, you have to use the following formula.

=IF(D14<$D$16,$B$8,"")

  • Then, press Enter.

apply if function for cell E10

  • Next, drag the Fill Handle icon to fill other cells with the formula.
  • Therefore, you will be able to repeat cell C8 three times in the Repeat column.

  • Next, you have to use the following formula.

=IF(D16<=$D$17,$B$9,"")

  • Then, press Enter.

apply if function for cell E12

  • Next, drag the Fill Handle icon to fill other cells with the formula.
  • Therefore, you will be able to repeat cell C9 three times in the Repeat column.

🔎 How Does the Formula Work?

  • IF(D5<$D$8,$B$5,””)

This formula will check whether the value of cell D5 is within cell D8 or not. If the condition is met, the function will return the cell B5 value. Otherwise, it moves to the next cell and returns a blank cell. Therefore, you will be able to repeat cell B5 three times in the Repeat column.

  • IF(D8< $D$10,$B$6,””)

This formula will check whether the value of cell D8 is within cell D10 or not. If the condition is met, the function will return the cell B6 value. Otherwise, it moves to the next cell and returns a blank cell. Therefore, you will be able to repeat cell B6 three times in the Repeat column.

  • IF(D10<$D$14,$B$7,””)

This formula will check whether the value of cell D10 is within cell D14 or not. If the condition is met, the function will return the cell B7 value. Else it moves to the next cell and returns a blank cell. Therefore, you will be able to repeat cell B7 three times in the Repeat column using this formula.

  • IF(D14<$D$16,$B$8,””)

This formula will check whether the value of cell D14 is within cell D16 or not. If the condition is met, the function will return the cell B8 value. Else it moves to the next cell and returns a blank cell. Therefore, you will be able to repeat cell B8 three times in the Repeat column using this formula.

  • IF(D16<=$D$17,$B$9,””)

This formula will check whether the value of cell D16 is within cell D17 or not. If the condition is met, the function will return the cell B9 value. Else it moves to the next cell and returns a blank cell. Therefore, you will be able to repeat cell B9 three times in the Repeat column using this formula.

Read More: How to Repeat Formula for Each Row in Excel


4. Copying and Pasting Cells

Here we will demonstrate another tricky and quickest method to repeat rows in Excel based on cell values by copying and pasting. The following dataset shows different person’s names and their repeat times.

Let’s walk through the following steps to repeat rows in Excel based on cell value.

📌 Steps:

  • First of all, copy cell C5 and select the range of cells E5:E7, and press ‘Ctrl+V’.

Copying and Pasting Cells to Repeat Rows in Excel Based on Cell Value

  • Therefore, you will see that cell C5 has been repeated three times in the Repeat column.

pasting value to Repeat Rows in Excel Based on Cell Value

  • Follow the above process for other cells.
  • As a consequence, we have repeated rows based on cell values in Excel.

 Repeat Rows in Excel Based on Cell Value by pasting value

Read More: How to Repeat Cell Values in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.


Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to repeat rows based on cell values in Excel. If you have any queries or recommendations, please share them in the comments section below.

Keep learning new methods and keep growing!


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.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo