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.
How to Hide Blank Cells in Excel: 5 Easy Ways
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.
Read More: How to Hide Extra Cells in Excel
2. Use Excel Hide Option to Remove Unused Cells
In the second method, we will use the hide option to remove or hide 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.
Read More: How to Hide Confidential Data in Excel
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.
Download Practice Book
You can download the practice book from here.
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. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
