It's time to learn more about rows in Google Sheets. Learn how to insert new lines to your table – one or many at once; freeze rows 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.
- To select a row, left-click its header (a grey field with an order number on the row's left).
- 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.
- 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 Google Sheets
It often happens that we need to squeeze a few rows just between other datasets.
Tip. Make sure there's enough room for new rows in your spreadsheet by reading this article.
Insert 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:
Another way to add a line is by using the Google Sheets menu: Insert > Row above (or Row below).
Add 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:
There are useful keyboard shortcuts in Google Sheets to manage rows. If you use Windows as I do, use Alt combinations. You will need to press one of the 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:
Tip. You can quickly jump to the very bottom of your table by pressing Ctrl+End on your keyboard.
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 in Google Sheets as you need, not only the first one. Here are two ways to do that and a way to cancel the changes.
- 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:
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.
- 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:
- 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:
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 > Clear:
There, check the Remove all empty rows option. Then hit the Clear button and all empty lines will be deleted.
If you have any questions about the add-on 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.
6 comments
How do you exit out from the "add multiple rows"? If you don't have the authority to add the spreedsheet is stuck and I can't get out of that function. Please help
Hello Suzie,
Could you please specify what function is stuck? Usually, pressing Esc or clicking somewhere on the sheet (in case you selected the rows manually) helps.
Excellent tool!
Thank you for your feedback, Jason!
If it's not too much trouble for you, we would appreciate it if you rate our add-on and write a few words on Google Workspace Marketplace.
Thanks in advance! :)
How can I hide a row and have it not add up in the total column?
You need to use the SUBTOTAL function, like this:
=SUBTOTAL(109,A2:A10)
in 109 "9" is the number of the SUM function and "10" is the command to skip hidden cells.
You can learn more about SUBTOTAL here:
https://support.google.com/docs/answer/3093649?hl=en