How to Hide Blank Cells in Excel (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn to hide blank cells in Excel. Unfortunately, you can’t hide an individual blank cell in Excel. You will have to hide the whole column or row if it contains any blank cells. Today, we will demonstrate 5 easy ways. Using these methods, you can easily hide blank cells in Excel. So, without any delay, let’s start the discussion.

Table of Contents hide

Download Practice Book

5 Easy Ways to Hide Blank Cells in Excel

1. Hide Rows or Columns Containing Blank Cells with Go To Special Feature

2. Use Excel Hide Option to Remove Unused Cells

3. Insert Filter to Hide Blank Cells of Rows in Excel

4. Hide Blank Cells of Entire Row or Column Using Context Menu

5. Apply Excel VBA to Auto Hide Rows or Columns Based on Blank Cells

Conclusion

Download Practice Book

You can download the practice book from here.

Hide Blank Cells.xlsm

5 Easy Ways to Hide Blank Cells in Excel

To explain the methods, we will use a dataset that contains information about the sales amount of some sellers. In the dataset, we have some blank cells. As we can’t hide the blank cells individually, we will try to hide the row or column that contains the blank cell.

1. Hide Rows or Columns Containing Blank Cells with Go To Special Feature

In the first method, we will use the Go To Special feature to hide rows or columns that contain blank cells. This process is simple. Let’s follow the steps below to see how we can hide rows or columns that contain blank cells.

STEPS:

First of all, select any cell in your dataset and press Ctrl + A to select all used cells.

Secondly, go to the Home tab and click on Find & Select. A drop-down menu will appear.
Select Go To Special from there. It will open the Go To Special window.

In the Go To Special window, select Blanks and click OK to proceed.

Finally, press Ctrl + 9 to hide Rows 6, 8, and 10.

To hide columns that contain blank cells, press Ctrl + 0.

2. Use Excel Hide Option to Remove Unused Cells

In the second method, we will use the hide option to remove unused cells in Excel. Here, we will use a dataset that doesn’t contain any blank cells. For this reason, we will remove all the cells except the range A1:E10.

So, let’s pay attention to the steps below to learn the method.

STEPS:

In the beginning, select Column F.
Press Ctrl + Shift + Right Arrow to select all the columns from Column F.

Secondly, go to the Home tab and click on the Format option. A drop-down menu will appear.
Select Hide & Unhide and then, select Hide Columns.

After hiding columns, the dataset will look like the picture below.

Then, select Row 11.

In the following step, press Ctrl + Shift + Down Arrow to select all the rows from Row 11.

Once again, navigate to the Home tab and select Format. A drop-down menu will appear.
Select Hide & Unhide and then, select Hide Rows.

Finally, you will be able to remove the unused cells except for the range A1:E10.

3. Insert Filter to Hide Blank Cells of Rows in Excel

We can also insert the Filter option to hide blank cells of rows in Excel. Usually, we apply filters to extract the desired data easily. But we can also use filters to hide blank cells. To describe the method, we will use a dataset that contains blank cells in Rows 6 and 7. In this case, we will also use the COUNTA function.

Let’s observe the steps below to learn the technique.

STEPS:

Firstly, select Cell E5 and type the formula below:

=COUNTA(B5:D5)

This formula counts the number of non-empty cells in the range B5:E5.

In the second step, press Enter and drag the Fill Handle down.

After dragging the Fill Handle down, the dataset will look like the picture below. You can see Rows 6 and 8 contain 0 non-empty cells.

In the following step, go to the Home tab and select Sort & Filter. A drop-down menu will appear.
Select Filter from there.

As a result, you will see filters in the headers of the dataset.

Then, click on the drop-down arrow of Blank Cells to open the menu and uncheck 0 from there.
Click OK to proceed.

Finally, you will see Rows 6 and 8 are hidden.

4. Hide Blank Cells of Entire Row or Column Using Context Menu

Another way to hide cells of an entire row or column is to use the Context Menu. It is a manual process. Suppose, our dataset has blank cells in Rows 6, 8, and 9. In that case, we can use the Context Menu to hide Rows 6, 8, and 9.

Let’s follow the steps below to learn the whole method.

STEPS:

First of all, select Row 6.
Secondly, right–click on the row number to open the Context Menu.
Select Hide from there.

As a result, Row 6 will be hidden.

You can repeat the above steps to hide Rows 8 and 10.

Also, you can right–click on the column number to open the Context Menu.
Select Hide from there.

Finally, you can also hide Column C.

5. Apply Excel VBA to Auto Hide Rows or Columns Based on Blank Cells

In Excel, VBA gives users the opportunity to do many difficult tasks easily. We can also use a VBA code to auto hide rows or columns based on cells in Excel. Suppose, our dataset doesn’t contain any blank cells. After the application of the VBA code, if you create a blank cell, then the whole row will be hidden.

We will discuss more in the following steps. So, let’s pay attention to the steps below to learn this method.

STEPS:

In the first place, right–click on the name of the sheet to open the Context Menu.
Select View Code from there. It will open the Code window.

In the Code window, type the VBA code below:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim val As Range
    Application.ScreenUpdating = False
        For Each val In ActiveSheet.UsedRange
            If val.Value = "" Then
                val.EntireRow.Hidden = True
            End If
        Next val
    Application.ScreenUpdating = True
End Sub

In this case, if we insert a blank cell, the entire row containing the blank cell will be hidden. For example, if we make Cell C7 blank then, the VBA code will hide the entire Row 7.

Press Ctrl + S to save the code.
Now, remove cell contents from Cell C7.

Finally, press Enter to see results like the picture below.

Note: If you run the VBA code, then you can’t undo the changes. That is why you should also know how to unhide cells in Excel.

Conclusion

In this article, we have discussed 5 easy methods to Hide Blank Cells in Excel. I hope this article will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. You can also visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.