How to Insert Multiple Rows in Google Sheets

- Written by Puneet

excelchamps-free-courses-puneet-gogia

For example, you’re a data analyst, and your boss has just handed you a list of new employees to add to your company’s existing employee spreadsheet.

insert-multiple-rows

To make space for the new information, you’ll need to insert multiple rows between the existing ones to keep everything organized and up-to-date.

In Google Sheets, there are multiple ways that you can add multiple rows to a sheet instantly or in one go. In this tutorial, we will look at all these methods and find which is easiest. So let’s get started.

Keyboard Shortcut for Inserting Multiple Rows

The keyboard shortcut is the easiest way to insert multiple rows in Google Sheets. Let’s say you want to add 4 rows before row 6, for this, you need to select 4 rows starting from row 6, and then use the keyboard shortcut:

  • For Windows: Ctrl + Alt + Shift + =
  • For Mac: Cmd + Option + Shift + =
shortcut-for-inserting-rows

The moment you press the keyboard shortcut it inserts 4 new rows above row 6. So, the row on the 6th number shifts to the 10th after having 4 rows before it.

Use Insert Row Option for Multiple Rows

There is also an option that you can use to insert multiple rows. Like a keyboard shortcut, to use this option to insert multiple or more than one row, you need to select those number of rows in your sheet.

Afterward, go to the Data menu> Insert Rows, and select where you want to insert the new rows, before or after the selected rows.

insert-rows-from-insert-row-option

You can see in the above example that we have selected 4 rows from 6 to 9, and when you go to the rows option from the insert menu, Google Sheets already knows that you have selected 4 rows. And now, when you select an option, let’s say, “Insert 4 rows below”, it instantly adds four rows below row 9.

four-rows-added-below

This option allows you to decide which side you want to insert or add new rows, either below or above.

Use the Right-Click Menu to Insert New Multiple Rows

Like the insert menu, you use the same option from the right-click menu. Once you select the number of rows you want to insert in a sheet, right-click on it, and there you will find two options.

right-click-menu-to-insert-rows

Now, from here, you can choose whether you want to add new rows above the selected rows or below. You can do both from the right-click menu.

Use a Google App Script to Insert Multiple Rows at Once

Below is a code that can help you add multiple rows flexibly. When you run this code, it will show you an Input Box to enter the number of rows below the selected cell or row.

google-app-script-to-insert-rows

Once you enter the number of rows and click OK, it will instantly insert that number below the selected cell.

function insertRowsBelowSelectedCell() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeRange = sheet.getActiveRange();
  var startRow = activeRange.getLastRow() + 1;  // Determine the row number immediately below the active selection
  
  // Create a dialog box to input the number of rows to insert
  var ui = SpreadsheetApp.getUi();  // Get the user interface
  var response = ui.prompt('Insert Rows', 'Please enter the number of rows to insert:', ui.ButtonSet.OK_CANCEL);

  // Process the user's response
  if (response.getSelectedButton() == ui.Button.OK) {
    var numRows = parseInt(response.getResponseText());
    if (!isNaN(numRows) && numRows > 0) {
      // Inserts the specified number of rows starting from the row below the selected range
      sheet.insertRows(startRow, numRows);
    } else {
      ui.alert('Invalid number of rows. Please enter a valid integer greater than zero.');
    }
  } else {
    ui.alert('Operation cancelled.');
  }
}
run-the-code

When you click the run button and return to the worksheet, it will show you that input box to enter the number of rows you want to insert. When you click OK, it will run the code and insert rows below the selected cell.

Drag and Drop Method

Apart from all these methods we discussed and learned above, you can also drag and drop blank rows within the rows.

drag-and-drop-to-insert-rows

First, you need to select the blank rows, say 4, and then you use drag to pick those rows and then go to the row where you want to add those rows and drop them there.

Last Updated: May 25, 2024