How to Insert Duplicate Rows in Google Sheets: A Step-by-Step Guide

To insert duplicate rows in Google Sheets, we can use a complex-looking VLOOKUP function. However, the formula is not that complex if you follow this tutorial. I have one dynamic formula that you can use to copy a row or rows multiple times.

I know that some of you may be wondering why such a formula is required. Normally, we look for ways to remove duplicates, not generate duplicates in Google Sheets. However, by making duplicates, we are not simply repeating the content of one row in another. It has a purpose.

Here is one real-life example:

Sample Dataset (Click the button below to obtain it):

Sample_Sheet

Sample data to insert duplicate rows in Google Sheets

This sample data shows the sales of my e-books and paperbacks, which are downloads via different online channels. Let’s take a closer look at the data.

In row 2, as you can see, two books have been sold. Column F shows the number of units sold per day. I want to duplicate this row if the units sold are two so that I have the same row twice.

In row 3, the number of books sold is four. So I want this row to be repeated four times. I’m expecting a result like the one below.

Example of duplicate rows inserted using a Formula in Google Sheets

What is the advantage of this type of report?

You can generate more detailed pivot tables and data grouping with such an output.

How do I duplicate rows like the example above, I mean how to insert duplicate rows in Google Sheets based on specific input? I’ll get into that.

Earlier, I gave several tips on how to remove duplicate rows in Google Sheets. But this time, I’m detailing how to populate duplicate rows.

Easy Steps to Insert Duplicate Rows in Google Sheets

As I mentioned, I’m going to use VLOOKUP, the most popular spreadsheet function of all time, for this purpose. It’s an array-based use of VLOOKUP.

The formula to insert duplicate rows in Google Sheets:

=ARRAYFORMULA(
     VLOOKUP(
        TRANSPOSE(SPLIT(QUERY(REPT(ROW(A2:A)&" ",F2:F),,9^9)," ")),
        {ROW(A2:A),A2:E},
        {1,2,3,4,5}+1,0
     )
)

In the above example, I’ve applied this formula in cell I2. Here are two important factors to understand about this formula so that you can use it for your purposes:

1. How to adjust this formula to your data range:

The data range in the sample data is A2:F. To adjust the formula to your data range, you need to specify the first and last columns in your data range (A2:A and F2:F here), and the actual data range to repeat (A2:E here). You also need to specify the columns you want in your result (like {1,2,3,4,5}).

2. Formula explanation:

This formula is a bit complex, but it works by creating a virtual sequence index for each row in your data range. This virtual sequence index is then used to look up the corresponding values in columns B through E. The results of these lookups are then returned in an array, which is then displayed in the cell (across and down) where the formula is entered.

I’ll explain both of these factors to you in more detail so that you can learn how to insert duplicate rows in your Google Sheets.

How to Customize the Duplicate Row Formula in Google Sheets

In the above formula, you only need to modify the cell references:

A2:A (first column)
F2:F (column that determines the number of times to repeat rows)
A2:E (range to repeat)

My original data range is A2:F (6 columns), excluding the title row.

The formula inserts duplicate rows in Google Sheets based on column F.

If you’re not very familiar with modifying formulas, do one thing: keep the last column as your column that contains the number of times to repeat each row. That’s the column F2:F in my formula. If you exclude this column, the remaining data range is A2:E. These two are the cell references you should take note of. Replace A2:A with the first column range.

If you have more columns in your data, make the following changes to the formula:

If your data range is A2:G (7 columns), change A2:E to A2:F and F2:F to G2:G assuming G2:G is the range that determines the number of times to repeat rows.
Change the index number array from {1,2,3,4,5} to {1,2,3,4,5,6}.

You can now modify the above formula on your own, depending on the number of columns in your data.

How This Formula Populates Duplicate Rows in Google Sheets

The formula is a VLOOKUP function in array use.

VLOOKUP Syntax:

VLOOKUP(search_key, range, index, [is_sorted])

In the formula, the search_key, range, and index are not mere cell references. They are different combinations of formulas or arrays.

You can see that one by one.

