This post was last Updated on by Himanshu Tyagi to reflect the accuracy and up-to-date information on the page.
If you have to work with Google Sheets or Excel, handling a large amount of data is routine. However, removing blank cells when thousands of entries are in the sheet can be pretty cumbersome. In this tutorial, you will learn how to crop Google Sheets.
Managing this daunting task manually while handling other entries can be pretty frustrating. Having several gaps in the Excel sheet where data is not to be entered or missing is often very frustrating.
Scrolling up and down, left, and right to find the following data entry is not how you would like your superiors to view the Google sheet. If the missing entries are restricted to a few rows or columns, you might be able to make the changes quickly. However, if they are scattered across the sheet, you need to find a more efficient way to do it.
Also Read: How To Change Page Color On Google Docs
Why do you need to crop Google sheets?
Also Read: How To Set Up a Line Chart in Google Sheets
Did you know that the spreadsheet, which seems endless and limitless, has a size limit? Yes, the Google Sheet has a limit of 5,000,000 cells, including any blank rows and columns left intentionally.
If you do not remove those blank cells, your sheet gets unnecessarily larger with several rows and columns. It might slow down performance or crash if it exceeds the size limit. You are at the risk of losing your precious data if the latter happens.
What’s more, you might even fail to recover it, which can be catastrophic, especially if it is essential. As a result, deleting unnecessary blank items from your Google Sheet is essential. When you crop Google sheets, you reduce the sheet’s overall size, enhancing its performance.
Also Read: How To Create a Dropdown in Google Sheets
How can you remove extra rows and columns from Google sheets
Considering the importance of reducing the size of Google sheets by removing blank rows and columns, you must understand how to crop Google sheets. There are several ways in which you can do this. These include:
How to delete blank rows in Google sheets?
Follow the given procedure to delete blank rows in Google sheets.
Step 1: The primary step is highlighting the cells you wish to filter.
Step 2: On the Data tab, click the Create a Filter option which opens filter icons on every header row column.
Step 3: Click the Filter icon on any column. In the dropdown menu, first, click Clear, and then select the Blanks option. This helps select all blank cells in the chosen range.
Step 4: Once all blank rows are highlighted, right-click on any highlighted cells and click Delete rows. This will delete all blank columns.
Step 5: Select the Turn Off Filter in the Data tab to let your data reappear.
Also Read: How To Create Crosswords Using Google Sheets
How to sort data sets and delete empty rows?
Another method is to sort the data set to have all empty rows together. Once that is done, you can select and delete them quickly. Here are the steps to follow for this procedure.
1. Select the entire data set and click on the Data tab.
2. Click on Sort range by column A, Z → A to sort all empty rows at the bottom of the data set.
3. Once the empty rows are grouped, you can select and delete them at the same time quickly.
Remember that you cannot go back to the original data set once you have sorted the data set. Also, remember that while sorting the data, you need to select Z → A option instead of A → Z, as the latter will cause data headers to be sorted and might end up in the middle or end of the data set.
How to use an Add-on to crop Google sheets?
Though these methods are easy to do, if you are looking for another option, several add-ons are available that can help you crop Google sheets. The add-ons are codes written by programmers for specific purposes. You can install the add-on to crop Google sheets and remove empty rows. The steps involved in this procedure are –
Step 1: To install the add-on, click on the Add-ons option in the menu and click ‘Get add-ons.’
Step 2: Once the dialog box opens, search for ‘delete empty rows’ to get a list of add-ons in Google sheets options that can help you with your query.
Step 3: Opt for a Crop Sheet or other free or paid versions. You can sign in to the Google Sheet using your Google account. You can also allow the add-in to access your account. Once that is done, the add-on can remove empty rows or columns in the Google Sheets.
Step 4: There are two ways to crop Google sheets using this add-on. You can select the entire data set and click on the add-on option in the menu. Hover the cursor on Remove Blank Rows (and more!) option and click on Delete/Hide blank rows/columns option. Select ‘All row cells must be blank.’ Select Remove Blank Rows from More options and click Delete.
Extra rows and columns can play spoilsport, especially if you do not want to share every data in the Google Sheet with people. If you have to share the documents with different people, you can crop Google sheets before sending them quickly using any techniques mentioned above.