How to Delete Rows with Blank or Zero Values in Excel
Question details
The user needs to remove rows from their spreadsheet where specific columns contain blank cells, zeros, or non-positive values.

- 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 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.
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.
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.
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'.
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.
Go to Data > Sort & Filter > Clear to remove the filter. Your dataset will now be displayed without the blank or zero-value rows.

Use Find and Replace to Target Zero Values
This method is useful if you have a large dataset and want to quickly locate and delete cells containing exactly zero without setting up filters.
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. Open your Document: Launch WPS Spreadsheet and open the workbook containing your dataset.
- 2. Apply AutoFilter: Highlight your dataset, navigate to the 'Data' tab, and click 'AutoFilter'.
- 3. Select Unwanted Values: Click the filter arrow on your target column, check the boxes for 'Blanks' or '0', and click 'OK'.
- 4. Delete the Rows: Select the visible row headers on the left, right-click, and choose 'Delete'.
- 5. Restore Data View: Click the 'AutoFilter' button again to turn off the filter and reveal your updated, cleaned dataset.

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.




