The appearance of the spreadsheet plays an important role, so it is important to ensure that all its rows are displayed normally. If there are blank rows or columns in a document, they only make it harder to read and apply additional features, so it is often necessary to remove unused areas. In the framework of this article, I will tell you in what ways you can solve the problem.
Method 1: Deleting at table borders
As a first example, I will consider the situation when it comes to a ready-made document in Google Sheets, where a certain number of rows and columns are not used. By default, they will always appear in the spreadsheet, but you can easily remove them with just a few simple steps.
- First, be sure to define the boundaries of the spreadsheet so that you don’t delete unnecessary things in the future. If it is already visually designed, it will be extremely easy to do this.
- Now select all the letters of the columns by holding the left mouse button on the first one and dragging to the end of the table.
- Right-click on one of the column letters and select Delete Columns from the context menu .
- You will see that a blank area is now displayed instead. You can use zoom to hide it and leave only the required cells.
- Roughly the same applies to lines, so select them all first.
- This will take a little longer as you will have to scroll to the bottom with the mouse wheel when you reach line 1000.
- Right-click on the number of any of them and select “Delete lines” .
- As you can see, now the table is presented within its borders without extra areas. If necessary, you can always add more rows and columns through the same context menu.
I repeat that this method can be considered optimal in cases where you have already compiled a table in the online service in question and are sure that its contents will not be edited in the near future.
Method 2: Remove spaces through filtering
The following situation is suitable for those users who are faced with incorrect data filling in Google Sheets. For example, someone filled rows and columns one by one, adding unnecessary visuals or leaving the cells in their default state. Of course, such an approach to filling out documents is not always welcome, therefore, it becomes necessary to remove such gaps, which is done by filtering and further copying the ranges.
- Hold down the left mouse button and select all rows within one or more columns, including gaps.
- After that, expand the Data menu and activate the Create Filter tool .
- Now near the first line you will see a down arrow, clicking on which will open the filtering options.
- You should remove empty lines from the filter by unchecking the corresponding box. Confirm the changes by clicking on “OK” .
- Now you will see that the empty rows have disappeared and the contents of the table are displayed correctly. Select the range and copy using the hotkey Ctrl + C .
- Instead, you can call the familiar context menu and select the item there “Copy” .
- It remains to delete the current filter, since it will no longer be needed when further interacting with the table. This is done through the same menu “Data” .
- However, that’s not all, because without the filter, spaces will reappear. You need to select the entire range and delete all these lines through the context menu.
- Paste the content you copied earlier in their place to make sure they display correctly.
- It may happen that the labels in the first line are bold, so undo this formatting if necessary.
This was just one example of using filtering in Google Sheets. If you are interested in this topic, I advise you to read my other article , which tells in more detail about this tool and its capabilities.
Method 3: Removing duplicates
Empty rows in Google Sheets in a certain area are duplicates because their content is the same – empty. Using the data cleaning function, you can remove repetitions, thereby achieving the desired effect.
- Select the required range along with empty lines and expand the menu “Data” .
- In it, hover over the item “Data Cleanup” and left-click on the item “Delete duplicates” .
- A window will appear in which you do not need to make any changes, just confirm your actions.
- You will be notified how many duplicate rows were found and how many unique ones are left.
- One empty line will still remain, since it is not repeated. Delete it through the context menu by right-clicking on this cell.
- This completes the cleaning process. Do the same for the rest of the columns, if necessary.
Each of the above methods has its own characteristics and differs in the implementation algorithm. Read my instructions and choose the option that is right for you.