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.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

Want to learn Excel?

Our Campus Pass includes access to our entire Undergrad and Masters catalog. Gamification ensures it is the most fun you can have learning Excel :)

2 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

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.