Excel Hide Columns Based on a Drop-Down List Automatically

To hide Excel columns when someone chooses an option from a drop-down list, create a Data Validation list and use a short VBA worksheet-change macro to hide or show the relevant columns. A formula by itself cannot change column visibility, and the macro works in desktop Excel—not Excel for the web.

How the drop-down controls column visibility

Excel’s drop-down list and its column-hiding feature are separate tools. Data Validation lets a person choose a value in a cell; VBA responds to that change and updates the worksheet’s column visibility. For example, a report might offer “Summary,” “Details,” and “All.” Choosing Summary can hide detail columns, while choosing All restores them.

This is useful when a sheet contains different levels of information, such as a compact dashboard and supporting calculations. It does not delete or filter the hidden data: the columns remain in the workbook and can be shown again. Because hiding columns changes the view for everyone using that workbook, this approach is best for a shared presentation or working sheet where a consistent view is acceptable.

The setup below uses cell B2 for the choice, columns C:E for one group, and F:H for another. Adjust those references to match your own layout. The choices are stored in J2:J4 so the list source is easy to maintain.

Create the drop-down list

Enter Summary, Details, and All in cells J2, J3, and J4. These can be on the same sheet as the report; keep the helper cells visible or put them in a separate, clearly labeled area.
Select B2, the cell where the user should make a choice.
On the ribbon, choose Data > Data Validation > Data Validation. In the Settings tab, set Allow to List.
In Source, enter =$J$2:$J$4, make sure In-cell dropdown is selected, and choose OK.
Select an initial value in B2, such as Summary. The macro will respond when that value is changed.

If your list is on another worksheet, a named range is a reliable way to refer to it. Create a workbook name for the cells containing the choices, then use that name as the Data Validation source. Excel’s list validation has restrictions on direct references to other sheets, so do not assume a plain cross-sheet cell reference will work in every setup. You can also type a short list directly in Source, but a cell range is easier to update and avoids separator differences between regional settings.

Add the VBA event macro

The code belongs in the code module for the worksheet containing B2—not in a standard module. In Windows Excel, right-click that sheet’s tab, choose View Code, and paste the procedure into the worksheet code window. It checks whether B2 changed, then applies the matching visibility setting.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim choice As String
    Dim previousEvents As Boolean

    'Ignore edits that do not include the drop-down cell.
    If Intersect(Target, Me.Range("B2")) Is Nothing Then Exit Sub

    previousEvents = Application.EnableEvents
    On Error GoTo CleanUp
    Application.EnableEvents = False

    choice = CStr(Me.Range("B2").Value)

    Select Case choice
        Case "Summary"
            Me.Columns("C:E").Hidden = True
            Me.Columns("F:H").Hidden = False
        Case "Details"
            Me.Columns("C:E").Hidden = False
            Me.Columns("F:H").Hidden = True
        Case "All"
            Me.Columns("C:H").Hidden = False
    End Select

CleanUp:
    Application.EnableEvents = previousEvents
End Sub

When B2 is Summary, the procedure hides C:E and shows F:H. When it is Details, it shows C:E and hides F:H. All shows every column from C through H. The code does not change columns A or B, so the selector remains available. If the value is not one of the three listed choices, the Select Case statement makes no visibility change.

Application.EnableEvents is turned off while the macro changes the sheet, then restored to its previous setting. This helps prevent another event from firing during the procedure. If you change the cell and nothing happens, check that the code is in the correct sheet module and that macros are enabled.

Save and use the workbook

Save the file as an Excel Macro-Enabled Workbook (.xlsm) so the VBA code is retained. A standard .xlsx file cannot store VBA macros; if you save the workbook in that format, Excel warns that the macro will be removed. When the file is reopened, the user may need to enable macros, subject to the organization’s security settings.

After saving, use B2’s arrow to choose each option and confirm that the intended columns appear. Test All as well as the other choices, since restoring every column is important when a workbook opens in a previously saved view. Save the workbook in the view you want readers to see first. The hidden-column state is part of the worksheet’s saved state, not a private view that automatically changes for each individual.

Adjust the example for a real report

