# How To Repeat A Range Of Items Multiple Times In Excel

In this tutorial I’ve created a formula that can retrieve a list of items, repeat that list, and automatically adjust if items are added to or deleted from that list.

Here I have a list of cities on a tab called “Cities”:

And on a tab called “Repeat” I’ve created a formula to copy that list of cities and keep repeating that list for as many rows as I copy the formula down:

The formula is:

=IF(ISBLANK(INDIRECT(“Cities!A”&ROW(A2))),INDIRECT(“Repeat!A”&(ROWS(\$A\$2:A2)-(COUNTA(Cities!A:A)-2))),Cities!A2)

It used the following functions:

IF
ISBLANK
INDIRECT
ROW
ROWS
COUNTA

Here is a breakdown of the formula:

In English, it is saying the following:

IF the cell referenced with the INDIRECT function (INDIRECT(“Cities!A”&ROW(A2)) ISBLANK, then run this formula: INDIRECT(“Repeat!A”&(ROWS(\$A\$2:A2)-(COUNTA(Cities!A:A)-2))). If it is not blank, then just give me what’s in that cell (Cities!A2).

So, the formula will copy what is in the cells on the “Cities” tab until it hits a blank, then it will run the second INDIRECT formula which will go to the top of the list of the “Cities” tab and return that result. As the formula is continued to be copied down, it will then give the next cell, and the next cell and so on.

If I remove two cities from the list:

My formula will automatically adjust the area where the formula is copied:

Take a look at the video and see the details of how the formula works. This might come in very handy when copying dynamic lists!

What can you do next?
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

### 10 Responses to How To Repeat A Range Of Items Multiple Times In Excel

1. Dominic Munton January 26, 2017 at 6:36 am #

Hi Michael,

Thanks so much for sharing this tutorial. It’s got me most of the way but I’ve run into a hitch. My scenario is the same as yours apart from the list in “cities” is being dynamically generated by a formula. Unfortunately your formula is also repeating all the “” entries from my dynamic list. Is there a way to get your formula to ignore blank entries?
Thanks!

• Michael Rempel January 26, 2017 at 5:31 pm #

Dominic, sorry I did not see your comment until today – don’t know why I didn’t get a notification. Anyhow, can you send me your file or a sample and I will gladly take a look at it. Send to mrempel@excel-bytes.com.

2. Nemos January 29, 2017 at 5:09 pm #

Great ! I tried for hours… and fortunately I found your solution!

3. firdaush June 13, 2017 at 3:26 pm #

Would you be able to help me with a problem?

I have 7 Items that I would like to repeat. However, 5 and 6 are relative formulas. My goal is to repeat items 1-4 and 7 but have 5 and 6 be a relative formula dependent on where it is in the repeating sequence. Is this possible?

• Michael Rempel June 13, 2017 at 4:29 pm #

This is just off the top of my head without testing the concept, but you can try wrapping the formula I used in another IF statement that would test for a formula using ISFORMULA. Then you can insert the formula and use a ROW function to adjust the cell reference to make it relative.

• firdaush June 13, 2017 at 4:31 pm #

I have actually gone in a different direction. Basically having a helper row that counts to 7 and repeats.
Then nested if statements like =if(A1=1,”BLAH). It seems to be a little easier and allows a little more freedom but i like this idea and will use it for another project.

Great website, long time user, first time commenter. Please never stop.

Have a great day.

4. brazilfootball September 20, 2017 at 3:03 pm #

Sorry, I lost you at the COUNTA explanation (min. 5:40). If my header is “Species” and I have 31 names under the header in column A, what would the formula look like?﻿

• Michael Rempel September 20, 2017 at 5:42 pm #

The INDIRECT function concatenates the sheet name (“Repeat!”) along with the column (“A”) to the appropriate row number. To get the appropriate row number, it uses the ROWS function to take the count from cell A2 to whatever cell that formula is at (for example ROWS(\$A\$2:A8) would equal 7) and subtract the number of cities on the cities tab of column A (COUNTA(Cities!A:A) which, for 5 cities would be 6 less 2 – one for the header plus one more which would be 4). So, let’s walk through the math. If I’m looking at the formula in A10, and there are 6 cities, I will want to copy what is in cell A4 (10-6=4). So to get to cell A4, it takes ROWS \$A\$2:A10 = 9 minus the number of cities and the header of 7 less 2 = 5. 9-5=4, so it will give me cell A4.

5. Jonathan November 15, 2017 at 12:21 pm #

This formula works great, I have an additional component that I need assistance with though. I have the repeating list of data in Column B (that I have created with your formula) but now I need to list dates in column A. The dates will repeat until it reaches the end of the other repeating list and then the next day will start. For Example:

11/15/2017 Data1
11/15/2017 Data2
11/15/2017 Data3
11/16/2017 Data1
11/16/2017 Data2
11/16/2017 Data3
11/17/2017 Data1
11/17/2017 Data2
11/17/2017 Data3

How can this be achieved?

• Michael Rempel November 15, 2017 at 4:25 pm #

Assuming the first row indicated is row 1, enter the first date in cell A1, then in cell A2, enter this formula: =IF(MOD(ROW(A2)-1,SUMPRODUCT(1/COUNTIF(\$B\$1:\$B\$9,\$B\$1:\$B\$9)))=0,A1+1,A1) and copy it down.