📋 Table of Contents
Having gaps in your data is frustrating. When you copy-paste from other sources or import a messy CSV, you often end up with a column full of scattered data separated by blank cells. You don't want to delete the whole row (because other columns might have useful data), you just want to remove the blank cells and shift the remaining data up.
Why Blank Cells Ruin Your Data
- VLOOKUP fails: Functions that search for data will stop or return errors when hitting blanks.
- Chart gaps: Line charts drop to zero or break completely.
- Pivot Table mess: You'll get a massive "(blank)" category ruining your summaries.
Method 1: The FILTER Formula
If you have a column with blanks (e.g., Column A) and want a clean version of it in Column B, the FILTER function is your best friend.
Steps:
- Click on the cell where you want the clean list to start (e.g.,
B2). - Type this formula:
=FILTER(A2:A, A2:A<>"") - Press Enter.
How it works: It takes everything in column A, but only returns the rows where A is not empty (<>""). The data instantly stacks up neatly.
Method 2: SORT Function
Similar to FILTER, you can use the SORT function to push all the blank cells to the bottom.
=SORT(A2:A)This simply alphabetizes the list. Since Google Sheets considers blanks as "larger" than text or numbers in an ascending sort, all your data will float to the top, and blanks will sink to the bottom.
Using SORT changes the original order of your data. If you need the items to stay in their original chronological or imported order, stick to the FILTER method.
Method 3: QUERY Function
If you have multiple columns and want to remove rows where a specific cell is blank, QUERY is incredibly powerful.
=QUERY(A2:D, "SELECT * WHERE A IS NOT NULL")This pulls all data from columns A through D, but skips any row where column A is blank.
Method 4: CleanSheet Auto-Delete (No Formulas)
Formulas are great, but they require creating a *new* column to display the clean data. If you want to clean the data in place without writing formulas, you need an add-on.
CleanSheet allows you to set up visual rules to delete rows based on empty cells.
Steps:
- Open CleanSheet from the Extensions menu.
- Create a Rule: IF Column A is Empty.
- Action: Delete Row.
- Click Run.
Instead of wrestling with formulas, your sheet is cleaned in seconds.
Frequently Asked Questions
How do I remove blank cells without deleting the entire row?
Use the FILTER formula in a new column, e.g. =FILTER(A2:A, A2:A<>""), which pulls only the non-blank values from Column A and stacks them without gaps. This leaves your original row structure untouched since the clean result appears in a separate column.
Why does sorting move blank cells to the bottom instead of removing them?
Google Sheets treats blank cells as "larger" than any text or number in an ascending sort, so SORT() or a manual sort pushes them to the end rather than deleting them. You still need to manually clear or delete those trailing blank entries, or switch to FILTER, which excludes blanks entirely instead of just repositioning them.
Can I delete rows with blank cells automatically without writing a formula?
Yes. CleanSheet lets you create a rule like "IF Column A is Empty → Delete Row" through a visual interface, so the row is removed in place instead of requiring a helper column formula like FILTER or QUERY.
Related Articles
- How to Delete Empty Rows in Google Sheets Automatically (2026 Guide)
- How to Automatically Hide Empty Columns and Rows in Google Sheets
- Google Sheets Row Cleanup: The Complete Automation Guide
🚀 Automate Your Cleanup
Stop writing complex formulas just to remove blanks. Clean your sheets visually.
Install CleanSheet Free