My Excel Columns Or Rows Not Hidden But Missing?!
Why Are The Columns/Rows Missing?
If you landed on this article, you’ve probably been pulling your hair out trying to figure out why you can’t unhide your Excel spreadsheet rows or columns. They just appear to be missing!
This is not a glitch or bug within your Excel file, it’s actually an intended spreadsheet feature (when used correctly). The feature that is causing this is Excel’s Freeze Panes button.
Why Is This Happening?
When you apply Freeze Panes to your spreadsheet, it essentially locks the view based on where the window is scrolled to. This means that if there are rows/columns that are not visible to either the left or top of your spreadsheet, these cells will remain unviewable once Freeze Panes has been activated. This essentially prevents you from accessing (unhiding) them.
An alternate scenario where this will occur is when Row 1 is part of an outline group and is collapsed prior to the Freeze Pane being applied. Oddly enough, this does not occur is Column A is part of a collapsed group as you can freely expand/collapse the group after the Freeze Pane has been applied.
Steps To Access/Fix The Locked Rows Or Columns
- Go to the View tab in your Excel Ribbon
- Click the Freeze Panes menu button within the Windows button group
- Select the Unfreeze Panes button
Now you should be able to access those “missing” rows or columns.
Why Is Hiding With Freeze Panes Useful?
Locking (hiding) rows or columns with the freeze panes button can be a passive-aggressive way to keep others from vital formulas you don’t want messed with. If you don’t want to deal with setting and remembering passwords, this can be a great alternative to preventing your users from easily accessing sections of your spreadsheet.
Hidings Rows or Columns With Freeze Panes
If you would like to hide or lock yourself from being able to access rows in the top portion or columns on the left side of your spreadsheet, you simply need to do the following:
- Scroll horizontally and vertically to position any rows/columns you wish to hide so they do not appear on your screen
- Select the cell you wish to designate for your Freeze Panes application
- Navigate to the View Tab and enable your Freeze Panes
After you have completed these steps, you will be unable to access the rows or columns you locked from view until your click the Unfreeze Panes button.
Hiding Grouped Rows With Freeze Panes
If you would like to hide or lock yourself from being able to access an outline grouping of rows in the top portion of your spreadsheet, you simply need to do the following:
- Apply your outline grouping to the rows you wish to collapse
- IMPORTANT! In order for this to work, Row 1 must be part of your grouping. If it is not, you will still be able to expand the outline group when the Freeze Panes are applied.
- Select the cell you wish to designate for your Freeze Panes application
- Navigate to the View Tab and enable your Freeze Panes
After you have completed these steps, you will be unable to access the collapsed rows until your click the Unfreeze Panes button.
I Hope This Helped!
Hopefully, I was able to explain how you can unlock or lock rows/columns in your spreadsheet using the Freeze Panes feature. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.