To hide columns that contain no data in Excel for Microsoft 365 on Windows, identify the blank columns, select their column letters, then choose Home > Format > Hide & Unhide > Hide Columns. Excel has no built-in command that automatically hides every empty column in a worksheet, but a helper formula or a short VBA macro can help you find them.
What Counts as a Column With No Data?
Before hiding columns, decide what “no data” means for your workbook. A column can look empty but still contain a formula, a space character, or other content. A formula that displays an empty string, for example, may make a cell appear blank even though the cell contains a formula. Formatting alone, such as a fill color or border, does not mean a cell has data.
For many worksheets, the useful test is whether a column contains any visible values in the rows you care about. That can include text, numbers, dates, logical values, and errors, while treating genuinely empty cells and formulas that return an empty string as blank. If instead you need to find columns with no cell contents at all, formulas themselves should count as content, even when their displayed result is blank.
Also define the rows to check. A worksheet may have a title, notes, or headers above the actual dataset. If those cells are included, a column with no records may still appear nonempty because it contains a heading. For a table with headers in row 1 and records in rows 2 through 500, you might check only rows 2 through 500.
Hide Empty Columns Manually
For a small sheet, checking the columns and hiding them directly is usually quickest. Inspect the relevant rows, then select the letter at the top of each column that is entirely empty. To select several neighboring columns, click the first column letter and Shift-click the last. To select separate columns, hold Ctrl while clicking each column letter on Windows.
- Confirm the rows that define your dataset and check each candidate column across those rows.
- Select the column letter or letters for columns that are empty throughout the chosen rows.
- Go to Home > Format > Hide & Unhide > Hide Columns.
- Check the remaining sheet to make sure you did not hide a column that contains a needed heading, formula, or value.
Hiding a column changes its display, not its contents. Formulas can still refer to hidden cells, and hidden values can still affect calculations. This is useful for simplifying a report, but it is not a way to remove or protect data.
Use a Helper Formula to Find Blank Columns
A helper row is more reliable than judging a wide worksheet by eye. Put a check formula in a spare row below the data, with one formula for each column. For data in rows 2 through 500, enter the following in A501 to check column A:
=COUNTBLANK(A2:A500)=ROWS(A2:A500)
This returns TRUE if every cell in A2:A500 is blank according to COUNTBLANK, and FALSE if at least one cell is not blank. COUNTBLANK also treats a formula that returns an empty string as blank. A cell containing a space is not blank, so it makes the result FALSE. Fill the formula across the helper row to check the corresponding columns; Excel adjusts the column references as you fill right.
Once the results are visible, use them to select the matching blank columns by their column letters, then hide those columns through Home > Format > Hide & Unhide > Hide Columns. The helper results do not hide columns by themselves. Keep the check row outside the range being checked, or the helper formulas would make their own columns appear nonempty. You can delete the helper row after hiding the columns if you no longer need it.
If you want formulas themselves to count as content—even formulas that display nothing—use COUNTA instead. With data in A2:A500, this check returns TRUE only when COUNTA finds no entries in that range:
=COUNTA(A2:A500)=0
Choose the test that matches your purpose. A report intended to suppress visually blank columns may need COUNTBLANK; an audit looking for columns with no entered values or formulas may need COUNTA. Neither test can determine whether a value is meaningful to your particular project.
Hide Many Columns With a VBA Macro
If you repeat this task often, a macro can inspect the cells in the worksheet’s used range and hide columns whose cell values are all empty. This example treats formulas returning an empty string as visually blank, but counts errors, zero, FALSE, and spaces as content. It checks the active worksheet, so select the intended sheet before running it.
The macro only checks the cells within the used range. Excel’s used range can extend farther than expected if cells were previously formatted, so an unusually large used range may make the scan slower. Review the code and save a copy of your workbook before running a macro; VBA actions cannot be undone with Ctrl+Z.
Sub HideColumnsWithNoDisplayedValues()
Dim ws As Worksheet
Dim used As Range
Dim colRange As Range
Dim cell As Range
Dim colIndex As Long
Dim firstCol As Long
Dim lastCol As Long
Dim hasContent As Boolean
Set ws = ActiveSheet
Set used = ws.UsedRange
firstCol = used.Column
lastCol = used.Column + used.Columns.Count - 1
For colIndex = firstCol To lastCol
Set colRange = ws.Range(ws.Cells(used.Row, colIndex), _
ws.Cells(used.Row + used.Rows.Count - 1, colIndex))
hasContent = False
For Each cell In colRange.Cells
If IsError(cell.Value2) Then
hasContent = True
Exit For
ElseIf Len(CStr(cell.Value2)) > 0 Then
hasContent = True
Exit For
End If
Next cell
If Not hasContent Then ws.Columns(colIndex).Hidden = True
Next colIndex
End Sub
To use it in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code, and run the procedure with the intended worksheet active. The workbook may need to be saved in a macro-enabled format, such as .xlsm, to retain the code. On Mac, open the Visual Basic Editor with Option+F11 or use the Developer tab if that shortcut is unavailable; keyboard behavior can vary with the Mac’s function-key settings. Excel for the web cannot create, run, or edit VBA macros, so use desktop Excel for this method.
How to Unhide Columns
To restore hidden columns, select the column headers on both sides of the hidden range. For example, if columns C and D are hidden, select columns B and E, then choose Home > Format > Hide & Unhide > Unhide Columns. If the hidden columns are at the far left or right of the sheet, select the whole worksheet using the triangle at the top-left corner where the row and column headings meet, then use the same Unhide Columns command.
You can also select a range that spans the hidden columns before using the command. Unhiding makes the columns visible again; it does not change their contents or formulas. If a column remains difficult to select because its width is zero, use the neighboring headers or select the entire worksheet rather than trying to click the hidden column itself.
Common Problems and Misconceptions
- Go To Special > Blanks does not identify wholly empty columns. It selects blank cells within a selected range, including blanks in columns that also contain data. It is useful for finding gaps, but not by itself for determining that an entire column has no data.
- A formula can look blank without being absent. COUNTA counts cells containing formulas even if the formulas display empty strings. COUNTBLANK is a better fit when those results should count as visually blank.
- Spaces are not empty cells. A cell containing one or more spaces can look blank but will not be treated as blank by the helper formula. If that matters, inspect or clean the source data rather than hiding the column based on appearance alone.
- Hidden is not the same as deleted. Hidden values remain in the workbook and can still be referenced in formulas, charts, and other features. Do not hide columns as a confidentiality measure.
- Filters work vertically, not as a general blank-column switch. Excel’s filters can show or hide rows based on field values, but they do not automatically hide worksheet columns that have no entries.
Frequently Asked Questions
Can Excel automatically hide all blank columns?
There is no standard worksheet command that automatically hides every blank column at once. You can identify candidates with a helper formula and hide them manually, or use VBA to automate the check in desktop Excel.
Does COUNTBLANK count a formula that returns an empty string?
Yes. COUNTBLANK treats a formula result of an empty string as blank. If formulas should count as content regardless of what they display, use COUNTA instead.
Will hiding an empty column affect formulas?
Hiding a column does not delete its cells. Formulas that refer to cells in hidden columns can continue to calculate, and unhiding the column reveals its contents again.
How do I hide blank columns without hiding blank cells in used columns?
Check each whole candidate column across the specific data rows, using a helper formula such as COUNTBLANK(range)=ROWS(range). Hide only the column headers whose checks return TRUE; selecting blank cells alone can also select gaps inside columns that contain data.
Can I do this in Excel for the web?
You can select columns and hide them using the available column or Home formatting controls, but the VBA method in this article requires desktop Excel. The exact web interface can differ from the Windows ribbon, and VBA macros do not run in Excel for the web.
Will a column with formatting but no values be hidden by the macro?
The example checks cell values, not formatting, so formatting alone does not count as content. It only scans the worksheet’s used range, however; if formatting has expanded that range, the scan may include more cells than the visible dataset.
Related
- Excel: Hide a Column When a Cell Contains Text
- Excel Heatmap Table: How to Create and Read One
- Excel: Hide Column Headers Without Hiding Data
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 Bold Subtotal Rows in ExcelTo bold subtotal rows created with Excel’s Subtotal command, collapse the outline to level 2, select the data…
- Excel Data Validation: Copy Rules Quickly and SafelyTo copy only data validation in Excel for Microsoft 365 on Windows, copy a cell that has the…
- How to Freeze Multiple Rows in Excel While ScrollingTo freeze more than one row in Excel for Microsoft 365 on Windows, select the cell in column…
- SUMIF Negative Numbers in Google Sheets: TipsTo add only negative numbers in Google Sheets, use =SUMIF(B2:B7,"<0") when the values are in B2:B7; it returns…
- How to Double Space in Word OnlineTo double-space text in Word Online, select the text—or click in a paragraph—then go to Home > Line…
- Page Break in Excel Shortcut: Insert and Adjust BreaksIn Excel for Microsoft 365 on Windows, select the row, column, or cell where the printed page should…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.