No. In Excel for Microsoft 365 on Windows, conditional formatting can change how cells look, but it cannot hide or show a column. To conceal a column, hide it manually, collapse a column group, or use a VBA macro if the visibility must change automatically.
What conditional formatting can and cannot do
Conditional formatting applies visual styles when specified conditions are met. For example, a rule can turn overdue dates red, shade high sales values, or make duplicate entries stand out. The rule affects the appearance of cells; it does not change the worksheet structure or the visibility state of rows and columns.
In Microsoft 365 for Windows, you create or edit rules from Home > Conditional Formatting. Common choices include Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets. You can also use a formula-based rule to decide which cells receive a format. None of these rule types includes an action to hide a column.
This distinction matters because a condition and an action are different things. A rule can detect that a status cell says “Closed” and format related cells accordingly, but it cannot tell Excel to remove their column from view. A formula in a conditional-formatting rule does not gain the ability to change worksheet layout just because it evaluates to TRUE or FALSE.
Why a formatting rule cannot hide a whole column
Column visibility is a worksheet layout property. Excel stores whether a column is hidden separately from the font, fill, border, and number-format settings that conditional formatting can apply. Conditional formatting is designed to update cell formatting as values change, not to alter column widths or visibility.
You can apply a rule to an entire column, such as selecting column C before creating the rule, but that only makes the rule apply to cells in column C. It does not hide the column header, remove the column from the worksheet, or make neighboring columns shift over. Similarly, applying a rule to a table or a large range does not add a hide-column command.
The same limitation applies to hiding rows. Conditional formatting cannot hide a row automatically either. Excel’s filters can hide rows that fail a condition, and VBA can change row or column visibility, but those are separate features. This is a common source of confusion: a filter may look like conditional formatting because both respond to data, yet filtering and formatting work differently.
Can formatting make column contents look hidden?
It is possible to make cell contents less visible without hiding the column. For instance, you could use a conditional-formatting rule to set the font color to match the cell fill, or apply a number format that suppresses the display of certain numeric values. These are visual workarounds, not true hiding. The column remains in place, and the values may still be available through the formula bar, editing, copying, or other workbook operations.
These workarounds can be useful for presentation, such as making a dashboard less cluttered while retaining a consistent layout. They are poor choices if the goal is privacy, security, or preventing users from accessing information. A person who can open the workbook may be able to reveal the contents or change the formatting. Use workbook permissions and appropriate data-access controls for sensitive information; do not treat a font color or number format as protection.
A visually blank column can also confuse people. Readers may not know whether the cells are empty, whether a rule is active, or whether the display has been intentionally masked. If you use this technique, add a note or clear instructions, and test how the worksheet behaves when printed, copied, or viewed with different themes and fills.
Hide a column manually in Excel
For a column that should stay out of view regardless of cell values, use Excel’s built-in hide command. On Windows, select the column by clicking its letter at the top of the worksheet, then choose Home > Format > Hide & Unhide > Hide Columns. You can also right-click the selected column heading and choose Hide.
To hide several neighboring columns, select their column letters together before using the command. For separated columns, select one column, then hold Ctrl while selecting the other column headings, and hide the selection. On a Mac, the ribbon’s Home > Format > Hide & Unhide commands provide the equivalent options; the right-click menu is another route. The exact shortcut behavior can depend on the keyboard and system settings, so the ribbon command is a dependable choice.
Hidden columns are still part of the worksheet. To show one again, select the column headings on both sides of the hidden column, then choose Home > Format > Hide & Unhide > Unhide Columns. If the hidden column is at the left edge of the sheet, select the worksheet and use the Unhide Columns command, or select the adjacent visible column and carefully include the hidden area. A narrow column may also be difficult to distinguish from a hidden one, so check its boundary before assuming the data was deleted.
Collapse columns with grouping
If people should be able to show and hide a set of columns while working, grouping is often more convenient than repeatedly using Hide and Unhide. Select the columns to collapse, then choose Data > Group. Excel adds outline controls above the worksheet. Use the minus control to collapse the group and the plus control to expand it again.
Grouping is useful for reports with detail columns that readers only need occasionally. For example, a budget sheet might keep monthly figures available in a grouped section while showing only annual totals in the collapsed view. Unlike conditional formatting, grouping is an intentional worksheet-structure feature. It does not automatically respond to a cell’s value, but it gives users an obvious way to switch between summary and detail views.
If the outline controls are missing, check whether the columns are grouped and whether outline symbols are enabled in Excel’s display settings. Grouping can also become awkward when multiple outlines overlap or when the sheet is protected. In those cases, review the outline levels and protection settings rather than trying to reproduce the behavior with a formatting rule.
Automatically hide a column when a value changes
If the column must hide or reappear automatically when a user changes a cell, VBA can control the column’s Hidden property. The following example hides column C when B2 contains YES and shows it for any other value. It responds to direct edits in B2; it does not respond just because a formula result in B2 recalculates.
In desktop Excel for Windows, open the Visual Basic Editor, find the worksheet in the project pane, and place the event procedure in that worksheet’s code module—not in a standard module. On Mac, open the Visual Basic Editor from Tools > Macro > Visual Basic Editor, then use the corresponding worksheet module.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo CleanUp
' Run only when B2 is edited.
If Intersect(Target, Me.Range("B2")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Me.Columns("C").Hidden = (UCase$(Trim$(CStr(Me.Range("B2").Value))) = "YES")
CleanUp:
Application.EnableEvents = True
End Sub
The event is limited to B2, so changing other cells will not trigger the visibility change. If B2 contains “yes,” “Yes,” or “ YES ”, the comparison still matches because the code removes surrounding spaces and ignores letter case. Update the cell reference, column letter, and comparison text to fit your layout. If B2 is formula-driven, a Worksheet_Change event is not sufficient; formula recalculation needs a different event approach, such as Worksheet_Calculate, designed carefully to avoid repeated or unnecessary changes.
Save a macro-enabled workbook with the .xlsm extension, and make a copy before adding or running VBA. Excel for the web does not run VBA macros, so this automation requires desktop Excel. Macro security settings can also prevent code from running. A macro changes visibility; it does not secure the hidden data, and users with access to the workbook may be able to unhide the column.
Choose the right alternative
The best option depends on why the column needs to disappear. Use the simplest feature that matches the job:
- Hide the column manually when the same columns should remain out of sight until someone deliberately unhides them.
- Group columns when users should be able to expand and collapse optional detail themselves.
- Filter a list when the goal is to show only matching records. Filters hide rows, not columns; they are not a substitute for conditional column visibility.
- Use VBA when a desktop workbook needs to change column visibility in response to user edits and macros are acceptable.
- Use visual formatting only when changing appearance is enough and the underlying values can remain accessible.
For a dashboard that needs different layouts for different audiences, separate sheets or purpose-built views may be easier to maintain than a complex set of macros. If users need to see different fields based on permissions, hiding columns in a shared workbook is not an access-control system; provide appropriately limited copies or use a platform that supports the required permissions.
Common problems and misconceptions
“I applied the rule to the whole column, so why is it still visible?” Applying a rule to a whole column sets its formatting scope. It does not change the column’s visibility property. Use Hide Columns, Group, or VBA for that.
“The cells look blank, so the information must be hidden.” A blank-looking cell can still contain a value or formula. Formatting affects what is displayed, not necessarily what is stored. Click the cell and inspect the formula bar before assuming the data is absent.
“A filter should hide columns with no matching values.” Standard worksheet filters determine which rows are displayed based on field values. They do not automatically hide fields or columns. To remove optional fields from view, hide or group the columns separately.
“The macro does nothing.” Check that the code is in the correct worksheet module, macros are enabled, the file is saved as .xlsm, and the edited cell is the one referenced by the code. Also remember that changing a formula result is not the same as directly editing the cell, so the example’s Worksheet_Change event will not fire for recalculation alone.
“Can I protect the sheet so nobody can reveal the column?” Worksheet protection can restrict certain editing actions, depending on its settings, but it is not a reliable way to keep sensitive information secret. Hidden columns are a layout convenience, not a security boundary. Keep confidential data out of workbooks shared with people who should not have access to it.
Frequently Asked Questions
Can conditional formatting hide a column in Excel?
No. Conditional formatting changes cell appearance, not column visibility. Hide the column manually, group it, or use VBA for automatic behavior.
Can conditional formatting hide rows instead?
No. It cannot hide rows either. Use a filter to hide rows that do not meet criteria, or use VBA if row visibility must be automated.
Can I hide a column when a cell contains a certain word?
Not with conditional formatting alone. A VBA event macro can check an edited cell and set a column’s Hidden property, but macros require desktop Excel and must be enabled.
Will a hidden column’s values still be in the workbook?
Yes. Hiding a column changes what is shown, not whether its cells exist. Users may be able to unhide or otherwise inspect the data, so hiding is not a privacy measure.
What is the simplest way to temporarily hide columns?
Select the column headings and choose Home > Format > Hide & Unhide > Hide Columns. Use Group instead if you want a visible expand-and-collapse control.
Related
- Defining Print Area in Excel: Setup and Printing Tips
- DDSolver Excel Add-in: What It Does and How to Use It
- Data Validation in Excel: Lists, Rules, and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Text Upside Down: What Works and What Doesn’tIn Excel for Microsoft 365 on Windows, you cannot rotate text inside a worksheet cell a full 180…
- Excel Page Breaks: How to Set, Move, and Remove ThemTo set a page break in Excel for Microsoft 365 on Windows, select the cell where the next…
- How to Add Color to a Drop-Down List in ExcelTo add color to a drop-down list in Excel, create the list with Data Validation, then use conditional…
- Compare Two Columns of Names in Excel: Find MatchesTo check whether names in one list appear anywhere in another, put the first list in A2:A6 and…
- Freeze Panes in Excel: Keep Rows and Columns VisibleTo freeze rows or columns in Excel for Microsoft 365 on Windows, select the cell below the rows…
- BBA Worksheet Examples for Accounting, Marketing and MoreBBA worksheet examples include accounting statements, break-even analysis, marketing campaign trackers, HR scorecards and operations schedules. They can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.