Excel: Hide a Column When a Cell Contains Text

How-to basics · Updated November 19, 2026

Excel has no built-in formula or conditional-formatting rule that automatically hides a column when a cell contains particular text. In Excel for Microsoft 365 on Windows, you can automate it with a short VBA worksheet macro; for a one-time change, use Find or a filter to locate matching cells and hide their columns manually.

What “contains” means in Excel

A cell “contains” text when the target word or phrase appears anywhere in its value. For example, if the keyword is hide, cells containing hide, please hide, or hidden all include that sequence of letters. The macro below checks for a partial match without distinguishing uppercase from lowercase, so HIDE also matches.

There is an important distinction between hiding a cell’s contents and hiding its entire column. Formatting can make text hard to see, and a filter can hide rows, but hiding a column changes the worksheet’s layout: every cell in that column is concealed until the column is unhidden. Decide whether that is actually what you need before automating it.

Automate column hiding with VBA

This example checks cells C1:Z1. If one of those cells contains the word hide, it hides the corresponding column; if it does not, it makes that column visible. Change the range and keyword in the code to suit your worksheet. The code also refreshes when you activate the sheet, which applies the rule to values already present.

  1. In desktop Excel, press Alt+F11 on Windows to open the Visual Basic Editor. On a Mac, use Developer > Visual Basic; the keyboard shortcut can depend on your keyboard settings.
  2. In the project pane, find your workbook and double-click the worksheet that contains the cells to check. Paste the code into that worksheet’s code window, not a standard module.
  3. Replace C1:Z1 with the cells that control the matching columns, and replace hide if you want a different keyword.
  4. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), close the editor, and edit or activate the worksheet to run the rule.
Private Sub Worksheet_Activate()
    RefreshColumnVisibility
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("C1:Z1")) Is Nothing Then Exit Sub
    RefreshColumnVisibility
End Sub

Private Sub RefreshColumnVisibility()
    Dim cell As Range
    Dim textValue As String

    On Error GoTo Failed
    Application.EnableEvents = False

    For Each cell In Me.Range("C1:Z1").Cells
        If IsError(cell.Value) Then
            textValue = ""
        Else
            textValue = CStr(cell.Value2)
        End If

        Me.Columns(cell.Column).Hidden = _
            (InStr(1, textValue, "hide", vbTextCompare) > 0)
    Next cell

    Application.EnableEvents = True
    Exit Sub

Failed:
    Application.EnableEvents = True
    MsgBox "Column visibility could not be updated: " & Err.Description, vbExclamation
End Sub

The change event responds when a user edits or pastes into the watched range. The activation event checks it again when you switch to the sheet. The test is based on the cell’s value, not its displayed formatting. A cell with an error value is treated as having no match in this example.

Choose the right cells and matching rule

The sample assumes one control cell per column: C1 controls column C, D1 controls column D, and so on. That arrangement is easy to maintain because the cell being checked and the column being hidden have the same column letter. If the control values are in a different row, update both references to the same range; for example, change each instance of C1:Z1 to C5:Z5.

If your condition is somewhere in a data column rather than a header row, clarify what should happen when several cells in that column have different values. The sample does not search every row; it checks only the specified control cells. You could adapt the VBA to scan a larger range, but that requires defining whether one matching cell, every cell, or a particular row should trigger hiding.

The InStr check uses a substring match. That is useful for notes such as hide this field, but it can also match a longer word that happens to include the keyword. If you need an exact match instead, compare the cell value to the keyword rather than searching within it. If you need whole-word matching, define how punctuation and spaces should count; a simple substring check does not distinguish whole words.

What changes when a cell is formula-driven

Worksheet_Change runs when a cell is edited, but it does not run just because a formula recalculates and returns a new result. If the watched cells contain formulas, add this event to the same worksheet code window:

Private Sub Worksheet_Calculate()
    RefreshColumnVisibility
End Sub

That makes Excel recheck the range after worksheet calculation. It can also run more often than you expect, since recalculation may happen after changes elsewhere in the workbook. Use it only when formula results in the watched range need to control visibility. As with any event macro, test it on a copy first, especially if the sheet has many columns or complex formulas.

Manual options without a macro

If this is a one-time task, manual hiding is often simpler than setting up VBA. Search for the text, identify the matching cells, and hide their columns. In Excel for Microsoft 365 on Windows, use Home > Find & Select > Find, enter the text, and use the Find options to choose whether Excel searches within the sheet or the workbook. For a repeatable workflow, use Find All to review the matches before selecting their columns; searching does not itself hide anything.

Once you have selected the relevant column or columns, use Home > Format > Hide & Unhide > Hide Columns. On Windows, Ctrl+0 hides selected columns. To reveal them later, select the columns on either side of the hidden area and choose Home > Format > Hide & Unhide > Unhide Columns. Excel for Mac has equivalent Hide and Unhide commands in the Home tab’s Format menu; keyboard shortcuts can differ by version and keyboard settings.

If the data is organized as a table and your real goal is to show only records meeting a condition, filter the rows instead. A filter hides nonmatching records from view without changing which columns are displayed. Use the filter arrow on the relevant table header and choose a text filter, such as Contains. This is usually a better fit when the condition describes which records to see, rather than which fields to conceal.

Why conditional formatting is not enough

Conditional formatting can change a cell’s appearance when it meets a rule, but it cannot automatically hide the column containing that cell. A formula placed in a worksheet cell also cannot directly change the worksheet’s column visibility. These tools calculate values or formatting; hiding a column is a structural interface action, which is why automatic behavior requires VBA or another automation approach.

You can use conditional formatting as a visual substitute—for example, color a header that contains a keyword—if the purpose is to flag columns rather than remove them from view. This keeps the layout stable and makes it less likely that someone will overlook information because it has been hidden.

Common problems and limitations

  • The code does nothing: Confirm it is in the correct worksheet’s code module, macros are enabled, and the file is saved as .xlsm. Editing a cell outside the watched range will not trigger the change event.
  • The columns do not update after a formula changes: Add the optional Worksheet_Calculate event shown above if formula results are the controls.
  • A column stays hidden after changing the keyword: The macro sets each watched column’s visibility according to its current control cell. Check that the relevant control cell is in the range and no longer contains the keyword, then activate the sheet or edit a watched cell.
  • The worksheet is protected: Protection settings can prevent column formatting changes. You may need to unprotect the sheet or adjust its protection setup before the macro can hide or unhide columns.
  • The workbook is open in Excel for the web: Excel for the web does not run VBA macros. Open the workbook in desktop Excel to use this code. Excel for Mac supports VBA, though editor access and some keyboard shortcuts differ from Windows.

Hidden columns are not deleted, encrypted, or made private. A reader who can use the workbook may be able to unhide them, and hiding is not a security control. If the data must not be available to someone, do not rely on column hiding to protect it.

Alternatives for recurring reports

For a report that changes regularly, consider whether you need to hide columns at all. A separate report sheet can display only the fields readers need, leaving the source data intact. Excel tables and filters are useful when the condition applies to records. Grouping columns can make optional sections collapsible through outline controls, which is convenient when users should choose whether to expand them.

Power Query can transform and load a selected set of columns into a report, but it does not automatically hide source-sheet columns based on a cell’s text. Choose a method based on the desired result: VBA for automatic layout changes in desktop Excel, filters for selecting rows, grouping for user-controlled sections, and a separate report for a consistently reduced view.

Frequently Asked Questions

Can an Excel formula hide a column automatically?
No. Worksheet formulas return values; they do not change a column’s hidden status. Use VBA for automatic hiding in desktop Excel, or use a manual hide command when the change is occasional.

Can I hide a column if any cell in it contains a word?
Yes, but the example above checks only a designated control row. To base the decision on an entire column, the VBA must scan the relevant rows and define whether any match should hide the column. Specify a bounded range to avoid needlessly searching every row.

Does the macro match uppercase and lowercase text?
Yes. The example uses vbTextCompare, so the keyword match is case-insensitive. It also finds the keyword inside a longer string, not just as a separate word.

Will the macro work in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not execute VBA. Run the macro in desktop Excel for Windows or Mac.

How do I unhide the columns again?
Change or remove the matching text from the control cells and trigger a refresh by editing a watched cell or activating the sheet. For a manual change, select the neighboring columns and choose Home > Format > Hide & Unhide > Unhide Columns.

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.