VBA & Macro Problems

How to Hide and Show Excel Rows with Empty Cells

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 868 views

Question details

Hide rows containing empty cells in selected columns and show them again when needed.

How to Hide and Show Excel Rows with Empty Cells
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.
Before you start

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.

Solution 1Recommended

Filter out blank cells in one Excel column

Use AutoFilter when one column determines which rows to hide.

1
Select the data

Click a cell in the data range or select the full range, including its headers.

2
Turn on filtering

Open the Data tab and select Filter to add drop-down arrows to the headers.

3
Hide blank rows

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.

4
Show the rows again

Open the same filter drop-down and select Clear Filter From the column, or use Data > Clear to remove the filter criteria.

Filter out blank cells in one Excel column
Manually hidden rows: Clearing a filter does not necessarily unhide rows hidden manually. To reveal those rows, select the surrounding rows and use Home > Format > Hide & Unhide > Unhide Rows.
Filter blank rows in WPS Spreadsheet

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. 1. Open the worksheet: Open WPS Spreadsheet and choose Open to load your Excel file.
  2. 2. Enable the filter: Select the data range with its headers, then choose Data > Filter.
  3. 3. Exclude blanks: Open the filter arrow in the column you want to check, clear the blank-value option, and confirm the selection.
  4. 4. Restore the rows: Open the column's filter menu and clear its filter criteria to show the filtered rows again.
Hide rows based on blank cells without writing a macroOpen and edit Microsoft Excel XLS and XLSX filesClear filter criteria when you need to see the rows againWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.