Remove Empty Rows: How to Delete Them

To remove empty rows from a spreadsheet or dataset, you can use filtering, sorting, or a dedicated delete function, depending on your tool. This article explains the most common methods for Excel, Google Sheets, and other data tools, and how to avoid accidentally deleting data you need.

Why Empty Rows Cause Problems

Empty rows in a spreadsheet or dataset can break formulas, skew charts, and make data analysis unreliable. For example, if you have a column of numbers and a blank row in the middle, a simple SUM function will ignore the blank, but a chart may show a gap, and pivot tables may not include the rows below the blank. In databases, empty rows can cause errors during import or processing. Removing them keeps your data clean and your calculations accurate.

How to Remove Empty Rows in Excel

Microsoft Excel offers several ways to delete blank rows. The method you choose depends on whether the blanks are scattered or in a specific area.

Using the Go To Special tool: Select the range of cells, press F5 or Ctrl+G, click Special, choose Blanks, and click OK. This selects all blank cells in the range. Then, right-click any selected blank cell and choose Delete > Entire row. This works well for small datasets but can be slow for very large ones.
Using a filter: Select the entire data range, go to the Data tab, and click Filter. Click the dropdown arrow in a column that has blanks in the rows you want to remove, uncheck Select All, and check Blanks. This filters to show only rows with blanks in that column. Select those visible rows, right-click, and choose Delete row. Remove the filter to see the remaining data.
Using a helper column: Add a new column at the end of your data. In the first cell, enter a formula like =COUNTA(A2:Z2) (adjust the range to cover all columns in that row). Copy the formula down. This counts non-empty cells in each row. Then filter on that column to show only rows where the count is 0, and delete those rows. This is the safest method for large datasets because it doesn’t rely on selecting blank cells.

After deleting, always check that no data was lost by verifying the row count before and after.

How to Remove Empty Rows in Google Sheets

Google Sheets has similar methods, but the interface differs slightly.

Using the filter: Select your data range, go to Data > Create a filter. Click the filter icon in a column header, choose Clear to deselect all, then select Blanks to show only rows with blanks in that column. Select those rows, right-click, and choose Delete rows. Turn off the filter.
Using a helper column: Add a column with a formula like =COUNTA(A2:Z2) (adjust the range). Then use the filter to show rows where the helper column equals 0, and delete them.
Using the “Remove empty rows” add-on: Google Sheets has a built-in feature in the Data menu called Data cleanup > Remove empty rows. This removes rows that are completely empty (all cells in the row are blank). It does not remove rows with partial data.

For a quick cleanup, the built-in tool is the easiest, but it only removes rows where every cell in the selected range is blank. If you have rows with some data but many blanks, use the helper column method.

Removing Empty Rows in Other Tools (Python, R, SQL)

If you work with data in programming languages or databases, there are specific commands to drop empty rows.

Python (pandas): Use df.dropna(how='all') to remove rows where all columns are NaN. To remove rows with any NaN, use df.dropna(). You can also specify a subset of columns: df.dropna(subset=['col1', 'col2']).
R (tidyverse): Use filter_all(all_vars(!is.na(.))) to keep rows with no NA in any column. For rows with all NA, use filter_all(any_vars(!is.na(.))).
SQL: There is no direct “remove empty rows” command, but you can delete rows where all columns are NULL: DELETE FROM table WHERE col1 IS NULL AND col2 IS NULL AND ... This requires listing all columns.

In all cases, be careful to define what “empty” means: a truly blank cell, a cell with a space, or a cell with a formula that returns an empty string. These are different and may require different handling.

Common Mistakes and How to Avoid Them

Removing empty rows seems simple, but mistakes happen often.

Deleting rows with hidden data: A row may look empty but contain formulas that return “” or spaces. Use the COUNTA method to detect these, or use a find-and-replace to remove spaces before deleting.
Selecting the wrong range: If you select a range that includes columns to the right of your data, you might delete rows that have data in those extra columns. Always select the exact range of your dataset.
Not making a backup: Before deleting any rows, save a copy of your file. This is the safest practice, especially for large or important datasets.
Using “Delete” instead of “Delete row”: In Excel, if you select blank cells and press Delete, it only clears the cells, not the entire row. You must use Delete > Entire row to remove the row itself.

How to Choose the Right Method

The best method depends on your data and your comfort level with the tool.

Small datasets (under 100 rows): Manual selection or the Go To Special method in Excel is quick and fine.
Large datasets (thousands of rows): Use a helper column with COUNTA and filter, as it is fast and less error-prone.
Data with partial blanks: If you want to remove rows that have any blank cell, use the filter method on a specific column, or use the dropna function in Python.
Data with completely blank rows only: Use the built-in “Remove empty rows” tool in Google Sheets, or the dropna(how=’all’) in pandas.

Always test your method on a copy of the data first if you are unsure.

Alternatives to Deleting Rows

Sometimes you don’t want to permanently delete rows, but rather hide or ignore them.

Filtering: Use a filter to hide blank rows temporarily. This is non-destructive and useful for visual inspection.
Using a table: Convert your range to an Excel Table (Ctrl+T) or Google Sheets table. Tables automatically expand and contract, and you can filter out blanks easily.
Using a pivot table: Pivot tables ignore blank rows by default, so you can analyze data without removing them.
Conditional formatting: Highlight blank rows instead of deleting them, so you can see them but not lose data.

Frequently Asked Questions

How do I remove all empty rows in Excel at once?

Select your data range, press F5 > Special > Blanks, then right-click a selected blank cell and choose Delete > Entire row. This removes all rows that have any blank cell in the selected range. For rows that are completely empty, use the COUNTA helper column method.

Why does my filter not show blank rows?

If your filter does not show blank rows, it may be because the cells contain spaces or formulas that return empty strings. To fix this, use a find-and-replace to replace spaces with nothing, or use a formula like =TRIM(A2) to remove extra spaces. Then reapply the filter.

Can I undo removing empty rows?

Yes, in Excel and Google Sheets, you can press Ctrl+Z (Cmd+Z on Mac) immediately after deleting rows to undo the action. However, if you close the file or perform other actions, you may not be able to undo. Always save a backup before deleting.

How do I remove empty rows in Excel without affecting other columns?

Use the helper column method: add a column with =COUNTA(A2:Z2), filter on that column to show 0, and delete those rows. This only deletes rows where all specified columns are empty, leaving rows with data in any of those columns intact.

What is the difference between removing empty rows and removing blank cells?

Removing empty rows deletes entire rows that have no data. Removing blank cells shifts data up or left, which can misalign your data. You should only remove blank cells if you intentionally want to compact data, but this is rarely recommended because it destroys relationships between rows.

Does removing empty rows affect formulas?

Yes, if your formulas reference a range that includes the deleted rows, they will automatically adjust to the new range. However, if a formula directly references a specific cell that is deleted, it will return a #REF! error. Always check formulas after deleting rows.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →