Skip to main content

How To Quickly Insert Blank Rows Between Existing Rows In Excel

Table of contents
  1. Video training

Sometimes, you may need to dynamically insert blank rows between each of the existing rows into your Worksheet. Although blank rows are generally bothersome, in some situations, the final formatted version of your report requires them to separate data. This tip will help you quickly insert blank rows between multiple data lines. If you want to use VBA to do this, see this VBA code.

  1. Create a helper column. Enter 1 and 2 in the starting cells (D1, D2), grab the fill handle and drag it to the last data cell (D8).
    Quickly Insert Blank Rows Between Existing Rows 1
  2. Now copy the series (D1:D8) in the helper column and paste the range just below the last cell (D9:D15).
    Quickly Insert Blank Rows Between Existing Rows 2
  3. Select the Helper data.
    Quickly Insert Blank Rows Between Existing Rows 3
  4. On the Home tab, in the Editing group, click Sort & Filter button, then click Sort Smallest to Largest.
    Quickly Insert Blank Rows Between Existing Rows 4
  5. When we click Sort Smallest to Largest, the screen will display a warning window, in Sort Warning window, select Expand the selection, then click Sort to close the warning box and sort our data.
    Quickly Insert Blank Rows Between Existing Rows 5
  6. You will see the results. Empty rows will appear between the lines with data.
    Quickly Insert Blank Rows Between Existing Rows 6
  7. Select and delete the helper column.
    Quickly Insert Blank Rows Between Existing Rows 7
  8. Final results.
    Quickly Insert Blank Rows Between Existing Rows 8

Question: If you want to insert two or more blank rows between existing rows, how to do it? It's very easy, in step 2, copy and paste helper data two times.

Video training

Quickly Insert Blank Rows Between Existing Rows In Excel

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>

20 comments
  1. BE
    Beth

    These directions do not work

    • NT
      Ntinos

      @Beth Not only they do work but this is the best guide I found online. Straightforward, without useless info, saved me big time. Thanks

  2. MA
    Max

    Brilliant. Thanks a lot!

  3. BP
    Bogdan Petre

    saved me some time!

  4. KA
    Karen

    This is the most f'n genius thing I've found on the internet today!

  5. SO
    Soko

    Saved me a lot of time and headache. Greatwork!

  6. LO
    Lola

    Great help! Exactly what I needed to do.
    Thanks!!

  7. DB
    Dharmishtha Bariya

    How i insert blank row after 7 rows

  8. FE
    fedz

    no sort warning appeared

  9. AM
    ambrose

    its a very helpful

  10. SU
    sudarshan

    It helps a lot, appreciate it

  11. PR
    prem

    Great help Thanks!!

  12. GA
    ganesh

    how insert new row in excel if there is{ =IF(E694="Cash", G693-F694,(IF(E694="card",G693,"")))}this formula exit

  13. BA
    BAly

    Many thanks works great for journal vouchers in the field I work in!

  14. DO
    Don

    A simple and helpful trick. Just did it with a 60k line item sheet with no problems.

  15. AU
    Audrey-Ann

    Helpful trick, thanks!

  16. KA
    Kawaljit

    Helpful!

  17. GE
    Gerald

    Probably the best and most straightforward explanation online on how to do something so simple, but for whatever reason it's a multiple step process. Others make it even more complicated with their approach.

    Unfortunately, your example with 7 rows isn't very realistic. One will typically have hundreds or thousands. There should be a more efficient way , say a macro where you would just enter the starting row and ending row number.

    I found the video needs to be slowed down. It's quite fast.

  18. AT
    Athul

    Is there any option to insert row between filtered items?

  19. CH
    Chris

    This is a great shortcut to know about! It's not what I was looking for today, though.