Insert Multiple Rows in Excel

How to Insert Multiple Rows in Excel?

To insert multiple rows in excel, we need to select the number of rows first based on that we can insert those rows. Once the rows are inserted, we can use the F4 key to repeat the last action and insert as many rows as we want.

Top 4 Useful Methods to Insert Rows in Excel (Discussed with an Example)

  1. Insert Row using INSERT Option
  2. Insert Multiple Rows in Excel using Short Cut Key (Shift+Space Bar)
  3. Insert Multiple Rows Using the Name Box
  4. Insert Multiple Rows Using the Copy & Paste Method

Let us Discuss each method in detail along with an example –

You can download this Insert Multiple Rows Excel Template here – Insert Multiple Rows Excel Template

Method #1 – Using INSERT option

We need to select the row first, but it depends on how many rows we insert. If we want to insert two rows, we need to select two rows, and if we want to insert three multiple rows, we need to select three rows and so on.

Multiple Rows in Excel Method 1.1 - Step 2

In the above image, I have selected three rows, and now I will right-click on the column header and click on insert; this would insert three multiple rows in a single shot.

Multiple Rows in Excel Method 1.1 - Step 4

Method #2 – Using Excel Short Cut (Shift+Space Bar)

  • Step 1: Select the cell above you want to insert the row.

Multiple Rows in Excel Method 2 - Step 1

  • Step 2: Now, use the shortcut key to select the entire row instantly. The shortcut keyboard key is Shift + Space Bar.

Multiple Rows in Excel Method 2 - Step 3

  • Step 3: Now, if you want to insert two to three rows, select those many rows by using the Shift + Down Arrow key. In the below image, I have selected 4 rows.

Multiple Rows in Excel Method 2 - Step 4

  • Step 4: Now, click on another keyboard Ctrl + (plus key) shortcut key to insert a row in excel.

Ctrl + (plus key) shortcut

Now we have inserted 4 multiple rows. Suppose we need to insert another 4 rows; we need to click on Ctrl + if the rows are selected, or instead, we can use the key F4, which repeats the previous action in excel.

Multiple Rows in Excel Method 2 - Step 6

Method 3: Using the Name Box.

Suppose if we need to insert 150 rows above the cell we have selected will take some time because first, we need to select those many rows first then insert rows in excel shortcut.

Selecting 150 rows instantly is not possible in the above two methods. We can select those name box in excel.

  • Step 1: Select the cell above we need to insert rows.
  • Step 2: On the name, box mention the row range. In my case, I have mentioned 5:155 because I have to insert 150 rows.

Multiple Rows in Excel Method 3 - Step 1

  • Step 3: After typing the range, hit the enter key; this would select the cells from 5:155 instantly.
  • Step 4: Once the range is selected, use the Ctrl + shortcut key to insert a row in excel. This will insert 150 rows in just a click.

Alternative Shortcut Key to Insert Row in Excel: ALT + H + I + R is another shortcut key to insert a row in Excel.

Alternative Shortcut Key to Insert Row in Excel

Method 4: Using the Copy & Paste Method

Microsoft Excel is such a flexible man. Can you believe you can insert rows by the copy-paste method?

Yes! You heard it right; we can insert rows just by copy and paste another blank row.

  • Step 1: Select the blank row and copy.

Select the blank row and copy

  • Step 2: Now select the cell above you want to insert rows.
  • Step 3: Once the desired cell is selected, select the number of rows you wish to insert and right-click and select Insert Copied Cells.

Right Click & Insert

Case Study: I have data from A1: A10, as shown in the below image.

data

I want to insert one blank row after every row, as shown in the below image.

insert one blank row

Alternative Coolest Technique

In the above example, I have only ten rows. What if I have to do it for 100 cells? This will take a lot of time. However, I have the coolest technique you ever see.

Follow the below steps to learn.

  • Step 1: Insert serial numbers next to the data.

Insert serial numbers

  • Step 2: Copy those serial numbers and paste them after the last serial number.

copy and paste serial numbers

  • Step 3: Now, select the entire data, including serial numbers, and press Alt + D + S.

A+D+S

  • Step 4: From the dropdown list, select the second column and make sure the smallest to highest is selected.

Drop down list - smallest to largest

  • Step 5: Now, click on the Ok. This would instantly insert the blank row after every row.

Click Ok to insert blank row

Wow! Amazing. Probably the coolest and the most intelligent technique you have learned till date :-)

Things to Remember

  • Alt + H + I + R, Ctrl +, is the shortcut key to insert a row in excel.
  • Always insert new rows after selecting the entire row (s) first. Otherwise, there are chances that your data may shuffle.

Recommended Articles

This has been a guide to Insert Multiple Rows in Excel. Here we learn how to insert multiple rows in Excel using excel insert shortcuts along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>