Wednesday, January 28, 2009

Unhide in Excel won't let you scroll

I had a user with an odd problem today. They received a spreadsheet from one of our other offices, and one of the sheets had a bunch of hidden rows. The information she needed was in those hidden rows, so she had to unhide them. As soon as she did that, the sheet she was on would freeze. Everything was responsive, and she could click on cells, but she couldn't scroll any direction to see any other information. Other sheets within that same workbook were working fine. I did a quick search and found this, which solved her problem:

1. Unhide the hidden rows/columns like you normally would
2. On the menu bar, go to Windows->Unfreeze panes

For some reason the sheet was freezing the visible data into a single pane after it was unhidden, which gave the illusion that the sheet was locked up.

*Note: If you go to the Windows menu in Excel and you don't have the option to Unfreeze panes (you'll see Freeze panes instead), then you're having a different issue. Sorry, but I don't have a solution for that, at least not yet

14 comments:

Dale (DJ) said...

Thank you, this solved my problem!

rachel said...

Brilliant!!!! Just spent an hour trying to fix this problem, it's very odd indeed! Thank you so very very much.

Anonymous said...

I know this is an old post, but thought I'd point out why this happened in the first place.

Someone would have hidden one or more rows near the top of the sheet. At some later point in time, someone used Windows -> Freeze to freeze the top row so the headers were always visible. However, when they froze the top row, they also unknowingly froze one or more of the hidden rows.

When your user chose to unhide all the hidden rows, Excel treated the top x rows (including the previously hidden rows) as frozen, so when the user tried to scroll, nothing moved on the screen, because all of the visible rows at the top of the spreadsheet were frozen.

Hope that makes sense!

Mike said...

Excellent! Thanks

Anonymous said...

Thank you so much! Problem fixed and I spent only a few minutes on it (since I decided to search for an answer rather than struggle).

Carpy said...

Appreciated :)

faysal said...

Life saver from an angry user. after trying 30 min i turned to Google and found this post to solve my user problem.

so Thank you

Anonymous said...

I know it's an old post, but awesome! I've run into this before and never figured out why it was happening. Thanks

Anonymous said...

Thank you! This was a constant annoyance at work that has finally been addressed!

Anonymous said...

Thanks all, very useful post and comments

Craig Oliver said...

Thanks for posting this! This exact thing happened to me and I couldn't figure out why Excel wasn't scrolling. Very helpful -- thanks again!

Anonymous said...

Ditto all the above!! This had been happening with a shared spreadsheet at a new job, and it really baffled me. I'd never encountered it before, then decided to consult Google. Thank you!!!!

Anonymous said...

Thank you so much for keeping this out there, it solved the problem I had for a long time and couldn't figure out why it was doing that. It was a macro file, so I didn't think of unfreezing panes. Thank you!!!

Kenneth Nicklowicz said...

Thanks! I do I.T for a living (contracted as well as my own company) and this saved a lot of headache. Thanks