/ How to

How To Find and Delete All Hidden Rows and Columns in Excel Workbooks

Working with large Excel workbooks containing hidden rows and columns can be frustrating. Over time, hidden areas accumulate and create file bloat and confusion. Fortunately, Excel provides easy ways to locate and remove hidden rows and columns across an entire workbook.

Finding Hidden Areas

The first step is identifying hidden rows and columns. Here are three methods:

1. Visually Scan for Hidden Row and Column Indicators

Look along the row numbers and column letters. Double lines between rows or columns indicate a hidden area. You can also try selecting all cells. The borders around hidden rows and columns will remain visible.

2. Use the Go To Special Dialog

This dialog selects visible cells, highlighting the hidden areas:

  1. Go to Home > Find & Select > Go To Special
  2. Select Visible cells only
  3. Click OK

3. Press Keyboard Shortcuts

Select all visible cells with:

  • Ctrl + ; (semicolon) for Windows
  • ⌘ + ; (semicolon) for Mac

This selects visible cells and shows hidden areas.

Deleting Hidden Rows and Columns

Once found, deleting hidden areas is easy:

Use the Document Inspector

The Document Inspector scans for hidden content and lets you delete it in bulk:

  1. Go to File > Info > Check for Issues > Inspect Document
  2. Click Inspect
  3. Select Remove All beside Hidden Rows, Columns and Worksheets

Use VBA Macros

Macros automate deletion in one click:

'Delete hidden rows
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
    If Rows(i).Hidden = True Then
        Rows(i).EntireRow.Delete
    End If
Next

'Delete hidden columns 
For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1 
    If Columns(i).Hidden = True Then
        Columns(i).EntireColumn.Delete
    End If
Next

Tips for Managing Hidden Areas

Instead of permanently hiding rows and columns, use these methods:

  • Filtering – Temporarily hide irrelevant data
  • Grouping – Collapse details you don’t need to see
  • Multiple Worksheets – Separate data onto different sheets
  • Separate Workbooks – Isolate data into different files

Conclusion

Finding and removing hidden rows and columns keeps workbooks lean. Scan for indicators, use Go To Special, or run macros to delete hidden areas in bulk. With a little cleanup, you can eliminate bloat and confusion.

Additional Resources

Related Posts