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

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.


Download Practice Book

You can download the practice book from here.


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.

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

  • 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.

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

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

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

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

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

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

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

Read More: How to Hide Cells in Excel Until Data Entered (2 Easy Ways)


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.

Use Excel Hide Option to Remove Unused Cells

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.

Use Excel Hide Option to Remove Unused Cells

  • 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.

Use Excel Hide Option to Remove Unused Cells

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

Use Excel Hide Option to Remove Unused Cells

  • Then, select Row 11.

Use Excel Hide Option to Remove Unused Cells

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

Use Excel Hide Option to Remove Unused Cells

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

Use Excel Hide Option to Remove Unused Cells

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

Use Excel Hide Option to Remove Unused Cells

Read More: How to Hide Extra Cells in Excel (with Quick Steps)


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.

Insert Filter to Hide Blank Cells of Rows in Excel

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

STEPS:

  • Firstly, select Cell E5 and type the formula below:
=COUNTA(B5:D5)

Insert Filter to Hide Blank Cells of Rows in Excel

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.

Insert Filter to Hide Blank Cells of Rows in Excel

  • 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.

Insert Filter to Hide Blank Cells of Rows in Excel

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

Insert Filter to Hide Blank Cells of Rows in Excel

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

Insert Filter to Hide Blank Cells of Rows in Excel

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

Insert Filter to Hide Blank Cells of Rows in Excel

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

Insert Filter to Hide Blank Cells of Rows in Excel

Read More: How to Hide Cell Contents in Excel Formula Bar (5 Methods)


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.

Hide Blank Cells of Entire Row or Column Using Context Menu

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

STEPS:

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

Hide Blank Cells of Entire Row or Column Using Context Menu

  • As a result, Row 6 will be hidden.

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

  • Also, you can rightclick 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, rightclick 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.

Read More: How to Hide Part of Text in Excel Cells (2 Effective Methods)


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.


Related Article

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo