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.
Table of contents
YouTube 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.
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.
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.
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
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.
After the sort you have exactly what you want
Make the insert blank rows automatic
If you want this to be more automatic e.g. you copy and paste it into an area and in a new sheet it creates the blank rows you can use a VLOOKUP.
Below in the blue area we can paste our data. Note that in column A we have created a Lookup Index which is even numbers.
In cell A28 we created a column going in number order (1, 2, 3 etc). Now all we need to do is create a VLOOKUP that pulls through the number if it finds a matching number (e.g. the 2), but if it doesn’t find a match it puts a blank in.
To learn more about VLOOKUP and how/ why we have the column numbers in row 1, look at our online VLOOKUP course.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
Insert Copied Cells not working