Sunday, December 27, 2009

Inserting Multiple Empty Rows in Excel

The other day, I was preparing a dashboard and I came across this problem:
I had a few rows of data and I had to insert multiple empty rows (5) in between each row. Initially I wrote a simple VBA macro to do it, but later a colleague showed me a much simple way to doing the same thing using Excel’s “Sort” function. Here’s how:

Step 1: Insert the data in continuous rows.

Step 2: Fill the adjacent rows with numbers starting with 1 going up in ascending order.

Step 3: Once you are done filling the rows with the numbers, copy the numbers and paste it immediately below for the number of times same as (the number of empty rows – 1) you want to insert in between. For example, in case you want to insert 5 empty rows insert the numbers as shown for 4 more times.

Step4: Next select all the filled up rows as shown in the image below and click on the sort button.

Step5: Select Column B (column containing the numbers) for “Sort by” in the sort dialogue box, select “Values” in “Sort on” and “Smallest to Largest” in “Order” in the sort dialogue box.

Step6: Click Ok button and you are done.

Hope this information helped


  1. would you mind updating your blog with extra information? It is extremely helpful for me.
    web agency Italy

  2. Domain hosting wiki: for bloggers, business owners and webmasters looking for starting, maintaining and knowing more about domains and hosting