1. Search_Key

VLOOKUP search key in the above formula that inserts duplicate rows:

I have used this formula (bolded) as the search key in VLOOKUP:

=ARRAYFORMULA(TRANSPOSE(SPLIT(QUERY(REPT(ROW(A2:A)&" ",F2:F),,9^9)," ")))

In the Master Formula, you can see this formula without the opening ArrayFormula. I have wrapped this formula with the ArrayFormula because it’s required when you are using this formula independently. In the main formula, there’s already an ArrayFormula in the beginning. That takes care of the other formulas nested in it. This formula returns the following result.

Repeating row numbers based on n (provided number) in Google Sheets

More Details About the Search_Key:

The first item in my above data range is in row 2. Its “Unit Sold” is 2, so the formula repeats row 2 twice.
The second item’s “Unit Sold” is 4, and it’s in row 3. So the formula repeats row 3 four times.
Similarly, rows 4 and 5 are repeated one time each.

Formula Breakdown

I can split this formula into two parts for explanation purposes. This will help you learn how the formula generates the search keys.

Part 1:

=ARRAYFORMULA(REPT(ROW(A2:A)&" ",F2:F))

Result:

3333

This formula uses the REPT and ROW functions to repeat the row numbers n times, where n is the value in column F.

Part 2:

=ARRAYFORMULA(QUERY(REPT(ROW(A2:A)&" ",F2:F),,9^9))

Result:

“2 2 3 3 3 3 4 5”

This formula uses the QUERY function to combine the values in one column into one row, as shown above.

Rest of the Formula

The rest of the formula first splits and then transposes the output of the second part to create the search_key for VLOOKUP. (Please scroll up to see the image.)

If you want to learn how to use the Query function to combine columns, please follow the below tutorial: The Flexible Array Formula to Join Columns in Google Sheets.

2. Range

VLOOKUP range in the formula that inserts duplicate rows:

Now I am going to explain the “range” in VLOOKUP. The range is the original data range.

Since we have row numbers as search keys, to look up these search keys in the range, I have added a column to the range. This added column contains the actual row numbers.

=ARRAYFORMULA({ROW(A2:A),A2:E})

Result:

Adding a virtual row number column with the range to insert duplicate rows using VLOOKUP

I hope you can now guess how the formula works. We have already generated the search keys, which repeat the row numbers twice if the “Unit Sold” is 2, three times if the “Unit Sold” is 3, and so on. So the VLOOKUP will look up those search keys in the first column of this “range”.

