Quickly insert multiple rows in Excel

If your job requires you to practically live in Excel sheets, you may be looking for ways to make repetitive tasks easier. For example, when you work with data that needs to be updated regularly, you often need to add multiple rows.

In this guide, we will tell you how to insert multiple rows in Excel in a few quick and easy steps. Choose one that suits you best.

Insert multiple rows by dragging border selection

This is the fastest way to insert multiple blank rows into your Excel sheet. It’s easy and doesn’t require any keyboard shortcuts.

  1. Select the row below or above which you want to enter blank rows.
  2. Move your pointer over the border selection.
  3. Press Shift and you’ll see the pointer change to a point with an up and down arrow. Drag the selection down for the number of rows you want to add.

Insert multiple rows with Excel keyboard shortcuts

If you like the idea of multiple right clicks While you are working on your Excel sheet, there are two keyboard shortcuts you can use.

First, select the number of rows equal to the number of blank rows you want to enter. To use shift + space to select all columns for the selected rows or to select rows with row numbers on the left.

Then use one of the two shortcuts:

  1. Alt + I and Alt + R: Press Alt + I, hold the Alt key and press R.
  2. Ctrl + Plus: You must use the plus sign on the numeric keypad. To use the plus sign on the main keyboard, use Ctrl + Shift + Plus.

The hotkey method is also scalable. on . to press F4 on an Excel sheet repeats the previous action. You can use any of the above keyboard shortcuts to add 10 rows, for example, and then press F4 10 times to add 100 blank rows.

Insert multiple copied rows using keyboard shortcuts

If you want to copy or cut multiple rows instead of adding blank rows, use the Alt + I + E shortcut.

  1. Start by copying or cutting the rows.
  2. Select the row above where you want to paste the rows by pressing the row number on the left.
  3. Press Alt + I + E and choose Move cells down

You can also use the following keyboard shortcuts instead of Alt + I + E (the process is the same no matter which keyboard shortcut you use):

  • Ctrl + Shift + =
  • Control + Plus (plus sign must be from the numeric keypad)

Insert multiple rows with Insert option

To use this method all you need to do is select the number of rows you want to add, right click on the selection and Insert

If you have many columns in your data, press shift + space to select all columns for the selected rows, or select the rows using the row numbers on the left side of the worksheet.

When you select InsertExcel will insert rows above the first row you selected.

If you want to enter a relatively larger number of blank rows, you can select a range and then use the Insert option. For example, suppose you want to enter 500 rows.

  1. Select a range in the format ‘start row: last row’. Assuming you want to enter 500 rows after row 5, select the range 5:504 by entering this range in the cell name box.
  1. Right click and select Insert

Insert multiple rows with an Excel macro

If you’re working with a large set of data on a worksheet that you expect to use regularly, you can automate row insertion using macros. You don’t have to be an expert, although knowing the basics of VBA can give you a lot of power while using Excel.

Before starting the process, make sure the Excel ribbon has a tab at the top called Developer† If not, go to FilesOptionsCustomize ribbon and check the radio button next to it Developer

  1. Navigate to the Developer tab and select Visual Basic
  1. Look for the Insert menu once the Visual Basic Editor is opened and select module
  1. Type the following code into the editor:

Sub InsertRowsAtCursor()
Answer = InputBox(“How many rows to insert? (up to 100 rows)”)
NumLines = Int(Val(Answer))

If NumLines > 100 Then
Number ofLines = 100
End if

If NumLines = 0 Then
Go to the endInsert lines
End if

To do
Selection.Entire Row.Insert
Count = Count + 1
Loop While Count < NumLines

EndInsertLines:
end sub

  1. Exit the code editor.
  2. Select in the top ribbon DeveloperMacros or just press Alt + F8† Select the macro you just created and select Options† Set a keyboard shortcut for the macro, but make sure it’s not an existing key combination that you often use, such as: Ctrl + C† In this case we used Ctrl + Shift + T† When you’re done, select Okay to save changes and exit.
  1. Return to the worksheet and select a cell under which you want to enter the rows.
  2. Use the shortcut you set for the macro (in this case Ctrl + Shift + T† You will see a popup on the worksheet asking you to enter the number of rows you want to add. For example, suppose you want to enter five rows. Enter 5 and press Enter.
  1. This adds 5 rows below the cell you selected.

Anytime you want to insert multiple rows in Excel, just use the shortcut, enter the number of rows and you’re done.

Insert multiple rows in Excel

like you often use Excel, it’s worth learning how to save time on repetitive, mechanical tasks like inserting rows. Since there are several ways to insert multiple rows in Excel, it just depends on which method you feel comfortable with. If you have added many blank lines within the dataset, there are also several ways to remove blank rows.

Leave a Reply

Your email address will not be published.