Excel formulas cannot hide columns by themselves. To hide or show columns when a formula’s result changes, put a formula in a control row that returns “Hide” or “Show,” then use a short VBA worksheet event macro to apply that result. This guide sets up that approach in Excel for Microsoft 365 on Windows; save the workbook as a macro-enabled file and enable macros for it to work.
What You’ll Need
- Excel for Microsoft 365 desktop on Windows. Excel for the web can display a macro-enabled workbook, but it cannot run VBA macros.
- A workbook where you can reserve a row for control formulas. The example uses B1:F1 to control columns B:F and checks data in rows 2 through 100.
- Permission to edit the workbook’s VBA project and enable macros. If the worksheet is protected, you may also need to unprotect it or allow column formatting.
- A saved backup or copy of the workbook before adding VBA. Hiding columns is reversible, but workbook code and macro actions deserve a safe working copy.
Step-by-Step
- Choose what each column should respond to. Decide the condition that makes a column unnecessary. In this example, a column is hidden if its data range is empty and shown if it contains something. You can adapt the control formula to another rule, such as whether a related status cell says “Complete.” The macro does not decide the business rule; it reads the word returned by the formula.
- Set aside a control row. Use row 1 for the formula results and keep the data being checked below it. Here, B1 controls column B, C1 controls C, and so on through F1 and F. Make sure the columns in the control range match the columns you want the macro to manage. The macro will set each managed column to hidden or visible, so it can override a manual hide or unhide in those columns.
- Enter a formula in the first control cell. With the data for column B in B2:B100 and the control result in B1, enter
=IF(COUNTA(B2:B100)=0,"Hide","Show")in B1. If B2:B100 contains no entries, the formula returns “Hide”; if at least one cell contains a value, it returns “Show.” For example, if B17 contains “North,” B1 returns “Show.” COUNTA counts cells containing formulas too, even if a formula displays an empty string, so account for that if your source data uses formulas that return empty text. - Copy the control formula across the managed columns. Select B1 and drag its fill handle across to F1, or copy B1 and paste into C1:F1. Excel adjusts the references as it fills: C1 checks C2:C100, D1 checks D2:D100, and so forth. Confirm the formulas are in the intended cells and each checks its own column, not a fixed B range. If you need to control more columns, extend the control range and later update the code to match.
- Open the worksheet’s code window. If the Developer tab is available, select Developer > Visual Basic. In the VBA editor’s Project Explorer, find the workbook and double-click the specific worksheet tab that contains the control formulas. Paste the code in that worksheet’s code window, not in a regular module and not in ThisWorkbook. If Project Explorer is missing, press Ctrl+R in the VBA editor.
- Add the calculation event macro. Paste this event procedure into the worksheet code window. It checks each control result after that worksheet recalculates and hides a column only when the corresponding cell says “Hide.” Any other result, including “Show,” makes the column visible.
Private Sub Worksheet_Calculate()
Dim controlCell As Range
' Apply each control result to its matching column.
For Each controlCell In Me.Range("B1:F1").Cells
Me.Columns(controlCell.Column).Hidden = _
(LCase$(Trim$(CStr(controlCell.Value2))) = "hide")
Next controlCell
End Sub
- Match the code range to your layout. The line
Me.Range("B1:F1")tells VBA which cells to read. If your formulas are in H2:L2, change that range to H2:L2; the macro will then manage columns H:L. Keep the control cells in the same columns as the columns they control. The code compares the result without regard to capitalization and trims spaces, so “hide” and “ Hide ” also trigger hiding. - Save as a macro-enabled workbook. Close the VBA editor and return to Excel. Choose File > Save As, then select Excel Macro-Enabled Workbook (*.xlsm) as the file type. An .xlsx file cannot retain VBA code. Reopen the .xlsm file if necessary and choose to enable macros only when you trust the workbook and its source.
- Test the rule by changing the data. With calculation set to Automatic, clear the entries in B2:B100 and check that column B hides after recalculation. Add a value to B17 and check that B becomes visible. Repeat for another managed column to confirm the formulas were copied correctly. If calculation is manual, press F9 to recalculate; on a Mac, use Fn+F9 where the function keys are assigned to system controls, or choose Formulas > Calculate Now.
- Adjust the range for real data. The sample checks only rows 2 through 100. If entries can go beyond row 100, expand each control formula’s range, or choose a suitable larger range. Avoid using whole-column ranges without a reason: they can be less efficient in a workbook with many formulas. Keep the header or control row separate from the data range to prevent the formula from counting itself.
On a Mac, the VBA editor can usually be opened through Developer > Visual Basic; depending on keyboard settings, Option+F11 or Fn+Option+F11 may also open it. You can enable the Developer tab in Excel’s ribbon settings. The worksheet event code and the .xlsm requirement are the same. Excel for the web does not execute this VBA event, so use the desktop app to make the columns respond automatically.
Common Mistakes
- Expecting a formula alone to hide a column. Functions such as IF return a value in a cell; they do not change worksheet layout. The VBA event is the part that changes visibility.
- Pasting the event into the wrong place. Worksheet_Calculate must be in the code window for the sheet whose formulas recalculate. If it is in a standard module, Excel will not call it as a worksheet event.
- Using different control cells in the formula and code. If formulas are in B1:F1 but the code reads A1:E1, it will apply the wrong results. Align the range and the target columns before testing.
- Saving as .xlsx or declining to enable macros. Either prevents the VBA behavior from running. Save as .xlsm and enable macros only for a workbook you trust.
- Assuming every visually blank cell is empty. COUNTA counts a cell with a formula even when that formula displays nothing. A column that looks blank can therefore return “Show.” Use a condition that reflects how your source data is actually stored.
- Protecting the sheet without allowing the needed action. A protected worksheet may block VBA from hiding or unhiding columns. Test with protection off first, then review the protection settings and your organization’s rules before changing them.
Tips
- Use clear labels or a different fill color for the control row while building the workbook, then hide that row if appropriate. Hiding the control row does not stop its formulas from calculating.
- Make formula results exactly “Hide” or “Show” rather than using a long explanatory sentence. That keeps the control logic easy to inspect and avoids ambiguity.
- Keep a note near the worksheet explaining which row controls visibility and which columns the macro manages. This is especially helpful if someone else maintains the workbook.
- Test boundary cases: no data, one entry, and data at the last row included in the formula. The result should reflect the condition you intended in every case.
- If the rule depends on values on another sheet, put the control formula on the sheet whose columns should change and make it refer to the other sheet. Recalculate and confirm that the control sheet updates when those source values change.
- For a one-time change rather than automatic behavior, use the formula result to decide what to hide and then select the relevant column letters, right-click, and choose Hide. That manual method does not update when the formula changes.
Troubleshooting
The column does not hide after the data changes. First confirm that the control formula now displays “Hide” in the matching cell. Then check that the event code is in that worksheet’s code window, the macro is enabled, and the workbook is saved as .xlsm. If calculation is manual, recalculate with F9 or select Formulas > Calculate Now. In Excel for the web, open the workbook in desktop Excel because web Excel does not run VBA.
The wrong column hides, or columns all respond the same way. Inspect the formula in each control cell and confirm it points to the data in its own column. Verify that the range named in the VBA code starts and ends at the actual control cells. For example, a control range of B1:F1 maps B1 to B and F1 to F; do not shift the formula row without adjusting the code.
A column remains visible even though it looks empty. Select its control cell and inspect the formula result, not just the displayed data range. COUNTA treats a formula returning an empty string as content. Also check for spaces or other invisible characters typed into cells. If the output says “Show,” revise the condition to match what should count as empty, then test the revised formula on a few representative rows.
Excel reports a VBA error or the sheet is protected. Confirm that the range in the code exists and that the worksheet is not protected against column formatting. If the code was pasted into a standard module, move it to the relevant worksheet object. If company policy blocks macros, do not bypass it; ask your administrator about an approved solution. Excel for the web and some managed environments may not permit this workflow.
Frequently Asked Questions
Can an Excel formula hide a column without VBA?
No. A worksheet formula can calculate a result such as “Hide,” but formulas cannot directly change a column’s hidden state. VBA can apply the result automatically; without VBA, hide the column manually or use a different layout, such as filtering rows or grouping columns.
Will the columns update whenever the formula changes?
They update when the worksheet recalculates and the Worksheet_Calculate event runs. With Automatic calculation, ordinary edits to precedent cells generally trigger recalculation. In Manual mode, recalculate explicitly. If a formula depends on an external connection or data refresh, the visibility update occurs when the relevant formulas recalculate.
Can the macro hide several columns based on different conditions?
Yes. Give each column its own control formula, with each result returning “Hide” or “Show,” and include all those control cells in the code’s range. The example already does this for B:F. Check that each formula refers to the correct data before using the workbook.
Can I use a different word than Hide?
Yes, but change the comparison in the VBA code to match the word you want the formula to return. The sample checks for “hide” without regard to capitalization and ignores leading or trailing spaces. Keep one consistent result for hiding and use another result, such as “Show,” to make the column visible.
Will it work in Excel for the web?
No, the web app does not run this VBA event. You can open and view the workbook there, but automatic formula-based hiding requires desktop Excel with macros enabled. Excel for Mac supports VBA in its desktop app, though editor access and keyboard shortcuts can differ.
Can I still hide a column manually?
You can, but the next worksheet recalculation resets every managed column according to its control result. To keep a manual hide, remove that column from the code’s control range or change its formula so it returns “Hide.” Columns outside the managed range are not changed by this macro.
Related
- Excel AVERAGE Tips and Tricks for Everyday Spreadsheet Tasks
- Excel: Sum Values Based on Other Values
- Excel SUM Visible Rows: Add Filtered Data Correctly
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort Excel by Color: Cells, Font Colors, and IconsTo sort Excel rows by color in Microsoft 365 for Windows, select your data, choose Data > Sort,…
- HLOOKUP in Excel With Multiple Sheets: A Practical GuideHLOOKUP does not search multiple worksheets in a single lookup operation. To search several sheets, combine separate HLOOKUP…
- Sort Excel by Color: Steps, Options, and TroubleshootingTo sort an Excel sheet by color in Microsoft 365 for Windows, select a cell in your data,…
- Excel Column Name to Number: Formula and ExamplesIf cell A1 contains an Excel column name, enter =COLUMN(INDIRECT(A1&"1")) to return its number: A returns 1, Z…
- Calculate Profit Margin % in Excel: Formula and StepsTo calculate profit margin in Excel, divide profit by revenue. With revenue in B2 and total costs in…
- Excel Link to Named Range: Formulas and MethodsTo create a clickable link to a named range in Excel for Microsoft 365 on Windows, use the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.