Insert X Rows to Expand Table

I received a question from Sam, who asked how to insert a variable number of rows (X rows) into a table based on a column value. That is, by the number specified in a column. In this post, we’ll accomplish this request with Power Query.

Objective

Before we jump into Excel, let’s confirm our objective. Let’s say we have a table that contains a company name and the number of passes issued to each, like this:

Sam wants to have Excel insert rows based on the number of passes, like this:

We’ll use Power Query to accomplish this.

Video

Narrative

We’ll accomplish this by performing the following steps:

  1. Get source into Power Query
  2. Create a list
  3. Expand to new rows
  4. Load results into table

Note: this tutorial uses Power Query. You can quickly determine if your version of Excel includes Power Query by clicking the Data tab, and looking for the “Get & Transform Data” group of commands.

Time needed: 10 minutes

Let’s work through the details of each step.

  1. Get source into Power Query

    To get the source table into Power Query, select any cell in the table and click the Data > From Table/Range command.

    The data preview will appear in the Power Query editor:

  2. Create a list

    Next, we need to create a new column that contains a list for each company. Each list will start at 1 and end at the number of passes. To create such a list, we select Add Column > Custom Column.

    In the resulting Custom Column dialog, we enter a column name, such as List, and the formula ={1..[Passes]} as shown below:



    The {curly brackets} tell Power Query we are creating a list. The 1 will be the first value in each list. The two dots .. tell Power Query to fill in the values between 1 and the last value, which in our case is stored in the [Passes] field. If your data has a different field name, you would modify the formula accordingly.

    Once we hit OK, Power Query displays a new column with a List data type.

    We no longer need the original Passes column, so we select it and then use the Home > Remove Columns command to delete it.

  3. Expand to new rows

    Select the List column, and then click Transform > Expand. (Or, you can use the expand icon in the upper-right corner of the column label and select Expand to New Rows.)

    Power Query expands the table by inserting the desired number of rows:



    All that remains now is to send the results to an Excel table.

  4. Load results into table

    To send these results back to Excel, we click Home > Close & Load To …. Select Table and send to a new or existing sheet as desired. Click OK and … mission accomplished:

The nice part about this approach is that it is easy to refresh next period. For example, if we add a bunch of new rows in the source table, all we need to do is right-click the results table and select Refresh.

What do you think about this approach? Do you have any alternate ways to accomplish this or improvements to the query? Let me know by posting a comment below … thanks!

Sample file:

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.

10 Comments

  1. Glenn on July 16, 2020 at 9:36 am

    Thanks, Jeff, this was a really timely tip! I wanted to do something like this the other day, but wasn’t sure how, and didn’t have the time to look it up.

    • Jeff Lenning on July 16, 2020 at 9:52 am

      Excellent Glenn … glad to help!
      Thanks
      Jeff

  2. James on May 5, 2021 at 12:29 pm

    Great video Jeff! I want to do the same thing but instead I want to insert new rows inbetween dates. So instead of 1 to 2, I want to insert X new rows based on the number of days between months from Jan 1 2021 to end of month, and then Feb 1 to end of month, Mar 1 to end of month and so on.

  3. Kurt LeBlanc on May 24, 2021 at 3:27 pm

    Mr. Jeff,

    How much storage does a Power Query take up when you pull the data from another file?

    • Jeff Lenning on May 26, 2021 at 8:00 am

      Hi Kurt!
      The file size will increase based on the data the query returns, but, there isn’t much overhead related to the m-code of the query itself.
      Thanks
      Jeff

      • Kurt on May 26, 2021 at 8:40 am

        ok thanks

  4. Eddy on June 27, 2022 at 9:25 pm

    You’re the Man! Saved me lot of time!

  5. Amandine on January 13, 2023 at 1:34 pm

    Thank you very much,
    Thanks to your explanation above I could easily convert a cell containing “x to y” to y-x lines containing all the numbers between x and y

Leave a Comment