How to: Insert a blank row after every 3rd row

I just received the following question which might be interesting the share with you. ASAP Utilities contains many tools and one of the strengths is that you can often use a combination of the tools to accomplish what you need.

The question I received this week:
I want to insert a blank row after every 3rd row in an excel file containing 1000 rows. Can you tell how..
Sure I can!

Initially you might think of the tool in ASAP Utilities to insert empty rows between every row in your selection. But that does it for every row, not every 3rd. However, with a small workaround it can still be done quickly.
With the help of a temporary column we enter the numbers 1 till 3 down to the bottom.
Then we select all the cells with the value 1 and then we insert the empty rows and finally remove the temporary helper column.

How it works:

  1. This is an example of the initial dataset where we need to insert a blank row after every third row:initial-dataset
  2. Insert a temporary column and select the cells all the way down to the end.
  3. Then in the excel menu choose ASAP Utilities » Fill » Quick numbering of selected cells... and enter the information as presented in the screenshot below. The "restart counter after number" with the value 3 is what is handy here.
    temp-column-with-numbers
  4. When you press the OK button you will see that the cells are filled with 1,2,3 down to the end.
    Then we select cells B3 down to the bottom and we use ASAP Utilities to select all the cells with the value 1. In the Excel menu choose ASAP Utilities » Select » Select cells based on content, formatting and more... and on the "Based on content" tab choose to select the cells equal to 1:
    select-cells-with-value-1
  5. Now that these cells are selected we can use Excel to insert the empty rows:
    insert-rows
  6. The rows are inserted above every cell with the value 1 and then you can remove the temporary column:
    delete-the-temporary-column
  7. And this is the result:
    the-result

Food for thought.
The above approach shows how to insert an empty row after every 3rd row. You can easily use the same approach to insert it after every 4th, 5th, 25th etc., etc. or use it on columns. When you want to insert more then one row, for example 2 rows, you can use the conditional select to select the cells 'less then or equal to' 2. Then when you use the insert rows command in Excel two rows will be inserted.

And as always in Excel, this is just one approach to solve the problem.

Comments are closed.

Pinterest