Removing empty cells in Google Sheets can significantly improve the readability and functionality of your spreadsheet. Here’s a guide on different methods you can use to efficiently remove or deal with empty cells in Google Sheets, including ways to remove empty columns or remove blank rows in Google Sheets.
Method 1: Filter and Delete Empty Cells
One of the simplest ways to get rid of empty cells is by using the Filter function to isolate and delete them.
Select your data range:
Highlight the range of cells where you want to remove empty cells. This can be a single column or multiple rows.Apply a Filter:
Go to the toolbar and click on the “Data” tab, then select Create a filter. You’ll now see filter dropdowns appear at the top of each column.Filter empty cells:
Click on the filter icon in the column that contains empty cells. In the filter options, uncheck Blanks to hide all empty cells.Delete visible rows or columns:
Once the empty cells are hidden, select the visible rows or columns that remain, right-click, and choose Delete rows or Delete columns. This will remove only the visible rows or columns with empty cells.Remove the filter:
To restore the original structure, go back to “Data” and click on Remove filter.
This method effectively deletes empty cells in Google Sheets and shifts the remaining data up. It can also help if you’re trying to remove empty columns in Google Sheets without disturbing the rest of your data.
Method 2: Using a Formula to Remove Empty Cells
If you prefer not to manually delete empty cells, you can use a formula to automatically display only non-empty cells in a new range. Here’s how:
Create a helper column:
In a blank column, use the following formula to filter out empty cells:=FILTER(A:A, A:A <> "")
Replace
A:A
with the range of your column. This formula will return only the non-empty cells from column A.Copy and paste values:
Copy the filtered data from the helper column and paste it as values into the original location (or another range) to remove empty cells permanently.
Method 3: Script Automation (Google Apps Script)
For advanced users who want a more automated solution, you can write a script in Google Apps Script to remove empty cells from your sheet.
Open Script Editor:
In your Google Sheet, click on Extensions → Apps Script.Write a script:
Here’s an example script that removes empty cells from a specified column:function removeEmptyCells() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getDataRange();
var values = range.getValues();
var cleanedValues = values.filter(function(row) {
return row[0] !== ''; // Change this index to target different columns
});
sheet.getRange(1, 1, cleanedValues.length, cleanedValues[0].length).setValues(cleanedValues);
}This script checks the first column and removes any rows where the cell in the first column is empty. You can modify the index in
row[0]
to check other columns.Run the script:
Save the script and click on the Run button. The script will automatically clean up the empty cells in the specified range.
Method 4: Remove Blank Rows Using a Filter
If you have entire rows that are empty and want to delete them, you can apply a filter to isolate and remove these rows.
Select the entire data range:
Highlight the data range in your sheet, including all the columns that could potentially contain empty rows.Apply a filter:
Click on Data → Create a filter. A filter will now be applied to your data.Filter out empty rows:
From the filter dropdown, uncheck Blanks in each relevant column to filter out rows where cells are blank.Delete the empty rows:
Select the remaining rows with blank cells and right-click to delete them.Turn off the filter:
Go back to Data and click on Remove filter to restore the view.
Method 5: Using Find and Replace to Find Empty Cells?
Can you use Find and Replace (shortcut Ctrl + H) to find empty cells? Is there any regular expression you can use to select empty cells? Unfortunately, you can't find and replace empty cells using the Ctrl + H method, as there’s no regex that specifically selects empty cells in Google Sheets. You may try regular expressions such as ^
, null
, or even leave the field empty, but none of these will effectively highlight empty cells.
Conclusion
Removing empty cells from your Google Sheets can make your data cleaner and easier to manage. Whether you prefer manual methods like filtering or deleting, formulas, or scripts, there are multiple ways to handle this task efficiently. By choosing the method that best suits your workflow, you can keep your spreadsheets organized and ready for analysis.
If you need further guidance, explore more Google Sheets tips on handling blanks, such as:
Published