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.
- In desktop Excel, press
Alt+F11on Windows to open the Visual Basic Editor. On a Mac, use Developer > Visual Basic; the keyboard shortcut can depend on your keyboard settings. - 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.
- Replace
C1:Z1with the cells that control the matching columns, and replacehideif you want a different keyword. - 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_Calculateevent 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
- Excel Consolidated Sum: Combine Data Across Sheets
- Excel: Consolidate Two Columns Into One Without Losing Data
- Excel Consolidate Worksheets Into One: Methods & Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Solver Settings: How to Set Up and Choose OptionsIn Excel for Microsoft 365 on Windows, open Data > Solver, set an objective cell, choose the cells…
- Excel Fill Color Shortcut: How to Use ItIn Excel for Microsoft 365 on Windows, select the cell or range, then press Alt, H, H in…
- Remove Duplicates in Excel and Keep One Matching RowTo remove duplicates and keep one record in Excel for Microsoft 365 on Windows, select your full data…
- How to Make a Brochure in Microsoft Word, Step by StepTo make a brochure in Microsoft Word, start with a brochure template or set a blank document to…
- How to Wrap Text in Google Sheets: Settings and TipsTo wrap text in Google Sheets in a web browser, select the cell or range, then choose Format…
- Google Sheets Duplicate Rows: Find and Remove ThemYes. In Google Sheets, select the data range and choose Data > Data cleanup > Remove duplicates to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.