Since the row number (row #2) is two times in the search key, the formula will look up the number 2 in the first column in the “range” and return the content of the matching row number two times. The formula will return row #3 four times as it repeats 4 times in the search key, and finally rows #4 and 5 one time each.

3. Index

VLOOKUP index number in the formula that inserts duplicate rows:

{1,2,3,4,5}+1

Explanation:

VLOOKUP can return multiple columns or entire rows as output by specifying the column indexes separated by commas and enclosed in curly braces. For example, {1,2,3,4,5} will return the values in columns A, B, C, D, and E, respectively.

In the formula I provided, the +1 is necessary because the first column of the range contains the row numbers. This means that the column indexes need to be shifted by one to match the actual column positions of the data that we want to return.

Conclusion

Everyone wants to find solutions to remove duplicates, but sometimes we need to insert duplicates to get the desired result. This detailed tutorial shows you how to do it in Google Sheets.

You can also use my custom-named function, REPT_ROWS, to insert duplicate rows if you’re not comfortable writing complex formulas.

Resources:

42 COMMENTS

Axe June 13, 2023 At 5:39 am

Hi, Thanks for this.

I have a similar request where there is an extra column called ID, and that cell has comma-separated values.

I need to create duplicate rows for each value in that cell.

- Prashanth June 14, 2023 At 9:10 am
  
  Hi, Axe,
  
  Thanks for the screenshot. Please see the “User Requested” tab on my sample sheet for a workaround solution.
  
  - Axe June 27, 2023 At 4:54 am
    
    Thank you, Prashanth. This is very helpful. I’ll look into this further and let you know if I have any questions.
    
    - Axe July 6, 2023 At 11:51 pm
      
      As a follow-up, how to include rows which are missing data in the ID cell of that row? Currently, it is ignored and the whole row is missing from the transformed data as expected, but I would like to have the option to include the row as is in such cases. Please suggest. Thanks.
      
      - Prashanth July 7, 2023 At 12:11 pm
        
        Please check. I have updated the formula in my sample sheet in the “User Requested” tab.
Dobbo June 9, 2022 At 10:14 pm

Excellent, just what I was after to help create duplicate lines for a list.

David March 30, 2022 At 8:53 pm

Hi Prashanth,

I’ve been using your tutorials for a while now.

I hugely appreciate your breakdown and explanation of how each step works; incredibly clear and concise.

I have a similar problem.

I’ve provided an example sheet with example data and my intended results. Any help on this would be hugely appreciated.

— URL removed by Admin —

- Prashanth March 31, 2022 At 11:39 am
  
  Hi, David,
  
  I’ve added my solution. Please check cell J2.
  
  For the steps, please check the relevant tab in your sheet.
  
  - David April 1, 2022 At 3:40 pm
    
    Hi Prashanth,
    
    This is a very elegant solution. Thank you for your explanation steps. This is a huge help 🙂
    
Sneha November 28, 2021 At 7:45 pm

Hi Prashanth,

I have been learning a lot from your tutorials! Thank you so much for all these brilliant formulae!

I need a little more help. Please refer to the sample sheet that I have attached here,

— Sample Sheet’s link removed by admin —

Duplicating rows based on column E, which is the count of comma-separated values in column D, is achieved by using your original MASTER formula.

However, I would like to print the individual comma-separated values from column D in the output.

I tried modifying your formula to get the desired output but couldn’t succeed.

Thanks in advance!

- Prashanth November 29, 2021 At 8:45 am
  
  Hi, Sneha,
  
  You can do that with just two steps.
  
  Your data range is A4:E, and you want to duplicate this data based on the number in column E.
  
  1. Use only the range A4:C and E4:E in the formula.
  2. Split and transpose the range D4:D and combine it with step 1 result.
  
  Here it is.
  
  =ArrayFormula( {vlookup(transpose(split(query(rept(row(A4:C)&" ",E4:E),,9^9)," ")), {row(A4:A),A4:C},{2,3,4},0), transpose(split(textjoin(",",false,D4:D),","))})
  
  - Sneha November 29, 2021 At 9:46 am
    
    Thank you so much for taking the time to help me out, Prashanth! The formula works!
    
Shyamal DLR July 28, 2021 At 1:06 pm

I have sets of rows to prepare a Gantt chart in google sheets. I want to duplicate/populate these sets of rows x number of times. How can I do it?

—link removed by the admin—

- Prashanth July 29, 2021 At 9:33 am
  
  Hi, Shyamal DLR,
  
  It’s a template. So please ask the developer for help.
  You May Like: Create a Gantt Chart Using Sparkline in Google Sheets.
  
Charles K. April 5, 2021 At 8:23 pm

Hi Prashanth,

You are a genius. Thanks for this amazing formula.

Need a little help, is it possible to have the duplicate rows with incremental dates of the following months on the exact same day. For example for the 24/04/2018 purchase which was to be duplicated 4 times I want to display the duplicates with dates for 24/05/2018, 24/06/2018, 24/07/2018, and so forth.

Would appreciate your help.

- Prashanth April 7, 2021 At 12:11 pm
  
  Hi, Charles K.,
  
  That we can do with a helper column (I can do that without a helper column too, but the formula may not be readable).
  
  I have edited my tutorial to include an example sheet. Please see that below the subtitle “Conclusion” at the end.
  
  That tab name in the sheet is “Copy of Example”.
  
  - Charles K. April 8, 2021 At 2:10 am
    
    Thanks, Prashanth for your prompt feedback.
    
    Have seen the helper columns aids to have the date increment by days. Is it possible to have the increments in months instead of day?
    
    Many thanks in advance.
    
    - Prashanth April 8, 2021 At 7:29 am
      
      Hi, Charles K.,
      
      Yep! It’s also doable. I have used the EDATE function for that. Please see the third tab in the shared sheet.
      
      I’ll explain how I have achieved the same in a coming post.
      
      - Charles K. April 9, 2021 At 12:38 am
        
        Prashanth you are a lifesaver.
        
        Thanks a lot, this has solved a major issue I was addressing on my sheet.
        
        Looking forward to the coming post to learn more.
      - Prashanth May 3, 2021 At 4:57 pm
        
        Hi, Charles K.
        
        Here it is – Incrementing Duplicate Dates by Month or Day in Google Sheets.
Christopher March 22, 2021 At 11:42 pm

Hi Prashanth,

I have a colleague using the formula you provided and we’re struggling to make a modification.

We are duplicating 3 columns (working fine), and are wanting to change to the 2nd column to be the iteration of the array.

For instance, if the row is to be repeated 4 times, then the repeated 2nd columns would contain values such as 4, 3, 2, 1.

We will then Concat to something like GS4, GS3, GS2, GS1.

Any help would be appreciated.

- Prashanth March 23, 2021 At 1:55 pm
  
  Hi, Christopher,
  
  Please use the “Reply” below to share an example Sheet that contains the sample and hand-entered expected result.
  
  So that I can try.
  
Rod September 8, 2020 At 11:05 pm

Hello,

I’m trying to see if it’s possible to add additional text to a cell after it is divided. For example, on column J, can it automatically name one “E-Book-A” and the 2nd one “E-Book-B”?

Thanks in advance.

- Prashanth September 9, 2020 At 1:39 pm
  
  Hi, Rod,
  
  You can do that separately using helper columns. Please find the related tutorial.
  
  Make Duplicates to Unique by Assigning Extra Characters in Google Sheets.
  
Trang May 12, 2020 At 9:48 am

Thank you for the amazing formula. I have a simpler problem, i.e. Fill “Quarter 2” 13 times, but I still wonder if the adapted formula that I’m using is the most optimal.

- Prashanth May 12, 2020 At 1:29 pm
  
  I could understand that you want the string “Quarter 2” repeated 13 times. Here is the formula.
  
  =ArrayFormula(if(sequence(13,1)<=13,"Quarter 2"))
  
  In this change 13 to 100 to repeat the string 100 times.
  
  Cheers!
  
  - Trang May 13, 2020 At 9:05 am
    
    Sweet. I didn’t know about the Sequence function. Thank you so much for taking the time to help me.
    
    - Prashanth May 13, 2020 At 11:52 am
      
      Hi, Trang.
      
      I am glad that I could help you!
      
Emily Rosche May 9, 2020 At 3:17 am

THIS FORMULA IS AMAZING! Thank you endlessly 😀

TIAGO FAGUNDES FONSECA September 6, 2019 At 7:36 pm

Hi Prashanth!

I did it, and it helped a lot, but I’m still having problems

I’ve to auto-create lines for each payment dates (“parcel dates”) based on a “payment type” and a “Starter date” …

My mainly difficult is: insert lines with edited dates + ifs + arrays + query (or vlookup) + transpose
How do I make all of this works togheter??

“Link removed – Admin”

In Brasil, we use dd/mm/yyyy

Could you give me a suggestion?

Thank you!

Eleanor August 22, 2019 At 2:30 am

Hi There, this might be a relatively simple question, but I can’t seem to get the formula to work for 2 columns (Column A is the unit I want to duplicate by the quantity of column B). Pretty simple but I can’t get it for the life of me!

=ArrayFormula(vlookup(transpose(split(query(rept(row(A1:A),(B1:B),,9^9),"")),ArrayFormula({row(A1:A))}),{2,3},0))

Thank you!!!!

- Prashanth August 22, 2019 At 4:43 pm
  
  Hi, Eleanor,
  
  See if this formula works for you?
  
  =ArrayFormula(vlookup(transpose(split(query(rept(row($A$1:$A)&" ",$B$1:$B),,9^9)," ")),{row($A$1:$A),$A$1:$A},2,0))
  
degobanan January 28, 2019 At 1:21 pm

I just took a first glance, and it seems absolutely perfect.

degobanan January 27, 2019 At 9:55 pm

I have a google-sheet here where I’ve tested this…

“https://docs.google.com/spreadsheets/d/1sp5DRBwFP0-aG-FvjUPKBmyylz0WoPB63ASOOdUGdnI/edit?usp=sharing”

The orange range is what I would like to achieve if using col C in the formula instead of col A.

The leftmost column in the orange output range contains the comma-separated values from using column C (which is what I want to do). Every row in the input is copied to output as many times as there are comma-separated numbers in column C for that row. And then the output is sorted on those numbers so that I can have the same row copied to multiple different positions in output.

The leftmost column in the output isn’t really something I’m interested in seeing in the final output. It’s really only used for sorting. I left it on the sheet to make it easier to see what’s going on.

- Prashanth January 28, 2019 At 9:13 am
  
  Hi,
  
  You can try this new formula.
  
  =Sort({transpose(split(textjoin(",",true,C2:C13),",")), vlookup(transpose(split(query(rept(row(D2:D)&" ", if(len(E2:E),len(SUBSTITUTE(C2:C,",","")),)),,9^9)," ")), ArrayFormula({row(D2:D),{if(len(E2:E),len(SUBSTITUTE(C2:C,",","")),), D2:F}}),{3,4,5},0)},1,1)
  
  Copy the below sheet where I have added the formula and the changes mentioned.
  
  Repeat Comma Separated Rows Sheets.
  
  Best,
  
joseph December 31, 2018 At 10:24 pm

This formula is almost exactly what I am looking for!

I am new to this and do not know how to change the output so that the output will be sent to a new sheet.

My use would be all raw data is on sheet1, and the data is copied to sheet two if column A has a value of “Yes”, N times based on a value in column B.

- Prashanth January 1, 2019 At 12:01 pm
  
  Hi, You can repeat the rows ‘n’ times based on ‘n’ values in column B and the values in column A is “Yes”.
  
  Example: Sample Data in the range A2:E in Sheet1.
  
  In that sample data, A2:A contains TRUE or FALSE, B2:B contains the number of times each row to repeat, C2:C contains few country names, D2:D contains player’s names and E2:E contains their age.
  
  Formula:
  
  =iferror(ArrayFormula(vlookup(transpose(split(query(rept(row(Sheet1!A2:A)&" ",Sheet1!B2:B),,9^9)," ")),filter({row(Sheet1!A2:A),Sheet1!A2:E},Sheet1!A2:A="Yes"),{2,3,4,5,6},0)))
  
  Best,
  
Katinka December 21, 2018 At 10:15 pm

Hey Prashanth,

I am using this formula for a very similar problem, but I have an extra that makes it a bit more complex. Instead of price per unit, I have the total price for all the units and I would like the price to be divided by the units in the new duplicate rows.
So for instance: 2 units sold, total price $10 => the result is 2 lines with $5 on each of them.
Is that something that is possible?

Thanks!

- Prashanth December 22, 2018 At 10:49 am
  
  Hi, Katinka,
  
  That seems simple!
  
  See this modified formula.
  
  =ArrayFormula(vlookup(transpose(split(query(rept(row(A2:A)&" ",F2:F),,9^9)," ")),ArrayFormula({row(A2:A),A2:D,E2:E/F2:F}),{2,3,4,5,6},0))
  
  Compare this with my master formula and you can see the minor changes I have made.
  
  Best,
  
  - Katinka December 26, 2018 At 8:59 pm
    
    Thank you Prashanth, that works perfectly!
    
Daniel Jean September 24, 2018 At 9:19 pm

Hi,

It seems that the code is outdated. Not Work.

Thank’s

- Prashanth September 24, 2018 At 9:40 pm
  
  Hi, Daniel,
  
  It’s still working. Here is the sheet.
  
  Repeat Rows