Hide or Unhide Columns Based on Drop Down List Selection in Excel

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates several examples to hide or unhide columns in Excel based on drop-down list selection. We’ll use VBA code to first set conditions to select columns and then hide or unhide them based on those conditions. Let’s dive into the examples to get a clear understanding of the whole procedure.


How to Hide or Unhide Columns Based on Drop Down List Selection in Excel: 2 Examples

At first, we need to create a drop-down list with the help of the dataset. The drop-down list would hold the options based on which we’ll hide or unhide columns. Let’s say we have a list of active and inactive members of a sports club. We want to make a drop-down list with two options- active and inactive and then hide and unhide those columns based on the selection.

Hide or Unhide Columns Based On Drop Down List Selection in Excel

Create a Drop-Down List in Excel:

To make a drop-down list, just follow the simple steps below-

  • Select a cell (E3, in this example) on which we’ll create the drop-down list.

  • Then go to the Data tab of the Excel Ribbon.
  • Click on the Data Validation option.

  • In the Data Validation window, select the Setting tab (By default get selected).
  • In the Allow drop-down list, choose the List option.

  • Then type Active and Inactive in the Source input box and finally hit OK.

  • As an output, we can see a drop-down list in cell E3 with two options to select- Active and Inactive.

Hide or Unhide Columns Based On Drop Down List Selection in Excel


Example 1: Hide or Unhide Columns Based On Drop Down List Selection in Excel

In this example, we’ll work on the dataset we discussed earlier to filter the active and inactive members by using the drop-down list. On the selection of the Active option, we want to hide the column with inactive members and vice versa. To do that let’s follow the steps described below-

  • Right-click on the sheet name and select the View Code option.

Hide or Unhide Columns Based On Drop Down List Selection in Excel

  • Then insert the following code in the visual code editor.
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = ("$E$3") Then
        If Target.Text = "Active" Then
            Columns("C").EntireColumn.Hidden = True
            Columns("B").EntireColumn.Hidden = False
        ElseIf Target.Text = "Inactive" Then
            Columns("C").EntireColumn.Hidden = False
            Columns("B").EntireColumn.Hidden = True
        End If
    End If
End Sub

Hide or Unhide Columns Based On Drop Down List Selection in Excel

  • Save the code by pressing Ctrl + S and then close the code editor.
  • Now on the worksheet, to hide the active members’ column e., keeping only the inactive members’ column, choose the Inactive option from the drop-down list.

  • Again, we selected the Active option from the drop-down list.

  • This time the column with active members appeared and the column with inactive members got hidden.

Hide or Unhide Columns Based On Drop Down List Selection in Excel

Code Explanation:

In our code,

  • we used the EntireColumn property to select the entire column with active and inactive members.
  • Then we set the .hidden property to True or False to hide a specific column.

Read More: How to Remove Used Items from Drop Down List in Excel


Example 2: Hide or Unhide Columns to Filter Data Based On Drop Down List Selection in Excel

In this example, we’re going to filter a sale list based on category selection. The sale list contains sales data for 7 products from 3 different categories i.e., Fruit, Vegetable, and Fish.

Hide or Unhide Columns Based On Drop Down List Selection in Excel

We want to hide all other columns except the columns with the selected category in the drop-down. In addition, all the columns will be visible when we’ll select the All option from the drop-down. To accomplish the task let’s follow the steps below.

  • In cell B2, create a drop-down list with 4 options- All, Fruit, Vegetable, and Fish. For this follow the steps in the create a drop-down list in Excel section that is described earlier in the article.

Hide or Unhide Columns Based On Drop Down List Selection in Excel

  • Now to open the Visual Code Editor, right-click on the sheet name and choose the View Code option.

Hide or Unhide Columns Based On Drop Down List Selection in Excel

  • Insert the following code into the editor.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim a As Variant, b As String
     If Target.Column = 2 And Target.Row = 2 Then
        b = Target.Value2
        With Range("C5:I5")
            Application.ScreenUpdating = False
            .EntireColumn.Hidden = (b <> "All")
            If b <> "All" Then
                For Each a In .Cells
                    If a = b Then a.EntireColumn.Hidden = False
                Next
            End If
            Application.ScreenUpdating = True
        End With
    End If
End Sub

Hide or Unhide Columns Based On Drop Down List Selection in Excel

  • Save the code by pressing Ctrl + S and then close the code editor.
  • Now our dataset is filterable based on the category we select from the drop-down list. The following screenshots show the outputs.

The first image is the list for the Fruit category.

Hide or Unhide Columns Based On Drop Down List Selection in Excel

Then we choose the Vegetable category.

Hide or Unhide Columns Based On Drop Down List Selection in Excel

The next image shows the Fish category list.

Hide or Unhide Columns Based On Drop Down List Selection in Excel

Finally, we choose all the categories.

Hide or Unhide Columns Based On Drop Down List Selection in Excel

Code Explanation:

  • We selected the target cell B2 using the following line of code defining its column and row number. We did it differently in example 1 using the Address property.
   If Target.Column = 2 And Target.Row = 2 Then
  • The variable b holds the value of the selected category in the drop-down.
  • The following code defines the range of cells containing category names in the sale list. Each of the values is matched against the variable b.
 With Range("C5:I5")
  • If the value of b matches with one of the values of Range(“C5:I5”), the code selects the entire column associated with the cell and keeps it visible by applying the Hidden property to False.

Read More: How to Remove Duplicates from Drop Down List in Excel


Things to Remember

In the VBA code, we set the Application.ScreenUpdating = False before starting the loop and again changed to Application.ScreenUpdating = True after finishing the loop to get a faster response while changing the selection in the drop-down list.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

Now, we know how to hide or unhide columns in Excel based on drop-down list selection with 2 well-explained examples. Hopefully, it’ll certainly help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles


<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

2 Comments
  1. Can you make the column be hidden across the entire workbook? And if the drop down is duplicated on every sheet, can the value selected on one sheet persist to each sheet in the book?

    • Reply Avatar photo
      Osman Goni Ridwan Aug 30, 2022 at 12:29 PM

      Hello M LAVI! with this code, only the column of the active cell that contains the target cell value will be hidden.
      And, you can copy and paste the drop-down cell to other worksheets without assigning data validation again. If you have anything more to know then inform us in a comment!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo