Insert blank rows between data in Excel- shortcut & big list option

00:00 Intro for big data sets
00:06 Create a helper column- even numbers
00:23 Add additional odd numbers for the blank rows
00:35 Sort the numbers from lowest to largest
01:03 Small data sets manual method
01:19 Quick way to highlight the rows individually to use Insert option
Images on website

Although it goes against all database good practice, sometimes you have to insert blank rows into a dataset to accommodate some other system. Perhaps your accounting system needs to have a gap between each journal entry. Below we look at some ways on how to insert blank rows in excel automatically

The Manual Method to insert blank rows
One way is to do it manually. Even in the manual method there is a quick way and a slow way. So below we need to insert a blank row after each row of data.

Firstly you could

highlight a row (row 3 below),
right click on row number,
and choose insert.
You would need to do this again and again until it is done.

How to insert blank rows in excel automatically

An alternate is to highlight all the rows BUT you need to highlight them individually. If you just highlight from row 3 to 25, it will move them all down. To highlight each row individually you must:

Highlight a single row by clicking on the row number (say row 3 below)
Hold the CTRL button (and keep holding it)
Click on each row number individually i.e. while CTRL is held down, click on row 4, then 5 etc
Once you are done you should see that there are thin white lines between the rows which means they have been highlighted individually
Let go of the CTRL key, right click on any one of the row numbers and click Insert.
How to insert blank rows in excel automatically

The end result should look like this. Excel systematically inserts a row in such a way that you now have a blank row between your data.

How to insert blank rows in excel automatically

Faster way to insert blank rows for bigger data sets
The methods above work well if you have a few lines. However, what if you have thousands of lines that need to have a blank row between them.

One way is to create an index column. So below in column J we have created an index column.

In cell J2 we type a 2, then below that we have a formula that says look above you and add 2, so we create a list of even numbers.
Once you have even numbers on each row with data, then you continue the index row by putting a 1 in the next cell (J26 below) and again add 2 to each cell after that (odd numbers).
Make sure the odd numbers go past the even numbers
How to insert blank rows in excel automatically

Now you just need to sort the data on the Index column. So you would:

Highlight the entire area (including the rows with the odd numbers on them)
Go to DATA, then SORT and sort from smallest to largest.
How to insert blank rows in excel automatically

After the sort you have exactly what you want

How to insert blank rows in excel automatically

[bokooo]

BOUNACE TO THAT
if you want to add more than one blank row excel won't allow you to add them this way since you will need to add like the follow
2
3
4
6
7
8

which is unfortunate
yet there is a a way inline with this, you add
1
5
for example and then fill, after you fill the column beside your data you go and add a sequential of 1, 2, 3, 4, 5, 6... and so on under the column you wish to sort by and make sure that the last number is equal or above the last number 1, 5, 9.... in value, then select the entire column and go to delete duplicate in DATA and from there you can use the sort functionality just like in video.
hop that helps 😊