You can repeat multiple rows any number of times using my REPT_ROWS custom Named Function in Google Sheets.

The number of repetitions can be the same for every row or different for each row.

For example, you can repeat the first row 5 times, the second row 2 times, the third row 15 times, and so on—or repeat all rows 10 times.

Previously, we used formulas to insert duplicate rows manually. Now, I’ve converted that process into a simple custom Named Function for ease of use.

You can import my REPT_ROWS function directly from the example Sheet linked below.

Get REPT_ROWS Named Function

Once you’ve copied the sheet, you can use the function there or import it into other Sheets. For detailed instructions on importing Named Functions, see How to Create Named Functions in Google Sheets.

Although the function will appear as a suggestion (formula help) when you start typing its name in a cell, it’s still helpful to follow the examples in this guide to use it effectively.

Syntax and Arguments of the REPT_ROWS Function

The REPT_ROWS() function has three arguments. I’ve kept it simple so it’s easy to use.

Syntax:

REPT_ROWS(range, rept_by_range, index)

Arguments:

• range: The range of rows to repeat (e.g., A2:Z10).
• rept_by_range: The column range specifying how many times each row should repeat (e.g., Z2:Z10).
• index: The relative positions of the output columns in the range, similar to the index argument in VLOOKUP.
  • Examples: 5 returns the fifth column, {1, 2, 5} returns columns 1, 2, and 5, and SEQUENCE(1, 10) returns columns 1 to 10.

Below are three practical examples showing how to use REPT_ROWS to repeat rows in Google Sheets.

Repeating Multiple Rows Using the REPT_ROWS Function in Google Sheets

We will use different data sets for each example. Let’s start with the basics.

1. Repeat Multiple Rows (Basic Form)

Suppose columns B (First Name) and C (Last Name) contain values, and column D specifies how many times each row should repeat.

Formula in F2:

=REPT_ROWS(B2:C4, D2:D4, {1, 2})

• B2:C4 → range
• D2:D4 → rept_by_range
• {1, 2} → index (columns to return)

Including Future Rows:

=REPT_ROWS(B2:C, D2:D, {1, 2})

This version automatically includes all present and future values in columns B to D.

2. Using Arithmetic Operations

Sometimes, the quantity (Qty) column itself can define the number of repetitions. You may also want to adjust another column, like dividing the amount by quantity.

Example: Repeat fruit names (Item) based on their quantity and adjust the Amount column:

=ArrayFormula(REPT_ROWS({B4:B6, D4:D6/C4:C6}, C4:C6, {1, 2}))

• range: {B4:B6, D4:D6/C4:C6} (Item and Amount per quantity)
• rept_by_range: C4:C6 (Qty column)
• index: {1, 2} (columns to return)

The division operator requires the use of ArrayFormula.

Related: How to Use Curly Brackets to Create Arrays in Google Sheets

3. Duplicate Rows and Increment Dates (Day, Month, or Year)

You can combine REPT_ROWS with a running count formula to increment dates after repeating rows.

Scenario: Two streaming subscriptions with start dates: 1-Jan-2022 and 25-Nov-2022. We want the full year of subscription dates by repeating each row 12 times.

Step 1 – Duplicate Rows B2:C3:

Enter this formula in cell G2:

=REPT_ROWS(B2:C3, D2:D3, {1, 2})

Step 2 – Duplicate Dates in A2:A3:

Enter this formula in cell F2:

=REPT_ROWS(A2:A3, D2:D3, 1)

If the values don’t display as dates, select the range and apply: Format > Number > Date.

Now we’ll adjust this formula based on whether we want to increment by month, day, or year.

Increment Dates by Month:

=ArrayFormula(EDATE(step_2_formula, running_count))

Where:

running_count : COUNTIFS(G2:G25, G2:G25, ROW(G2:G25), "<="&ROW(G2:G25))-1

• Here, EDATE adds the running count as the number of months, so each duplicate moves forward by one month.

Increment Dates by Day:

=ArrayFormula(step_2_formula + running_count)

• Adding the running count directly to the dates increments each duplicate by one day.

Increment Dates by Year:

=ArrayFormula(EDATE(step_2_formula, (running_count)*12))

• Multiplying the running count by 12 converts it into years (12 months per year), so each duplicate advances one year.

Resources

• Automatically Adjust Duplicate Dates in Google Sheets
• How to Duplicate Rows Based on Start and End Dates in Google Sheets
• Create All Combinations from Comma-Separated Columns in Google Sheets