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.
Table of Contents
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:
- Go to Home > Find & Select > Go To Special
- Select Visible cells only
- 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:
- Go to File > Info > Check for Issues > Inspect Document
- Click Inspect
- 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
- Show or hide rows and columns – Microsoft Support
- Delete All Hidden Rows and Columns in Excel – Trump Excel