Hide Columns Based on Cell Values in Excel Automatically

Yes. In Excel for Microsoft 365 on Windows, use a worksheet VBA event to hide or show a column when a chosen cell changes; for example, the code below hides column C when B1 contains “Hide.” A normal worksheet formula or conditional-formatting rule cannot directly hide a column.

What Excel Can—and Cannot—Do

Excel lets you hide a column manually, but it does not have a built-in conditional-formatting setting that automatically hides columns based on a cell’s contents. Conditional formatting changes the appearance of cells, such as their fill color or font; it does not change whether a whole column is visible. A formula can calculate a result in a cell, but it cannot directly change the column’s Hidden property.

For automatic behavior, VBA is the usual desktop Excel solution. An event macro watches for a relevant edit or recalculation and then changes the column’s visibility. This is useful for presentation sheets, forms, and reports where a control cell determines which fields a reader should see.

There is an important distinction: hiding a column changes the worksheet view, not the underlying data. Values in hidden columns remain in the workbook and can still be referenced by formulas. Hiding is not a way to secure confidential information.

Hide a Column Manually

If you only need to hide a column occasionally, there is no need to use code. Select the column by clicking its letter heading, then use Home > Format > Hide & Unhide > Hide Columns. To show it again, select the column headings on both sides of the hidden column, then choose Home > Format > Hide & Unhide > Unhide Columns.

For example, if column C is hidden, select columns B and D together before choosing Unhide Columns. You can also right-click a selected column heading and choose Hide or Unhide. These actions are immediate, but they do not respond to a cell value on their own.

On Windows, Ctrl+0 hides selected columns; Ctrl+Shift+0 may unhide them, although Windows regional or system settings can intercept that shortcut. On Mac, the common hide-column shortcut is Control+0, and the unhide shortcut can vary by keyboard and macOS settings. The ribbon commands are a dependable alternative on either platform.

Automatically Hide a Column When a Cell Changes

The following example monitors B1 on one worksheet. If B1 contains the word Hide, Excel hides column C; if B1 contains any other value, Excel shows column C. The comparison ignores capitalization and leading or trailing spaces, so “ hide ” also counts as Hide.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm).
Press Alt+F11 on Windows to open the Visual Basic Editor. On Mac, open the editor from the Developer tab or Tools > Macro > Visual Basic Editor; the keyboard shortcut can depend on the Mac keyboard and function-key settings.
In Project Explorer, find the workbook and double-click the specific worksheet where B1 is located. Do not paste this event procedure into a standard module.
Paste the code below into that worksheet’s code window, then save the workbook.
Return to the sheet and enter Hide or another value in B1. If macros are allowed and enabled, column C should respond.

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

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

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

    ' Hide column C only when B1 says Hide, ignoring case and extra spaces.
    Me.Columns("C").Hidden = (LCase$(Trim$(CStr(Me.Range("B1").Value2))) = "hide")

CleanUp:
    Application.EnableEvents = previousEvents
End Sub

Worksheet_Change runs when a user edits the cell or a value is pasted into it. It does not run merely because a formula elsewhere recalculates B1. The event code is stored with the worksheet, so put it on the sheet that contains the control cell and the column to hide. You can change B1 and C in the code to match your layout.

The code temporarily turns off Excel events while it changes visibility, then restores the previous event setting. That helps prevent other event procedures from firing unexpectedly and avoids leaving events turned off if an error occurs. If an error does occur, inspect the code and workbook state before assuming the column updated correctly.

When the Control Cell Contains a Formula

If B1 contains a formula, Worksheet_Change generally will not respond when the formula’s calculated result changes. Excel treats that as recalculation, not a direct edit to B1. In that case, use the worksheet’s Worksheet_Calculate event instead. Replace the Worksheet_Change procedure with the following procedure in the same worksheet code window; do not create two procedures with the same event name.

Private Sub Worksheet_Calculate()
    Dim previousEvents As Boolean

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

    ' Recheck the formula result in B1 whenever this sheet recalculates.
    Me.Columns("C").Hidden = (LCase$(Trim$(CStr(Me.Range("B1").Value2))) = "hide")

CleanUp:
    Application.EnableEvents = previousEvents
End Sub

