Working in Excel may require **repeating rows a certain number of times.** When you make product invoices or keep records, this happens. 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.

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

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

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

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.

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

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

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

**Read More:** **How to Repeat Cell Values in Excel (6 Quick Methods)**

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

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

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

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

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

**Similar Readings**

**How to Repeat Rows in Excel at Bottom (5 Easy Ways)****How to Count Repeated Words in Excel (11 Methods)****[Fixed!] Excel Rows to Repeat at Top Feature Greyed Out****How to Select Column A as Titles to Repeat on Each Page****How to Set Print Titles to Repeat in Excel (2 Examples)**

### 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**.

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

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

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

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

- 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. Else 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. Else 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 (7 Easy Ways)**

### 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’**.

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

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

**Read More:** **How to Repeat Cell Value X Times in Excel (6 Easy Methods)**

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

Don’t forget to check our website **Exceldemy.com** for various Excel-related problems and solutions. Keep learning new methods and keep growing!

**Related Articles**

**How to Repeat Rows in Excel When Printing (3 Effective Ways)****How to Repeat Multiple Rows in Excel (4 Effective Ways)****[Fixed!] Excel Rows to Repeat at Top Not Working (4 Solutions)****How to Repeat Formula in Every nth Row in Excel (2 Easy Ways)****Repeat Rows at Top of Specific Pages in Excel****How to Make a Pattern Repeat in Excel (8 Easy Ways)**