Working in Google Sheets: add, freeze, and remove rows

It's time to learn more about lines in Google Sheets. Learn how to insert new rows to your table - one or many at once; freeze lines in a spreadsheet in a few clicks; delete selected or only empty rows in your table. Some useful shortcuts and the add-on are there to ease your work.

Start working with rows

Rows are one of the basic elements of Google Sheets. They are as important as columns, and you need to know how to handle them to operate your data.

Of course, all electronic tables have standard rules of working with rows and columns. And they all are pretty much the same. However, rows in Google Sheets are somewhat peculiar to manage.

All operations can be applied to either one row or a group of rows. To begin, you need to select a cell within a line with data or select an entire row completely.

  1. To select a row, right-click its header (a grey field with an order number on the row's left).
  2. To select multiple adjacent lines, select the top row and drug the mouse till the bottom of the range.
    Tip. You can select the top row, press and hold Shift on your keyboard, and then select the bottom line. All the rows between these two, including them, will be selected.
  3. To select non-adjacent rows, just click on them while holding Ctrl pressed on your keyboard.

The row is selected and is ready to be managed.

How to add rows in a Google spreadsheet

It often happens that we need to squeeze a few rows just between other datasets.

Add one row to Google Sheets

Right-click the number of that row where you'd like to add one more and choose to insert it above or below from the context menu that appears:
Insert 1 row above or below the selected one.

Another way to add a line is by using the Google Sheets menu: Insert > Row above (or Row below).

Insert few lines to a spreadsheet

To add a few rows at once, for example, 3, I'd recommend you highlight the needed number of rows with the mouse and repeat the steps above. Google will prompt you to insert as many lines as you selected:
Insert a few rows at once in Google Sheets.

There are useful keyboard shortcuts in Google Sheets to manage rows. If you use Windows as I do, use Alt combinations. When Alt is pressed, Google Sheets underlines single letters in its menu. Press one of these letters on your keyboard to select the corresponding option.

For example, Alt+I will open the Insert menu. Press R next to add row above or B to add it below.

Action Google Chrome Other browsers
Insert row above Alt+I, then R
or
Ctrl+Alt+"="
Alt+Shift+I, then R
or
Ctrl+Alt+Shift+"="
Insert row below Alt+I, then B Alt+Shift+I, then B
Delete row Alt+E, then D Alt+Shift+E, then D

Insert multiple rows in a Google spreadsheet

What should I do when I need to add, say, 100 new rows? Should I select 100 existing lines, so Google could offer a corresponding option? No, of course not.

No matter how many rows there are in your table and how many of them you want to add, there's a feature that eases the job.

Go to the very bottom of your table - there you'll see the Add button. It's designed for cases like this. Simply enter the number of lines you need to insert and click this button. The rows will be added to the end of the table:
You can add as many rows as you need by clicking a single button.

Tip. You can quickly jump to the very bottom of your table by pressing Ctrl+End on your keyboard.

Limit for cells in Google Sheets

When adding rows and columns into Google Sheets, you need to remember one of Google Sheets' limits.

One file can contain maximum 2 million cells.

My spreadsheet has 26 columns (A-Z), and I was able to insert 76927 rows. Impressive, right?

But if you have several spreadsheets in one file, the same limit spreads over the entire file and all its tabs rather that each tab separately.

There's no need to calculate the number of cells in your head. If you try to add rows that are about to exceed the limit, you'll see a notification message:
The message that won't allow exceeding the limit for cells.

Moreover, you won't able to add another spreadsheet to a file. Because by default it would contain 1000 rows and 26 columns, while your limit for cells is already exceeded 😊

How to freeze rows in Google Sheets

Everyone who works with Google Sheets sooner or later thinks about locking at least a header row. Thus, the line won't disappear from the sheet when you scroll down the table. Of course, you can freeze as many rows as you need, not only the first one. Here are two ways to do that and a way to cancel the changes.

  1. Go to View > Freeze. The 1 row option will lock the header row, the 2 rows option - first two lines of the table.

    To freeze more lines, decide on the range to lock, select any cell in the row right below that range, and select Up to current row from the menu:
    How to freeze rows in Google Sheets.

    As you can see, it's the same as locking columns.

    Note. If you freeze more rows than can be displayed on your screen, you won't be able to scroll down the table. If that happens, you'll see a notification message and will be able to unlock everything back.
  2. Hover the cursor over the bottom border of the grey box that joins columns and rows. When the cursor turns into a hand icon, click it and drag the borderline that appears one or more lines down:
    Lock the rows manually with the mouse.
  3. To cancel the changes and unlock all rows, choose View > Freeze > No rows in the Google Sheet menu.

How to delete rows in a spreadsheet

We can remove lines from Google Sheets in a similar way we add them.

Select a row (or many lines), right-click it, and choose Delete row. Or go directly to Edit > Delete row in the Google menu:
Use the context menu to delete a row in Google Sheets.

How to remove empty rows

Sometimes a few empty rows may blend into your spreadsheet - when the data is removed, or for some other reason. Of course, no one wants empty lines in their neat tables. How do we get rid of them?

The first thing that comes to mind is to look through the whole table and delete those lines manually. But if the table is too large, it'd take a lot of time to process it, and you could still miss a row or two.

Sure, you could filter the rows, display only empty ones, then get rid of them. But you'd have to filter each column, otherwise, risk deleting the lines that contain information in some columns only.

However, there is one quick and reliable way to delete empty rows: the Power Tools add-on.

After you install it, go to Add-ons > Power Tools and click on the Remove icon:
Use Power Tools to delete all empty rows from your table quickly.

There, open the Remove empty rows and columns group and check the Remove all empty rows option. Click the Remove blue button, and all empty lines are deleted.
Check the needed options and click Remove.

If you have any questions about the add-on's work or about rows in general, feel free to leave a comment down below.

Next time I'll tell you about other operations you can perform on rows.

You may also be interested in:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com