Insert Repeating Items into a List

This post will demonstrate how to insert the same few items into a list and create a new row for each item (or each combination, if multiple items).  For example, let’s say we have a list of some sort … we’ll use a list of T-Shirts for this illustration. We have a few T-Shirt options that we want repeated for each T-shirt in our list. For example, each T-shirt has the same size and color options. We’d like to expand the list of T-shirts and create a new row for each combination of color and size. I’ll demonstrate how we can use Power Query to accomplish this task.



Before we get to the detailed steps, let’s quickly confirm our objective.

We have a list of T-shirts, like this:

We want to expand the list so that each T-shirt gets a new row for each size option (small, medium, large) and a new row for each color option (blue, black, white, red). The resulting list needs to have a new row for each combination of size and color, like this:

In the old days, this took quite a bit of copy/paste, or perhaps a clever macro. Nowadays, it is just a couple of clicks in Power Query.

Let’s get to it.


We’ll accomplish our objective with these steps:

  • Get list into Power Query
  • Create size and color columns
  • Split columns into rows

First up, getting the T-Shirt list into Power Query.

Get list into Power Query

To load our list into Power Query, we select any cell in the table and use the Data > From Table/Range command.

This will load our T-Shirt table into Power Query, and we should see something like this in the Power Query Editor:

Done. Time to create the size and color columns.

Create size and color columns

Our goal in this step is to create one column for each option list. Here, we have two options (size and color), so we’ll create two columns. Each column will contain a delimited list of values for example, small, medium, large. The end game is that we will ultimately split the delimited lists into new rows. So at this point, we just need to get the delimited lists into each row.

There are several ways to create the Size and Color columns. In this example, we’ll use Custom Columns. From within Power Query select the Add Column > Custom Column command. In the resulting Custom Column dialog, we enter the new column name as Size and the formula as = “Small,Medium,Large” (no spaces around the commas) as shown below:

We hit OK and now the size values are repeated in each row:

Now, we need to create the Color column. So, again, we use the Add Column > Custom Column command, and enter the corresponding formula:

Hit OK and so far so good:

Now that we have the desired option values in new columns, it is time to split these values into rows in order to create one new row for each combination of size and color.

Split columns into rows

First, we select the column we’d like to split. In this case, we’ll start with the Size column:

We select the Transform > Text Column > Split Column > By Delimiter command. In the resulting dialog, we identify the delimiter (in our case, a comma) and we want to Split at Each occurrence of the delimiter. Then we click Advanced options to reveal the Split into Rows option:

We click OK and confirm that there is one new row for each Size:

We repeat these steps for the Color column and bam:

We are looking good, so we just click Home > Close & Load To and send the results back into an Excel table:

The results are displayed in Excel:

Now, the beautiful thing about Power Query is that next period, when we have additional T-Shirts in our products table, all we need to do is right-click the results table and hit Refresh. And, if we have any additional colors or sizes, we can simply open up the query and modify our custom column formula (by clicking the gear icon) to include them. When we Close & Load, the updated values will flow into the results table!


If you have any other ways to accomplish this or suggestions on how this approach can be improved, please share by posting a comment below … thanks!

Sample File: RepeatOptions.xlsx

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.


  1. Jean-Sébastien Quesnel on December 4, 2019 at 12:24 pm

    So brilliant, once again! Thank you.

    • Jeff Lenning on December 4, 2019 at 12:25 pm

      Thank you 🙂

  2. Don Steele on December 4, 2019 at 8:23 pm

    That’s a good technique, Jeff. Beleive it or not, this can be done with formulas. Here is aink to my article on how to do it for three variables.

  3. Amirreza on August 29, 2020 at 5:52 pm

    Is it possible to use lists for reapting items? And after updating each list, the result updated too?

  4. Tasneem on February 25, 2021 at 4:33 pm

    How about if I want to add sub-prod ID also? How can that be done easily?

  5. Tasneem on February 25, 2021 at 4:35 pm

    Any way to create and update sub-prod IDs also?

  6. Mike Radford on August 10, 2021 at 9:53 am

    I need to keep track of 30 different blades that I have on site that will be sent away for sharpening. They are all individually marked with a serial number. These blades will cycle through. At any one time, I would like to know where they are. I.e are they in transit TO the place of recycling, are they with them to be re-sharpened, are they on the way back FROM the sharpeners, are they on the shelf to be used, or are they in use on our site. I understand I need to put measures in place to alert me of these details to manually enter them but I am unsure of what is the bet way to lay the table out. Any help greatly appreicated.

Leave a Comment