Because a calculation event can run whenever the sheet recalculates, keep the procedure focused: check the control value and set the intended column visibility. In manual calculation mode, a formula result may not update until Excel recalculates, for example when you press F9. If the workbook contains many calculations or macros, consider whether recalculating the sheet should trigger this visibility change each time.

Use Different Values or Control Several Columns

The example uses a text instruction, but the test can be adapted to a number, a status word, or another condition. For instance, you could hide a column when a status cell says No, or when a numeric control cell is zero. Make the comparison explicit and test the behavior for blank cells, unexpected entries, and errors so the sheet does not hide important information accidentally.

To control more than one column, add another assignment for each column that should follow the same condition. For example, you could make columns C and D respond to B1 together. If different columns depend on different cells or rules, give each rule a clear test rather than building one difficult-to-maintain expression. Document which control cell affects which columns, especially if other people will use the workbook.

Choose whether the code should hide only when a condition is true or whether it should also unhide when the condition is false. The sample does both: a value other than Hide makes C visible. That is often convenient, but it can override a user’s manual decision to hide C. If manual visibility choices must be preserved, use a different design, such as a button that applies a selected view or a separate control for each visibility state.

Choose Between VBA, Manual Hiding, and Filters

The best method depends on what “based on a cell value” means in your workbook:

Manual hiding is simplest when the layout changes rarely and a person can choose when to hide or show a column.
VBA events suit a desktop workbook that must react automatically to a cell edit or a formula recalculation. They require a macro-enabled file and permission to run macros.
Filtering is useful when the goal is to remove records that do not match a value. Excel filters hide rows, not individual columns based on a cell value.
Separate report layouts can be better when viewers need different sets of fields, when macros are prohibited, or when the workbook must work consistently in Excel for the web.

Grouping columns with Data > Group adds an outline control that lets a person collapse or expand columns. It is convenient for optional detail, but it is not an automatic condition tied to a cell. Similarly, a dropdown list can provide a clear user choice, but the dropdown alone does not hide anything; VBA or another layout approach must respond to the selection.

Common Problems and Safe Use

If nothing happens, check the common causes before changing the code. The procedure may be in a standard module rather than the worksheet’s code window; the workbook may have been saved as .xlsx, which does not retain VBA; macros may be disabled; or the cell address in the code may not match the control cell. Also check whether the code is attached to the sheet where the edit occurs.

If B1 contains a formula, use Worksheet_Calculate rather than expecting Worksheet_Change to detect a changed result. If the cell contains an error value, converting it to text can raise an error; the sample’s cleanup section restores Excel’s event setting, but you may want to add a deliberate error-value check for a workbook where errors are possible.

Macros cannot be undone with Ctrl+Z. Before adding or running VBA, save a copy of the workbook. Use macros only when you trust the file and its source, and follow your organization’s macro-security rules. Excel for the web can open and edit many macro-enabled workbooks, but it cannot run VBA macros; use the Excel desktop app for this automation.

Hidden columns are still part of the worksheet. Users may be able to unhide them, and the data can still be read by formulas or other workbook features. If the purpose is to restrict access to data, do not rely on hiding columns; store or share only the information each viewer should receive.

Frequently Asked Questions

Can conditional formatting hide a column?
No. Conditional formatting can change how cells look, but it cannot set a column to hidden. Use VBA for automatic visibility changes in desktop Excel, or choose a manual layout or reporting method if macros are unavailable.

Can a formula hide a column in Excel?
Not by itself. A formula can calculate a value that VBA reads, but a formula cannot directly change the worksheet’s column visibility. For a formula-driven control, use a Worksheet_Calculate event.

Why does my macro not run when a formula result changes?
Worksheet_Change responds to edits, not ordinary recalculation of a formula result. Use Worksheet_Calculate for a formula-based control, and confirm that calculation is occurring and macros are enabled.

Can I hide rows instead of columns based on a value?
Yes. VBA can set a row’s Hidden property using a similar event-based approach. If you are working with a structured list and want to display records that match a criterion, an AutoFilter is often a better fit because it is designed to filter rows.

Will the automatic hiding work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop version of Excel to use this event-based automation, or use a non-macro layout that meets the same need.

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.