How to insert multiple blank rows in Excel

While working in MS Excel sometimes inserting single or multiple rows is necessary. A few of the reasons are listed below:

Adding new data: When working with Excel, it is common to have new data that needs to be added to an existing worksheet. By inserting rows, users can make space for new data without having to reformat or rearrange their existing data.

Maintaining formatting: When inserting rows in Excel, it is important to note that any existing formatting will be preserved. This means that if a user has formatted their worksheet with alternating colours, for example, inserting a new row will not disrupt the formatting.

Sorting and filtering data: Inserting rows can be particularly useful when working with large data sets that need to be sorted or filtered. By inserting rows at the top of a worksheet, users can add sorting and filtering criteria that will apply to the entire worksheet.

Creating calculations: Excel is a powerful tool for performing calculations on data, and inserting rows can be useful when setting up formulas and functions. For example, inserting a row at the bottom of a table can provide space for a formula to calculate a total or average.

There are several ways to insert a row or multiple rows in excel. Let’s learn them one by one. Suppose we have the following data set above, follow the below steps to insert rows. We’ll discuss three different methods to achieve the said task.

Method 1 – By using the Context Menu

Step 1 – Select Rows

  • Let us suppose we want to insert 3 blank rows between JOHN DOE & JANE SMITH.
  • To select these rows, click & drag the cursor over the row number on the left-hand side of the excel sheet.
  • In this case, we should select rows 3, 4 & 5.

Step 2 – Click the Right Mouse Button

  • Click the right mouse button.
  • Select insert from the context menu.

Step 3 – Blank Rows Inserted

  • Blank rows will be inserted after row number 2.

Method 2 – Using Insert Button from Cells Group

Step 1 – Select Rows

  • Suppose we want to insert 2 blank rows between JANE SMITH & TOM LEE.
  • Select these rows by clicking & dragging the cursor over the row numbers on the left-hand side of the excel sheet.
  • For this example, we will select rows 4 & 5.

Step 2 – Click Insert Button

  • Click the Insert button in the Cells group of the Home tab.
  • Select insert sheet rows.
  • Alternatively, you can directly click on the insert button.

Step 3 – Blank Rows Inserted

  • Blank rows will be inserted after row number 3.

Method 3 – Using Shortcut Key

Step 1 – Select Rows

  • Now let’s insert the blank row with the help of a shortcut key.
  • Let’s assume we want to insert blank rows between JOHN DOE & JANE SMITH.
  • Select row numbers 3, 4 & 5.

Step 2 – Use The Shortcut Key

  • Use the shortcut key.
  • Hold down the CTRL key and press the + key (PLUS SIGN)
  • Blank rows will be inserted.