How to Hide and Show Excel Rows with Empty Cells
Question details
Hide rows containing empty cells in selected columns and show them again when needed.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to review a large worksheet without displaying rows that have empty cells in relevant columns.
- Observed behavior
- Rows with empty cells in the selected columns are hidden temporarily and can be displayed again.
Identify the column or columns to check and make sure the data range has a header row. A filter hides entire worksheet rows based on the selected criteria, so check that the range includes all related columns.
Filter out blank cells in one Excel column
Use AutoFilter when one column determines which rows to hide.
Click a cell in the data range or select the full range, including its headers.
Open the Data tab and select Filter to add drop-down arrows to the headers.
Open the filter drop-down for the column to check, clear the (Blanks) checkbox, and select OK. Rows with blank values in that column are hidden.
Open the same filter drop-down and select Clear Filter From the column, or use Data > Clear to remove the filter criteria.

Use a helper column to check several Excel columns
Use this method to hide a row when any cell in a specified group of columns is blank.
Hide and Show Rows with Empty Cells in WPS Spreadsheet
Use the filter in WPS Spreadsheet to hide rows with blank values in a chosen column, then clear the filter to display them again.
- 1. Open the worksheet: Open WPS Spreadsheet and choose Open to load your Excel file.
- 2. Enable the filter: Select the data range with its headers, then choose Data > Filter.
- 3. Exclude blanks: Open the filter arrow in the column you want to check, clear the blank-value option, and confirm the selection.
- 4. Restore the rows: Open the column's filter menu and clear its filter criteria to show the filtered rows again.

Frequently Asked Questions
How do I show rows hidden by an Excel filter?
Open the filtered column's drop-down and select Clear Filter From the column. You can also use Data > Clear to remove filter criteria from the range.
Can I hide a row if any of several cells are blank?
Yes. Add a helper column with a formula such as =COUNTBLANK(A2:C2)>0, fill it down, and filter the helper column to show FALSE.
Why are some rows still hidden after I clear the filter?
They may have been hidden manually. Select the rows surrounding the hidden rows, then choose Home > Format > Hide & Unhide > Unhide Rows.
Does COUNTBLANK treat a formula returning an empty string as blank?
Yes. COUNTBLANK counts cells whose formulas return an empty string, but it does not count cells containing spaces as blank.




