Yes—Excel can hide columns according to dates, but a formula alone cannot change a column’s hidden state. For Microsoft 365 on Windows, use a short VBA macro to compare each column’s date header with a cutoff date; for a one-time change, select the columns manually and use Home > Format > Hide & Unhide > Hide Columns.
What “hide columns by date” means in Excel
Usually, the dates are arranged across a header row, with each date identifying a month, week, or day in the column below it. A report might have month-end dates in C4:AG4 and figures beneath them. You may want to hide dates before a selected cutoff, such as every month earlier than January 1, 2025, while leaving the cutoff date and later dates visible.
Hiding a column changes the worksheet view; it does not delete the date or its data. Formulas can still refer to hidden cells, and someone who can use the workbook can unhide the columns. That makes hiding useful for tidying a report, but not for protecting confidential information.
The important distinction is that Excel formulas calculate values in cells, while hiding a column is a worksheet action. A formula can label a date as “old” or return TRUE or FALSE, but it cannot directly set the column’s Hidden property. For a repeatable date-driven hide, VBA or another automation method is needed.
Choose a method for your situation
The best approach depends on whether the date changes often and whether the result must update automatically. Consider these options:
- Hide columns manually: Best for a quick, one-time cleanup. Select the relevant columns and hide them through the ribbon or the column-header context menu.
- Group columns: Best when readers should be able to expand and collapse a section themselves. Grouping is controlled manually and does not decide which columns to hide based on a date.
- Use a helper row: Best when you want a visible TRUE/FALSE or label showing which dates meet a rule. It helps identify columns but does not hide them by itself.
- Use VBA: Best when the same date rule must be applied repeatedly in desktop Excel. A macro can compare the header dates with a cutoff and hide or show columns accordingly.
Excel’s AutoFilter is designed to filter rows based on field values, not to hide individual date columns. Conditional formatting can change a cell’s appearance, but it cannot hide a column either. These features may help present or inspect data, but they are not substitutes for a column-hiding action.
Hide columns with a VBA date rule
This example assumes the cutoff date is in B1, real Excel dates are in C4:AG4, and each date labels the column beneath it. It hides columns whose header date is earlier than the cutoff. The cutoff date itself remains visible. The macro also unhides columns in that range whose headers are not dates, so an earlier run does not leave those columns hidden by mistake.
Sub HideColumnsBeforeCutoff()
Dim ws As Worksheet
Dim cutoff As Date
Dim headerCell As Range
Set ws = ActiveSheet
If Not IsDate(ws.Range("B1").Value) Then Exit Sub
cutoff = CDate(ws.Range("B1").Value)
Application.ScreenUpdating = False
For Each headerCell In ws.Range("C4:AG4").Cells
If IsDate(headerCell.Value) Then
headerCell.EntireColumn.Hidden = _
(CDate(headerCell.Value) < cutoff)
Else
headerCell.EntireColumn.Hidden = False
End If
Next headerCell
Application.ScreenUpdating = True
End Sub
For example, if B1 contains January 1, 2025, a column headed December 31, 2024 is hidden; a column headed January 1, 2025 or later is shown. To hide the cutoff date too, change the comparison from < to <= in the macro. The date headers should be actual Excel dates, not text that merely looks like dates; consistent date formatting makes them easier to check.
To add the macro, open the Visual Basic Editor with Alt+F11 on Windows, choose Insert > Module, and paste the code into the module. Return to Excel and run HideColumnsBeforeCutoff from Developer > Macros. If the Developer tab is missing, enable it under File > Options > Customize Ribbon. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) to retain VBA code. On Mac, the Visual Basic Editor is available from Developer > Visual Basic; the keyboard shortcut can depend on the keyboard and function-key settings.
The code uses ActiveSheet, so it acts on whichever worksheet is active when the macro runs. If the workbook has several similar sheets, use a specific worksheet reference instead of ActiveSheet to reduce the risk of running it on the wrong one. Also adjust B1, C4:AG4, and row 4 to match your own layout.
Use a helper formula to identify dates
A helper row can make the rule visible before you hide anything. With the cutoff date in B1 and date headers in C4:AG4, enter =C$4<$B$1 in C5 and copy it across the row. For a header of December 31, 2024 and a cutoff of January 1, 2025, the formula returns TRUE; for a header equal to or later than the cutoff, it returns FALSE. This assumes both cells contain valid Excel dates.
The helper result is a check, not an automatic hiding command. You can use it to verify which columns the macro should hide, or to guide a manual selection. If you prefer words, a formula such as =IF(C$4<$B$1,"Before cutoff","Keep") can display a readable status. In Microsoft 365, you could also conditionally format the helper cells to make TRUE results stand out, but formatting them still does not hide their columns.
Keep helper formulas outside the columns you intend to hide if you need to see the results while reviewing the sheet. If you place the helper row beneath each date header, the helper cells will disappear along with their columns, which is expected but less useful for auditing.
Run the rule again when the cutoff changes
The sample macro runs only when you run it. Changing B1 afterward does not automatically recalculate the hidden-column state, even though formulas in the worksheet may recalculate. Run the macro again after changing the cutoff to refresh the view. This explicit workflow is often preferable for reports because it avoids hiding columns unexpectedly while someone edits the sheet.
If you need the view to refresh automatically, VBA can be extended with a worksheet-change event that watches B1 and calls the macro. That event code belongs in the worksheet’s code module, not in the ordinary module used for the macro. It should be designed carefully: event procedures can trigger again when code changes cells, and an error can leave Excel events disabled if they are not restored. For many workbooks, a button assigned to the macro is simpler and easier for other users to understand.
To create a button, show the Developer tab, choose Developer > Insert, and select a Button under Form Controls. Draw it on the sheet and assign HideColumnsBeforeCutoff. Add a clear label such as “Apply date view.” Users can then change the cutoff and deliberately refresh the view.
Manual hiding, grouping, and unhide controls
For a one-off change, click the first column letter to hide, hold Shift, and click the last column letter. Right-click a selected column heading and choose Hide, or use Home > Format > Hide & Unhide > Hide Columns. To show the columns again, select the column headings on both sides of the hidden range and choose Home > Format > Hide & Unhide > Unhide Columns. If the first or last worksheet column is hidden, use the Name Box to select the affected column range before unhiding.
Grouping is useful when people need to switch between a summary and detailed date columns. Select the columns, then choose Data > Group. Excel adds an outline control that can collapse or expand the group. Grouping does not inspect dates, so you still choose what belongs in the group; it is a convenient navigation tool rather than a date rule.
Common problems and checks
- Dates are stored as text: A cell may display 1/1/2025 but contain text rather than a date value. Check the cell’s value and formatting, and convert text dates to real dates before relying on date comparisons. Regional date conventions can make ambiguous text especially risky.
- The wrong columns are affected: Confirm the cutoff cell, header row, and first and last date columns in the macro. Also verify the active sheet when running a version that uses ActiveSheet.
- Some columns remain hidden: The example only changes columns C:AG. Columns outside that range are untouched. Unhide the necessary columns manually or expand the range deliberately.
- The cutoff is not a date: The macro exits if B1 is not recognized as a date. Enter a valid date in B1 rather than a label such as “January onward.”
- The macro does not run: Check that the file is saved as .xlsm and that macros are permitted by your organization’s security settings. Do not enable macros in a workbook from an untrusted source.
- Hidden data is mistaken for deleted data: Hiding changes visibility only. Formulas, charts, and other workbook features may still use the hidden values, depending on their settings and construction.
Before relying on the result, test the rule with a cutoff that should hide at least one date and leave at least one visible. Then inspect the column headers and confirm the comparison matches your intent. If the workbook is shared, explain how to rerun the macro and how to restore the full view.
Frequently Asked Questions
Can Excel hide columns automatically when a date changes?
Not with a worksheet formula alone. A VBA macro can apply the rule, and an event procedure can run it when a watched cell changes, but the basic macro must be run again after the cutoff changes.
Can conditional formatting hide a column based on a date?
No. Conditional formatting changes how cells look; it does not set columns to hidden. Use it to flag dates, then hide columns manually or use VBA.
Does this work in Excel for the web?
Excel for the web can display workbooks that contain VBA, but it cannot create, run, or edit VBA macros. Run the macro in desktop Excel for Windows or Mac. For web-based workflows, consider a manual view or an organization-approved automation option.
Will hiding a column remove its values from formulas?
No. Hiding a column does not erase its contents or normally stop formulas from referencing those cells. It is a display choice, not a data deletion or confidentiality feature.
How do I show every hidden column again?
Select the worksheet columns, then choose Home > Format > Hide & Unhide > Unhide Columns. You can also select the columns surrounding a hidden range and use the same command.
Can I hide dates later than the cutoff instead?
Yes. In the macro, change the comparison to greater than the cutoff, such as CDate(headerCell.Value) > cutoff. Decide whether dates equal to the cutoff should remain visible or be hidden, and use > or >= to match that rule.
Save a safe, understandable workflow
For an occasional report, manual hiding or grouping may be enough. For a repeatable date-based view, the macro approach is more reliable because it applies the same comparison across a defined range. Keep the date row and cutoff cell clearly labeled, test the boundary rule, and document how to restore the columns. Before running any macro on an important workbook, save a separate copy: VBA actions cannot be undone with Ctrl+Z.
Related
- Excel Data Validation: Add Criteria and Rules
- Excel Data Table With 3 Variables: Limits and Workarounds
- Excel Data Table With Multiple Outputs: How It Works
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 Put Percentages in Google Sheets: A Quick GuideTo put a percentage in Google Sheets, select the cell and choose Format > Number > Percent, or…
- Outlook: How to Stop Emails From Being Archived by AccidentTo keep messages in your Inbox, turn off AutoArchive in classic Outlook, remove Archive from Outlook on the…
- Outlook Closing Signature: How to Create OneTo create a closing signature in Outlook, open the signature settings for your version, write your sign-off and…
- Excel Time Picker: Ways to Choose a TimeExcel for Microsoft 365 on Windows does not include a built-in pop-up clock for choosing a time in…
- Analysis Using Excel: Tools, Methods, and ExamplesYes—Excel can analyze data using formulas, PivotTables, charts, and statistical tools. For a quick start, organize your data…
- How to Add a Header Template in WordTo add a header template in Microsoft Word, open the document, choose Insert > Header, select a built-in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.