How to Add Space between Rows in Excel

Get FREE Advanced Excel Exercises with Solutions!

To add space between rows, we need to insert blank cells between them. We can do it manually by right-clicking, pressing the keyboard shortcut, or using the Ribbon menu. But all these take a lot of time. In this article, we are going to learn some easy steps to add space between rows in Excel.


How to Add Space between Rows in Excel: 2 Easy Steps

1. Excel Sort Tool to Add Space between Rows

For data management, the Sort tool is one of the important and common tools. Assuming we have a dataset (B4:D9) of the employee names and their total working week along with the working hours per week. We are going to add a space between the rows.

Excel Sort Tool to Add Space between Rows

STEPS:

  • First, we need a helping column (E5:E9) adjacent to the dataset.
  • Next, fill a series of numbers (1,2,…..,5) in that range (E5:E9).

Excel Sort Tool to Add Space between Rows

  • Now select the range (E5:E9), copy it, and paste it at the bottom of the last cell.
  • Then select all the series numbers.
  • After that, go to the Data tab.
  • Click on the A to Z sorting option from the Sort & Filter section.
  • Here a small box pops up.
  • Then select ‘Expand the selection’.
  • Finally, click on the Sort.

Excel Sort Tool to Add Space between Rows

  • We can see that the spaces are added between the rows.

  • We can delete the helping column with the series of numbers and make the dataset suitable to use.


2. Excel VBA Code to Insert Space between Rows

Microsoft Visual Basic for Application can also help us in adding space between rows. Let’s say we have a dataset (B4:D9) of employee names with their working weeks and hours.

Excel VBA Code to Insert Space between Rows

STEPS:

  • Select the worksheet at first from the sheet tab.
  • Now right-click on it.
  • Select View Code.

Excel VBA Code to Insert Space between Rows

  • A Microsoft Visual Basic for Application module pops up. We can press the Alt+F11 keys for it as a keyboard shortcut.
  • Then type the code:
Option Explicit
Sub AddSpaceBetweenRows()
Dim rng As Range
Dim i As Long
Set rng = Range("B5:E9")
For i = rng.Rows.Count To 2 Step -1
rng.Rows(i).EntireRow.Insert
Next i
End Sub
  • Later select the Run option or press the F5 key.

  • At last, go to the worksheet and we can see that the added space between rows.

Read More: How to Space Rows Evenly in Excel


Download Practice Workbook

Download the following workbook and exercise.


Conclusion

These are the quickest ways to add space between rows in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Related Articles


<< Go Back to Space in Excel | Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

2 Comments
  1. Hi Nuraida,

    Love the tutorial! I’m intending on applying the code to a workbook with multiple sheets, any tips on how to do this? When I make the edits it runs the code on the first sheet but not the 2nd, 3rd, 4th etc.,

    Thanks again!

    Froggy

    • Hi Froggy,
      Hope you are doing well. Thanks for reaching out to us with your issue.

      As you intend to apply the above code to a workbook with multiple sheets so you need to add some extra lines in your code to define all available worksheets or selected worksheets.

      Here, I will give you two solution
      1. For selected sheets
      2. For all the available sheets in a workbook

      1. If you want to apply the same code in some selected sheets then use the code given below where I declared sheets names by using an Array.

      Option Explicit
      Sub AddSpaceBetweenRows()
      Dim all_sheets As Worksheet
      Dim rng As Range
      Dim i As Long
      Dim all As Variant

      Set all = sheets(Array(“VBA1”, “VBA2”))

      For Each all_sheets In all
      all_sheets.Select
      Set rng = Range(“B5:E9”)
      For i = rng.Rows.Count To 2 Step -1
      rng.Rows(i).EntireRow.Insert
      Next i
      Next
      End Sub

      2. To loop through all the available sheets of your workbook you will need to use the code given below where I declared all_sheets and used an extra For Loop.

      For your better understanding, I’m providing the modified code here,

      Option Explicit
      Sub AddSpaceBetweenRows()
      Dim all_sheets As Worksheet
      Dim rng As Range
      Dim i As Long

      For Each all_sheets In Worksheets
      all_sheets.Select
      Set rng = Range(“B5:E9”)
      For i = rng.Rows.Count To 2 Step -1
      rng.Rows(i).EntireRow.Insert
      Next i
      Next
      End Sub

      Note: Based on your Excel file you will need to change the sheet name and the cell range selection.

      For further queries comment down below.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo