Hide Excel Columns Based on Cell Values Automatically

How-to basics · Updated November 18, 2026

To hide Excel columns when a cell contains a chosen value, use a short VBA macro that checks a control row and hides each matching column. Excel does not have a built-in conditional-formatting rule that automatically hides columns, so the macro is the practical option when the hide/show result should update on demand. You can also hide columns manually if you only need a one-time change.

What You’ll Need

  • Excel for Microsoft 365 on Windows and a workbook saved as an Excel Macro-Enabled Workbook (.xlsm).
  • A row of control values, such as Hide or Show, in the columns you want the macro to check.
  • Permission to use macros. If your organization blocks macros, use the manual alternative described below or ask your administrator about approved options.
  • A saved copy of the workbook before running VBA. Macro actions cannot be undone with Ctrl+Z.

Step-by-Step

  1. Choose how the rule will work. This example checks row 2 in columns C through H. If a cell in that range says Hide, the macro hides the column above it; if it says anything else, the macro shows that column. For example, if D2 contains Hide, column D will be hidden. Decide which row will hold your control values and which columns the rule should cover before adding the code.
  2. Set up the control row. Put a clear marker in each cell to be checked. You might enter Hide for columns that should disappear and Show for columns that should remain visible. The marker can be in a dedicated row, or it can be a value already present in the data, as long as you point the macro at the right cells. Keep the marker consistent; the sample code looks for the word Hide and ignores letter case, but it does not treat similar words such as Hidden as a match.
  3. Save a backup and enable the Developer tab if needed. Save a separate copy first, then save the working file as an .xlsm workbook: choose File > Save As and select Excel Macro-Enabled Workbook. If Developer is not on the ribbon, go to File > Options > Customize Ribbon, select the Developer check box, and choose OK. On Mac, use Excel > Preferences > Ribbon & Toolbar and enable Developer under Main Tabs.
  4. Open the Visual Basic Editor. On Windows, choose Developer > Visual Basic, or press Alt+F11. On Mac, choose Developer > Visual Basic; the keyboard shortcut can depend on your keyboard and may require Fn with Option+F11. In the editor, find your workbook in the Project pane. Right-click the workbook project, choose Insert > Module, and select the new module.
  5. Paste the macro into the module. This version checks C2:H2 on the active worksheet. Change the range to the cells you chose, and change the criteria text if you want a different marker. Select the worksheet you intend to control before running it.
    Sub HideColumnsByValue()
        Dim ws As Worksheet
        Dim checkRange As Range
        Dim cell As Range
        Dim criteria As String
    
        Set ws = ActiveSheet
        Set checkRange = ws.Range("C2:H2")
        criteria = "Hide"
    
        Application.ScreenUpdating = False
        For Each cell In checkRange.Cells
            ws.Columns(cell.Column).Hidden = _
                (StrComp(CStr(cell.Value), criteria, vbTextCompare) = 0)
        Next cell
        Application.ScreenUpdating = True
    End Sub

    The line break after the equals sign is valid VBA because the underscore continues the statement on the next line. The code uses a text comparison that ignores capitalization: Hide, HIDE, and hide all match. For each checked column, it also sets visibility explicitly, so a nonmatching cell makes that column visible.

  6. Adjust the range and criterion carefully. For example, to inspect cells D5:K5 and hide columns whose marker is Skip, change the range to ws.Range("D5:K5") and the criterion to criteria = "Skip". The checked cells must span the same columns you intend to hide. The code uses the active worksheet, so it is important to activate the correct sheet before you run the macro.
  7. Run the macro. Return to Excel, select the worksheet with your control row, and press Alt+F8 on Windows. Select HideColumnsByValue and choose Run. In Excel for Mac, open the macro dialog from Developer > Macros; Option+F8 may also open it, depending on your keyboard. If Excel displays a security warning, only enable the macro if you trust the workbook and its source.
  8. Check the result and repeat after changing values. Confirm that columns marked Hide have disappeared and that other columns in the checked range are visible. This macro runs when you launch it; it does not automatically run every time a cell changes. After updating the control values, run it again to apply the revised choices. To unhide the checked columns without editing the markers, change the criteria so no cell matches, or use Home > Format > Hide & Unhide > Unhide Columns after selecting the affected area.

One-time manual alternative: If you do not need the columns to respond to values later, read the control cells, select the relevant column letters, and choose Home > Format > Hide & Unhide > Hide Columns. This is not automatic: you must repeat the selection yourself when the values change. Excel for the web does not run VBA macros, so use this manual method there or open the workbook in the desktop app.

Common Mistakes

  • Expecting the rule to run automatically. The sample is a regular macro, not an event-triggered macro. Changing a marker does not immediately hide or show a column; run the macro again.
  • Checking the wrong cells. If the control row is row 3 but the code still checks C2:H2, it will act on the wrong values. Verify both the row number and the first and last columns in the range.
  • Assuming the macro only hides columns. It sets every checked column to visible when its cell does not match the criterion. If you had manually hidden a nonmatching column in that range, running the macro will reveal it.
  • Saving in the wrong file format. A standard .xlsx file cannot store VBA code. Save as .xlsm and keep that format when you want to retain the macro.
  • Confusing filters or conditional formatting with column hiding. Filters hide rows that do not meet a condition, while conditional formatting changes appearance. Neither feature automatically hides a column because of a cell value.

Tips

  • Use a dedicated control row with short, predictable markers such as Hide and Show. This makes the rule easier to inspect and reduces accidental matches in ordinary data.
  • Keep the control cells visible, even when you hide other columns, so you can review the markers and understand why a column is hidden.
  • Test the macro on a copy with just two or three columns first. Confirm that a matching marker hides its column and a nonmatching marker shows it before applying it to a larger sheet.
  • If the values come from formulas, the macro checks the displayed result in each cell when it runs. Recalculate the workbook if necessary, then run the macro again.
  • For a different rule, change the comparison deliberately. For example, an exact marker is safer than a partial text test when labels might contain similar words. Make a backup before editing VBA, and remember that Ctrl+Z cannot reverse a macro.

Troubleshooting

  • Nothing happens: Check that you ran the macro from the macro dialog, that macros are permitted, and that the correct worksheet was active. Make sure the workbook is saved as .xlsm and that the code is in a standard module rather than pasted into a worksheet cell.
  • The wrong columns hide: Compare the checkRange in the code with the control cells on the sheet. The macro hides the column containing each checked cell, so a range shifted one column left or right produces a shifted result.
  • A cell looks like Hide but is not recognized: Check for extra spaces, a different word, or a formula result that differs from the visible text. The sample ignores capitalization but requires an exact text match. Edit the cell or change the criteria to match the actual value.
  • A column unexpectedly becomes visible: This is expected when its checked cell does not match Hide. The macro sets both hidden and visible states across the entire checked range. If you need to preserve manual hidden states, do not include those columns in the range.
  • The macro errors on a cell: The sample is intended for ordinary text markers. If a checked cell contains an Excel error value, such as #N/A, converting that value to text can cause a VBA error. Replace the error with a text marker or revise the code to handle errors before comparing values.
  • The macro runs but the columns stay hidden in Excel for the web: VBA macros do not run in Excel for the web. Open the workbook in desktop Excel to run the macro, or hide and unhide columns manually in the browser.

Frequently Asked Questions

Can Excel hide columns automatically without VBA?
Not through a standard conditional-formatting rule or a built-in formula setting. You can hide columns manually, or use VBA in desktop Excel to apply a value-based rule. Excel for the web does not run VBA.

Will the columns update as soon as I change a value?
No. The sample macro runs when you launch it. Run it again after changing the control cells. Automatic reaction to edits requires additional event-based VBA and should be added only if you understand how that code will behave in your workbook.

Can I base the rule on a value other than the word Hide?
Yes. Change the criteria assignment in the macro, for example to criteria = "No", and use that same value in the control cells. The comparison is case-insensitive but otherwise exact.

Can the macro check a different row or more columns?
Yes. Change the address in checkRange, such as C4:J4. Each cell in the range controls the visibility of the column it belongs to, so choose a range that matches the columns you want the macro to manage.

How do I show the columns again?
Change the markers so they no longer match the criterion and run the macro; it will show the checked columns. You can also select the columns around the hidden area and choose Home > Format > Hide & Unhide > Unhide Columns.

Can I use the same macro in Excel for Mac?
Yes. Excel for Mac supports VBA macros, though the Visual Basic Editor and keyboard shortcuts can differ from Windows. Excel for the web does not execute VBA, so use the desktop version to run the code.

Related

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.