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.
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 + =
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.
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.
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.
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.
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.');
}
}
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.
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.