Change the cell and column references consistently. If your selector is D1, replace both references to B2 in the code with D1. If Summary should hide only columns G:J, change the Summary line to hide G:J and decide explicitly whether other columns need to be shown. The All option should unhide every column controlled by the choices, not necessarily every column on the entire worksheet.

For more choices, add the labels to the validation source and add a matching Case for each label. For instance, a “Manager” option could show columns C:F and hide G:H, while an “Analyst” option could do the reverse. Decide what each option should do to every relevant column group; otherwise, a column hidden by a previous choice may stay hidden simply because the new case never shows it.

Keep the drop-down cell outside the columns that the macro hides.
Use labels that match exactly between the list and the Case statements, including spaces.
Keep helper values out of the columns being toggled, or the source list may become hidden from users.
Test with the workbook’s normal protection settings. Worksheet protection can prevent VBA from changing column visibility unless protection is configured appropriately.

What formulas and other features can—and cannot—do

A formula can return a different result when a selection changes, but formulas do not set a column’s Hidden property. Conditional Formatting can change cell appearance, but it cannot make entire worksheet columns disappear. If you want a formula-driven view of selected records rather than physically hiding columns, a FILTER formula may be a better fit in Microsoft 365 or Excel 2021 and later. That produces a separate filtered result; it does not hide the original worksheet columns.

Excel’s outline controls are another no-code option. Select the columns, then choose Data > Group to add expand and collapse controls. The user can show or hide that group manually, but a Data Validation choice does not automatically operate the outline. Standard filtering is also different: filters hide rows that do not match criteria, not columns. These distinctions matter when a request says “hide” but the underlying goal is actually to display a changing subset of data.

Limitations and common problems

Worksheet_Change runs when a cell’s contents are edited, including when a user selects a different validation value. It does not run merely because a formula recalculates. Therefore, if B2 contains a formula whose result changes, this event macro will not react to the recalculation; a different event design or another approach is needed. The example also responds to B2 only, so changing a different selector cell has no effect.

Excel for the web can open macro-enabled workbooks, but it cannot run or edit VBA macros. The drop-down may still be present, but selecting an option in the browser will not trigger this VBA behavior. Microsoft Excel for Mac supports VBA, although the ribbon and steps for opening the Visual Basic Editor can differ from Windows. The code shown is worksheet VBA and is intended for desktop Excel; test it in the version and environment where the workbook will be used.

Column hiding is a display convenience, not data security. A user can unhide columns, inspect formulas, or copy workbook contents if they have access to the file. Do not use hidden columns to protect confidential values. Also, macros may be blocked by company policy, downloaded-file security, or the user’s trust settings, so confirm that the intended recipients can run the workbook’s code.

Alternatives when macros are not suitable

If recipients use Excel for the web or cannot enable macros, consider keeping all columns visible and using a formula-based summary area, separate report sheets, or a PivotTable with suitable fields. Those options can make a report easier to read without relying on an event macro. If users are comfortable changing the view themselves, grouping columns provides a built-in manual expand-and-collapse control.

For a workbook used only by desktop Excel, VBA is the direct way to make a drop-down choice automatically hide columns. For a workbook shared across mixed platforms, prefer an approach that does not depend on VBA, or clearly tell users that the automatic behavior requires desktop Excel with macros enabled.

Frequently Asked Questions

Can Excel hide columns with a formula?
No. A formula can calculate values based on a selection, but it cannot change whether a worksheet column is hidden. Use VBA for automatic column visibility, or choose a formula-based display that returns results in another area.

Will the drop-down macro work in Excel for the web?
No. Excel for the web does not run VBA event macros. The workbook’s validation list may be available, but changing it in the browser will not run this code.

Why does choosing an option not hide the columns?
Check that the macro is in the worksheet module for the sheet containing the selector, that B2 is the actual selector cell, that the option text matches the Case labels, and that macros are enabled. Also confirm that the file is saved as .xlsm.

Can I make the selection show different column groups?
Yes. Add each option to the validation list and give it a corresponding Case in the VBA procedure. In each case, specify which groups to hide and which to show so a previous selection does not leave unwanted columns hidden.

Does hiding columns keep their data private?
No. Hiding changes what is currently displayed, not who can access the data. Anyone with access to the workbook may be able to unhide or inspect those columns.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.