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

Sometimes, in Excel, we need to hide cells or hide entire rows or columns. We want to input data into those cells later. Or sometimes, we have to hide the contents of the cells. In this article, you will see how to hide cells in Excel until data is entered.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


2 Handy Approaches to Hide Cells in Excel Until Data Entered

In this article, we will use the data set given below. Here we have some information regarding some people. Among this information, some are numeric and some are of textual value. We will demonstrate two different methods to accomplish our task. In our first procedure, we will change the cell format of some particular cells, and in our second one, we will apply VBA to hide rows and columns of the data set, respectively.

Handy Approaches to Hide Cells in Excel Until Data Entered

1. Changing Cell Format to Hide Cells in Excel Until Data Entered

In our first approach, we will hide the cells in our data set by changing the cell format. To do that, execute the following steps.

Step 1:

  • First of all, select the cells that you want to hide.

Handy Approaches to Hide Cells in Excel Until Data Entered

Step 2:

  • Secondly, right-click on the cells after selecting them and then choose the Format Cells command.

Handy Approaches to Hide Cells in Excel Until Data Entered

Step 3:

  • Then, you will see a dialogue box named Format Cells.
  • From that box, choose the Custom command under the Number tab.

Handy Approaches to Hide Cells in Excel Until Data Entered

  • Then, after choosing the command, type three semicolons (;) in the Type box.
  • Finally, press OK.

Handy Approaches to Hide Cells in Excel Until Data Entered

Step 4:

  • Finally, after typing, you will find the selected cells in the data set empty.

Handy Approaches to Hide Cells in Excel Until Data Entered

  • Furthermore, if you want to unhide the cells, then select the Format Cells… command again.
  • Then, choose the original number format of the data.
  • Here, it is in the Text format in our example.
  • Then, press OK.

Handy Approaches to Hide Cells in Excel Until Data Entered

  • Finally, you will see the data in the cells again.

Handy Approaches to Hide Cells in Excel Until Data Entered

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


2. Applying VBA to Hide Cells in Excel

In our previous approach, we hid some cells in our data set by changing the cell format. In our second method, we will apply VBA to hide an entire row or column based on their cell values.

2.1 Inserting Cell Text Value to Hide Rows Automatically

In this approach, we will hide an entire row automatically by applying VBA. To hide the row, we will specify a text value from the data set in the code. For a better understanding, go through the following steps.

Step 1:

  • Firstly, take the following data set for applying VBA.

Handy Approaches to Hide Cells in Excel Until Data Entered

  • Then, from the data set, choose any specific text value from any cell.
  • For example, we will choose the name Jack which is in row 7.

Handy Approaches to Hide Cells in Excel Until Data Entered

Step 2:

  • Secondly, choose the Visual Basic command from the Developer tab of the ribbon.

Handy Approaches to Hide Cells in Excel Until Data Entered

  • Then, choose the Module command from the Insert tab in the pop-up window.

Handy Approaches to Hide Cells in Excel Until Data Entered

  • Then, copy the following code and paste it into the module.
'Set the name of the function
Sub Inserting_Text_Value_to_Hide_Row()
'Declaring the first row of the data set
StartRow = 4
'Declaring the last row of the data set
LastRow = 10
'Addressing the column number that holds the text value
iCol = 2
'Finding the specific word of the above from first row to last row of the data set and hide the row
For i = StartRow To LastRow
'Start to find the specific word Jack from the adressed row by looping. If the word is not found, then the rows wont'be hidden
If Cells(i, iCol).Value <> "Jack" Then
Cells(i, iCol).EntireRow.Hidden = False
'If the word is found in the specific column then the rows will be hidden
Else
Cells(i, iCol).EntireRow.Hidden = True
End If
Next i
End Sub

Handy Approaches to Hide Cells in Excel Until Data Entered

VBA Breakdown

We will use the function name of the VBA as Inserting_Text_Value_to_Hide_Row.

Then, we will address the first row which is StartRow= 4 because we can find our data set from row 4.

After that, we will set the last row of the data set which is LastRow=10 as we can our last data in row 10.

Next, we will give the number of columns in which the text value is in by iCol=2.

Then, with the IF fucntion, the code starts to look for the word “Jack” from the addressed first row (4) to the last row (10) of the data set. Now the loop variable i finds the word “Jack” in any row of the declared column (B), then hides the entire row from the data set automatically. It continues to loop until it searches in all the rows.

Finally, with the ELSE function the code will assure that, if the specific word is not found then, any row will not be hidden.

Step 3:

  • Thirdly, save the code and press the play button to run.

Handy Approaches to Hide Cells in Excel Until Data Entered

  • Consequently, you will see that row 7, containing the word Jack has been hidden.

Handy Approaches to Hide Cells in Excel Until Data Entered

Step 4:

  • Furthermore, if you want to show the specific row again, then click on between rows 6 and 8.

Handy Approaches to Hide Cells in Excel Until Data Entered

  • Then again right-click on the selection and choose the Unhide command.

Handy Approaches to Hide Cells in Excel Until Data Entered

  • Finally, you will see the row again.

Handy Approaches to Hide Cells in Excel Until Data Entered

2.2 Inserting Cell Numeric Value to Hide Columns Automatically

We can also hide an entire column based on a cell value by applying VBA similar to the previous method. In this approach, we will insert a numeric value from the data set into the code to hide the column. Follow the below steps to understand the process.

Step 1:

  • First of all, we will select a random numerical value from the data set.
  • For our working purpose, we will take the numerical value 24 which is in column C.

Sample Data Set

Step 2:

  • Secondly, go to the Developer tab of the ribbon and choose the Visual Basic command.

Sample Data Set

  • Then, copy the following code and paste it into the Module tab like in the previous procedure.
'Address the function name
Sub Inserting_Numeric_Value_to_Hide_Column()
'Declare the first column, last column and the row number that holds the numeric value in the data set
StartColumn = 2
LastColumn = 5
iRow = 7
'Finding the specific number from first column to last column of the data set and hide the column
For i = StartColumn To LastColumn
If Cells(iRow, i).Value <> "24" Then
Cells(iRow, i).EntireColumn.Hidden = False
Else
Cells(iRow, i).EntireColumn.Hidden = True
End If
Next i
End Sub

Sample Data Set

VBA Breakdown

We will use the function name of the VBA as Inserting_Numeric_Value_to_Hide_Column.

Then, we will address the first column which is StartColumn= 2 because we can find our data set from column 2.

After that, we will set the last row of the data set which is LastColumn= 5 as we can our last data in row 5.

Next, we will give the number of row in which the numeric value is in by iRow=7.

Then, with the IF fucntion, the code starts to look for the number “24” from the addressed first column (2) to the last column (5) of the data set. Now the loop variable i finds the number “24” in any column of the declared row (7), then hides the entire column from the data set automatically. It continues to loop until it searches in all the columnc.

Finally with the ELSE function the code will assure that, if the specific number is not found then, any column will not be hidden.

Step 3:

  • Thirdly, save the code and press F5 or the play button.

Sample Data Set

  • Consequently, you will find column C hidden from the data set in which you have seen the numerical value 24.

Sample Data Set

Step 4:

  • Furthermore, to unhide the column, click in between columns C and D.

Sample Data Set

  • Then, right-click on the selection and choose the Unhide command.

Sample Data Set

  • Therefore, the column will be visible in the data set again.

Sample Data Set

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


Conclusion

That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to hide cells in Excel until data entered by using any of the methods. Please share any further queries or recommendations with us in the comments section below.


Related Articles

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo