How to easily insert multiple rows to expand a table in Excel

2 minute read time.

Inserting multiple rows in a table can be very time consuming.  In this Excel tip, rows will be inserted based on the specified number in a column. This will be accomplished using Power Query.

Download the workbook to practice.

In the example below, we require additional rows for each client based on the number in the Rows column.

Note: this tip 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.

  • Ensure your cursor is in a cell within the table. On the menu select Data and under the Get & Transform Data section select From Table/Range.

 

  • The Power Query editor opens with a preview of the data.

 

  • Create a new column containing a list for each client. Each client will start at 1 and increment by the number specified under the Rows column.  To create the list, select the Add Column menu item and Custom Column under the General section.

 

  • In the Custom Column dialog box, enter a name for the column, e.g. AddRows. In the Custom Column Formula section enter the formula ={1..[Rows]} 

{Curly bracket} is used in Power Query to create a list. The 1 is the first value and the two dots .. is to fill in the values between 1 and the number in the specified column, which in our example is the [Rows] field.

 

  • Select OK, and the new column “AddRows” is added with a List data type.

  • Select the Rows column and on the Home menu select Remove Columns from the Manage Columns section as the column is no longer needed.




  • Select the AddRows column. From the menu select Transform and from the Structure Column section select Expand. Alternatively, select the drop down icon in the upper-right corner of the column label and select Expand to New Rows.

      

  • Power Query expands the table by inserting the specified number of rows

 

  • To return the results to Excel select Close & Load or select the drop down at the bottom right corner of Close & Load, and select Close & Load To ….

          

  • Select Table and any other selections as required. Click OK.


    

  • A new table is add to Excel with the required number of rows added. The AddRows column is no longer required and can be deleted.

N.B.  Additional data can be added in seconds. If additional data is added to the Source table, right-click the Results table, select Refresh and the table is updated instantly with the new data and required rows.