Excel for Microsoft 365 on Windows does not include a built-in ribbon button that alternates between hiding and unhiding selected columns. For a no-code toggle, group columns and use the outline controls; for a clickable button that hides and restores the same selection, add a small VBA macro and assign it to a button.
What Excel’s hide command does
Hiding a column changes its display, not its contents: the column remains in the worksheet, and its values can still be used in formulas. The standard Hide command is a one-way action. After hiding columns, Excel does not automatically remember them as a special toggle group; you must unhide them by selecting the relevant area or use another mechanism such as grouping or a macro.
To hide columns with the ribbon, select their column headers, then choose Home > Cells > Format > Hide & Unhide > Hide Columns. To restore them, select the column headers on both sides of the hidden range and choose Home > Cells > Format > Hide & Unhide > Unhide Columns. For example, if C is hidden, select B and D (or the header range B:D) before choosing Unhide Columns.
You can also right-click selected column headers and choose Hide. The matching context-menu option, Unhide, works when the selection includes the hidden columns’ boundaries. On Mac, the ribbon and context-menu commands are similar, although the exact layout can differ by Excel release. If you cannot find the command, use the Home tab’s Format menu rather than relying on a shortcut.
Fast ways to hide and unhide columns
Keyboard shortcuts are useful for the one-way hide and unhide commands, but they do not create a persistent toggle button. In Excel for Windows, select the columns and press Ctrl+0 to hide them. The unhide shortcut is Ctrl+Shift+0; on some Windows systems, a keyboard or operating-system setting can intercept that combination. If it does not work, use the ribbon command instead.
On Mac, Command+0 hides selected columns in Excel. Shortcut behavior can vary with the keyboard layout and Excel version, so the ribbon’s Hide & Unhide commands are the dependable alternative, especially for restoring hidden columns. A shortcut speeds up an action; it does not give you a visible, reusable control on the sheet.
- Hide one column: click its lettered header, then use the Hide command or the Windows shortcut.
- Hide a range: drag across the headers, or select a range such as C:E, and hide the selected columns.
- Unhide a middle column: select the headers immediately before and after the hidden range, then choose Unhide Columns.
- Unhide a column at the sheet edge: select the entire worksheet using the square at the top-left corner of the row and column headings, then choose Unhide Columns.
Create a no-code toggle with column grouping
If your goal is to collapse and reopen a set of detail columns while working, Excel’s outline grouping is usually the simplest answer. Select the column headers you want to control, then choose Data > Outline > Group. If Excel asks whether to group rows or columns, choose Columns. The sheet displays outline controls, usually a minus sign when the group is open and a plus sign when it is collapsed. Click the control to hide or show the grouped columns.
Grouping is not identical to a custom button: the control is attached to the worksheet outline and sits above the grouped columns. It is useful for repeated viewing, such as hiding several optional cost breakdown columns while leaving a summary visible. It also lets readers understand that the concealed columns form a deliberate detail group rather than an accidental omission.
To remove a group, select its columns and choose Data > Outline > Ungroup. If the plus and minus symbols are missing even though a group exists, check whether outline symbols are being displayed. In Windows Excel, the relevant worksheet option is under File > Options > Advanced, in the display options for that worksheet. Grouping may also be unavailable when the sheet is protected; check the worksheet’s protection settings if the command is disabled.
Add a real toggle button with VBA
For a clickable control that changes the visibility of whichever columns are selected, use a macro. The example below treats a selected block consistently: if all its columns are hidden, it unhides them; if any are visible, it hides the whole selected block. Select cells in the columns or select their headers before running it. The macro is for a contiguous selection, such as C:E.
Sub ToggleSelectedColumns()
Dim selectedRange As Range
Dim col As Range
Dim allHidden As Boolean
If TypeName(Selection) <> "Range" Then
MsgBox "Select worksheet columns first.", vbInformation
Exit Sub
End If
Set selectedRange = Selection
allHidden = True
For Each col In selectedRange.EntireColumn.Columns
If col.Hidden = False Then
allHidden = False
Exit For
End If
Next col
selectedRange.EntireColumn.Hidden = Not allHidden
End Sub
To add it in Windows Excel, first save a copy of the workbook. Open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the code, and close the editor. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon. Then choose Developer > Insert > Button (Form Control), draw the button on the worksheet, and assign ToggleSelectedColumns when prompted.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so the VBA code is retained. Macros may be blocked by security settings, and you should enable them only in workbooks you trust. Excel for Mac supports VBA, but its ribbon and editor layout differ; the menu names and steps for inserting a form button can also differ by release. A macro cannot be undone with Ctrl+Z, so save a copy before running it and check the selected columns before clicking the button.
Choose between a shortcut, group, and macro
The right method depends on whether you need a quick command, a visible disclosure control, or custom toggle behavior. A built-in Hide command is best for an occasional change. Grouping is a strong no-code choice when the same columns should be opened and collapsed repeatedly. A macro is useful when a worksheet needs a clearly labeled button or a consistent action that users can trigger without navigating the ribbon.
- Use Hide and Unhide for occasional, straightforward changes without adding workbook features.
- Use grouping when people should be able to expand and collapse a known set of detail columns.
- Use VBA when a button must perform a custom toggle, and your users can work with macro-enabled files.
Consider who will open the workbook before choosing VBA. Some organizations block macros, and a macro-enabled file can prompt users about security. A grouped outline is easier to share in a macro-restricted environment. Conversely, a macro button can be easier to explain when the sheet’s intended users are comfortable enabling trusted macros and need an obvious control.
Common problems and misconceptions
“The column is gone.” A hidden column has not been deleted. Its width is simply set to zero for display. Unhide it to see the data again. Hiding columns is not a privacy or security measure: someone with access to the workbook may be able to reveal them, and formulas can continue to reference their contents.
“Unhide is grayed out or has no effect.” The selection may not include the hidden column’s boundary. If C is hidden, select B:D rather than only a distant column. For a hidden first or last column, selecting the whole worksheet is often the most direct approach. Also check that the worksheet is not protected in a way that prevents changing column formatting.
“The macro button hides everything.” The sample macro operates on the columns represented by the current selection, not on a permanently configured range. If a single cell in column C is selected, it affects C; if C:E is selected, it affects those three columns. Select the intended block before clicking. If the selection contains a mixture of visible and hidden columns, the macro hides all of them so the group ends in one consistent state.
“The shortcut does not work.” Shortcuts can be affected by the keyboard layout or, on Windows, settings that reserve a key combination. Use the ribbon command to confirm that hiding and unhiding themselves work. Also make sure the worksheet, rather than a chart or another object, is active when using a shortcut or the example macro.
Frequently Asked Questions
Is there a built-in Excel hide-columns toggle button?
No. Excel provides separate Hide and Unhide commands. For an on-sheet no-code control, group the columns and use the outline plus/minus symbols; a custom button that runs a toggle requires a macro or another automation approach.
How do I toggle columns without VBA?
Select the column headers and choose Data > Outline > Group. Use the resulting outline control to collapse or expand the group. This is a repeatable no-code toggle, though it is an outline control rather than a standard button.
Can I hide columns with a button that always controls the same range?
Yes. A VBA macro can refer to a fixed range of columns instead of the current selection, and you can assign that macro to a Form Control button. The sample here intentionally uses the selected columns, so adjust its logic if the button should always control a specific range.
Why can I still calculate with a hidden column?
Hiding affects what is displayed, not whether Excel stores or calculates the values. A formula can still refer to a hidden column. If the values must not be available to workbook users, hiding alone is not an appropriate protection method.
Does the button work in Excel for the web?
VBA macros cannot be created, edited, or run in Excel for the web. A macro button therefore requires desktop Excel with macros enabled. A workbook outline created in desktop Excel is a better no-code option, although the available controls and editing experience can differ in the browser.
Related
- Excel Data Table Sensitivity Analysis Explained
- Excel Data Table Row and Column Input Explained
- Excel Data Table Template: Set Up a Reusable Worksheet
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Make Labels in Word: Print Them CorrectlyTo make labels in Microsoft Word, open Mailings > Labels, choose the label brand and product number, enter…
- Microsoft Word: Make One Page Landscape in a DocumentTo make just one page landscape in Microsoft Word for Microsoft 365 on Windows, place a Next Page…
- Create a Fillable Template in Word: Step-by-StepTo create a fillable template in Word, turn on the Developer tab, add content controls such as text…
- Excel AutoFit Column Width Shortcut and How It WorksIn Excel for Microsoft 365 on Windows, select the column or columns, then press Alt+H, O, I in…
- How to Mail Merge in Word 2010: A Step-by-Step GuideTo mail merge in Word 2010, open the Mailings tab, choose Start Mail Merge or Step-by-Step Mail Merge…
- Accidentally Didn’t Save an Excel File? Recover ItIf you accidentally closed Excel without saving, first reopen the program and check the Document Recovery pane; if…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.