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 comment:

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