Document Editing Problems

How to Delete Rows with Blank or Zero Values in Excel

Khadija KhanKhadija Khan Nov 10, 2026 872 views

Question details

The user needs to remove rows from their spreadsheet where specific columns contain blank cells, zeros, or non-positive values.

How to Delete Rows with Blank or Zero Values in Excel
Product
Excel
Device & OS
not provided
Scenario
Cleaning up a dataset by removing irrelevant or incomplete entries, such as missing data or zero values like non-positive market capitalization.
Observed behavior
The user needs a reliable method to safely delete these rows, noting that Excel sometimes treats formulas returning empty strings or spaces differently than truly blank cells.
Before you start

Before making structural changes to your dataset, ensure you understand which columns determine if a row should be deleted, and consider formatting your data as a Table to prevent accidental misalignment.

Solution 1Recommended

Use Data Filters to Remove Blank or Zero-Value Rows

Applying filters is the safest and most efficient way to isolate and delete rows with unwanted values.

Filtering allows you to hide the data you want to keep and only display the rows that meet your criteria for deletion. This minimizes the risk of deleting the wrong information.

1
Enable Filters

Select your dataset or convert it to a table by going to Home > Format as Table. Then, navigate to the Data tab and click the 'Filter' button.

2
Filter for Blanks and Zeros

Click the filter drop-down arrow on the target column header. Uncheck '(Select All)', scroll to the bottom of the list, and check the boxes for '(Blanks)' and '0'.

3
Delete the Visible Rows

Highlight the visible row numbers on the far left side of the spreadsheet. Right-click the highlighted row numbers and select 'Delete Row' or go to Home > Delete > Delete Sheet Rows.

4
Clear the Filter

Go to Data > Sort & Filter > Clear to remove the filter. Your dataset will now be displayed without the blank or zero-value rows.

Use Data Filters to Remove Blank or Zero-Value Rows
True Blanks vs. Empty Strings: If a cell looks blank but isn't filtered as '(Blanks)', it may contain a space or an empty string from a formula. Use the TRIM or CLEAN functions, or Find and Replace, to normalize your data first.
Efficient Data Cleaning

Easily Manage and Clean Data with WPS Spreadsheet

WPS Spreadsheet offers powerful and intuitive filtering tools to help you quickly identify, isolate, and delete rows with blank or zero values, streamlining your data analysis workflow.

  1. 1. Open your Document: Launch WPS Spreadsheet and open the workbook containing your dataset.
  2. 2. Apply AutoFilter: Highlight your dataset, navigate to the 'Data' tab, and click 'AutoFilter'.
  3. 3. Select Unwanted Values: Click the filter arrow on your target column, check the boxes for 'Blanks' or '0', and click 'OK'.
  4. 4. Delete the Rows: Select the visible row headers on the left, right-click, and choose 'Delete'.
  5. 5. Restore Data View: Click the 'AutoFilter' button again to turn off the filter and reveal your updated, cleaned dataset.
Fully compatible with Microsoft Excel (.xlsx, .xls) formats without risking layout shifts.Built-in data filtering and advanced sorting capabilities to clean large datasets easily.Lightweight software with a familiar, user-friendly interface.Free built-in data cleaning tools like Find & Replace and duplicate removal.
microsoft office alternative - wps office

Frequently Asked Questions

Why aren't my blank cells showing up when I filter for blanks?

Cells that appear blank might actually contain hidden spaces, apostrophes, or empty strings returned by formulas (like ""). You can use the TRIM function to remove extra spaces or use Find and Replace to clear these hidden characters before applying your filter.

Can I delete rows based on multiple conditions at once?

Yes. You can apply filters to multiple columns simultaneously. Alternatively, you can use 'Number Filters' within the drop-down menu to set conditions like 'Less than or equal to 0' to catch both blanks (often treated as 0 in mathematical operations) and negative numbers simultaneously.

What is the fastest way to delete all blank rows in a worksheet?

You can press F5 to open the 'Go To' dialog, click 'Special', select 'Blanks', and click OK. Then, right-click any selected blank cell, choose 'Delete', select 'Entire row', and confirm. Be careful with this method, as it will delete the entire row even if only one cell in that row is blank.