How to Insert Multiple Rows in Google Sheets (4 Ways)

Inserting rows may sound like a pretty straightforward task, but one wrong move or one wrong click on your precious worksheet may end up in disaster.

So today, we are going to break down not only how to insert multiple rows in Google Sheets, but also the whys that are involved. And we will be doing that step by step.

But first, let’s start with some basics.

The Basics of Inserting Rows in Google Sheets

The first thing we will try to understand is how to insert a row, a single row, and all the functions it entails. Let’s start with selection.

It is always a good idea to select the entire row before inserting a new row. We can easily do this by clicking on the row number presented to us on the left side of our sheet:

Worksheet for how to insert multiple rows in Google Sheets

Next, we insert the row. We can go about this in two ways:

  1. Using the Insert Tab
  2. Using Right-Click

The Insert tab can be found on the top ribbon of the window. Clicking on it will present us with a tray of options, and if you have a row selected, the options Row above and Row below will be available. You can already guess what will happen if you click on either.

Insert tab menu

We can also bring up the insert options by Right-Clicking over the selected row. It will give us the same result.

right-click to insert row in Google Sheets

While not that important, you can also ask: What happens if we just select a cell instead of the entire row and click insert?

To put it simply, if you are using the Insert tab method, you will get the same options as before. However, if you are going for the Right-Click method, you will only get one option for rows. Selecting this option will, by default, insert rows above.

new row inserted

A row inserted above

Now that we have got the fundamentals of inserting a row in Google Sheets out of the way, let’s now dive into our main topic for the day.

4 Ways of How to Insert Multiple Rows in Google Sheets

1. Insert Multiple Adjacent Rows in Google Sheets

For this method, we will be looking at how we can insert multiple adjacent rows in Google Sheets.

Step 1: Select the number of rows that you want to insert. You can do this by clicking on the row number and:

  • Dragging the mouse up or down until the number of rows is selected, or
  • Holding down the SHIFT key and pressing the Up or Down Arrow key on your keyboard

Until the number of adjacent rows is selected.

selecting multiple rows

Step 2: Right-click over the selected rows or open up the Insert tab to insert the rows. In the following image you can see that for us it says Insert 3 above and Insert 3 below according to the number of rows we have selected.

right-click option to insert multiple rows in Google Sheets

If we had 2 rows selected, it would say 2, and if we had 10 rows selected, it would have said 10.

Step 3: Select the option that you require. In our case, we have selected Insert 3 below.

Newly inserted rows below in Google Sheets

And this is what it would have looked like if we selected Insert 3 above:

Newly inserted rows above in Google Sheets

Keyboard Shortcuts

At one point, you may reach a point of expertise, or you already are. In such a case, here are some keyboard shortcuts to help you out for inserting rows in Google Sheets:

  1. Insert the number of selected rows above: Ctrl + Alt + Shift + = OR Ctrl + Alt + =
  2. Keypress Macro (Insert Above):
      1. Alt + I, then R (Chrome browser only)
      2. Alt + Shift + I, then R (other Browsers)
  3. Keypress Macro (Insert Below):
      1. Alt + I, then W (Chrome browser only)
      2. Alt + Shift + I, then W (other Browsers)

2. Insert Multiple Non-Congruent Rows with Keyboard Shortcut

What if you wanted to insert multiple rows that are not adjacent to each other?

Unfortunately, this is not possible in Google Sheets.

As you can see in the following image, we have selected three non-congruent rows in our workbook (hold CTRL and click desired row numbers). But when we open up the option tray by right-clicking, the Insert row option does not appear.

non-congruent selection of rows

The same goes for the Insert tab, the options are greyed out.

greyed out options

This is one major limitation of Google Sheets. But we have a workaround to this.

For this problem, we will the utilizing the F4 key of our keyboard.

In Google Sheets, what the F4 keypress does is that it repeats the last action taken.

As you can see, we have first inserted a row above row 5 in the usual way. Google Sheets has kept this action in its memory.

manual insertion of a row

Now, if we select row 8 and press F4, we see that another row has been inserted above it.

table before F4 keypress

Before F4

table after F4 keypress

After F4

Google Sheets has repeated its last stored action. We can apply this multiple times.

While it is true that having a direct option to insert non-adjacent rows would have come in handy, but this method will still save you a lot of time.

On top of that, this method can be used for ALL actions taken in Google Sheets, not only for inserting rows!

3. Add More Rows at the Bottom of the Sheet

As the name suggests, this method is purely about adding more rows to the bottom of the end of the worksheet.

As you can see, at the bottom of the worksheet, the number of rows goes to about a thousand.

last row of a Google Sheets worksheet

Many datasets have more than a thousand rows of data. In such cases, we can utilize the Add more rows button over here.

All you have to do is simply input the number of rows that you want to add to the box and press Add.

inputting the number of rows to insert

Et voila! We have successfully added 500 rows to our worksheet.

new rows inserted

As you have noticed, this method only shines when you have too much data to work with.

4. Insert Multiple Rows Using Macros in Google Sheets

This is a more technical approach to our method 2 as it involves creating and applying macros.

Let’s go about this process step by step:

Step 1: Select the Tools tab in the top ribbon. Here you will find the option Macros. Selecting this option will allow you to also select the option Record macro.

navigating to Record macro

Step 2: After clicking on Record macro, the following panel will pop up at the bottom of the sheet. This represents that whatever action you will be taking now will be recorded.

Recording macro in Google Sheets

After you are done recording actions, click Save. In our case, the action was “inserting a new row above row 5”.

save recorded macro

Step 3: Clicking on Save will open up a Save new macro dialogue box. Here you can name your new macro as well as assign a shortcut to it. Click Save again.

save macro options in Google Sheets

Step 4: To run the macro you can utilize the shortcut that you have saved it under, or you can navigate to the Tools tab > Macros > (Name of your macro).

applying newly recorded macro in Google Sheets

In our case, the macro will keep on adding a new row above row 5 every time we activate it.

new row inserted by macro in Google Sheets

In this way, you can repeat a certain action multiple times, and it is not only restricted to adding new rows or multiple new rows.

Remove Multiple Rows

So far, we have been adding multiple rows, so it is also important to understand how to remove multiple rows as well.

Unlike inserting rows, removing multiple rows is more of a walk in the park.

Step 1: Highlight the rows you want to remove.

select multiple rows

Step 2: Right-Click on the highlighted rows, this will open up the options available. Click Delete rows …

delete multiple rows in Google Sheets

The result:

result after rows deleted

Great! The rows have been deleted.

Limitations (Google Sheets)

To summarize, the only limitation we have found is purely on the side of Google Sheets. To be more precise, it is what we faced in method 2 – the inability to add rows to multiple non-congruent selections.

This is also to be expected since Google Sheets is a fairly lightweight application, especially when compared to Microsoft Excel.

Final Words

All in all, how to insert multiple rows in Google Sheets has proved to be fairly straightforward. And thus, we have tried to cover the different iterations of the approach as the situation may not be the same all the time. Let us know if our article has proved helpful to you, and we also welcome tips from you guys.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply