This above video was made just recently. You may read this post to understand more. Note: Please turn on CC for English subtitles.
Hidden rows cannot be unhidden? Why?
Although today is April Fools’ Day and the question sounds like an April Fool’s question, this post is not about to fool anyone. Just another real case to share.
For an unprotected sheet, hidden rows can be unhidden easily. This is so basic. I thought so too before I received a strange worksheet sent by a colleague. (I believe he had no idea what he did.)
In the screenshot below, rows 2:15 are hidden. It is so obvious. Isn’t it?
Then I tried the normal ways to unhide the rows, but failed.
- The worksheet is not password-protected.
- Also I tried to press Down Arrow in A1 and observed the change in Name Box. It changed from “A1” to “A16”, which means rows 2:15 are hidden.
But why couldn’t I unhide them?
To test the Hide and Unhide be working properly, I have hidden rows 18:19 (by right-click & hide) and then unhide the whole worksheet:
Strange enough, rows 18:19 are back but not rows 2:15.
That was one of the weird things I encountered in using Excel.
While I was lost and had no clues, my “shaking” hand gave me the answer:
It is the row height!
It’s difficult to show what I did with my “shaking” hand in a static photo. Let’s take a look at the result when I changed the row height for rows 1:16 in the screenshot below:
All the hidden rows are back now!
This interesting behavior made me do a little test on different row heights.
Here’s the findings:
- For row height <=0.07, row are basically hidden where we can “unhide” the row as normal
- For row height from 0.08 to 0.67, row looks like hidden (We cannot move to “hidden row” by arrow key) but are not actually. We cannot “unhide” the row as normal. We need to change the row height to have them back.
- For row height >=0.68, we will see a “noticeable” row, so that we know the row is not hidden.
Row height set at 0.68 for rows 2:6
How do I know these? Just by experiment.
Learning is a series of curiosity, observation, and trials & errors.
If the above doesn’t help you to unhide rows, there may be a chance that the hidden rows was a result from advanced filter. You may read this post for more details.
wmfexcel 
Thank you!!! I spent hours trying to figure out what was going on!! I cannot thank you enough!
LikeLike
You are welcome! Glad it helped! 🤓
LikeLike
Oh my gosh and golly, you have just saved my world.
LikeLike
😀yeah!
LikeLike
Ah thank you so much for posting this! you’ve immediately eliminated my frustrations – I had no idea it could have been to do with the row height 🙂
LikeLike
You are welcome! Glad it helped 😉
LikeLike
Wow, I couldn’t figure out for the life of me why the workbook I share with a couple of people in the office wouldn’t unhide several rows. Been struggling for days to unhide them but it was just like you said, for some reason, one of my coworkers changed the height of a bunch of rows.
The help is much appreciated!
LikeLike
Hi Patrick
You are welcome. Glad it helped. 🙂
LikeLike
Thank you so much! This worked great for me!
LikeLike
You are welcome. Glad It helped! 😀
LikeLike
Sorry I stumbled on this thread due to hidden columns. Turns out I just had to remove the frozen cells and the columns came out.
LikeLike
Hi Joni, no matter how you came to this blog, you are welcome. Glad to her that you have solved your problem.
LikeLike
I didn’t expect my problem was due to frozen panes too! Thank you!
LikeLike
Glad you found a solution here. 😃
LikeLike
Yes, it was the frozen cells that was my problem too! Thanks so much for mentioning that! I kept looking at filters (was fine) kept selecting all and setting the cursor at A1 (didn’t help), multiple tries at “unhiding” and “row height” but for me it was the frozen cells!
LikeLike
Totally understood. 🙂 Glad you find the way out! 🙂
LikeLike
Thanks for sharing. I had also tried all those regular tricks documented elsewhere, but the filter was the key. Thanks for mentioning that. As soon as I could get back row 1, i could undo filters , then unhide all other 200 rows. It worked.
LikeLike
You are welcome. Glad It helps. Btw you can clear all filter without going back to row 1.
Take a look at this post:
https://wmfexcel.com/2014/01/30/why-do-you-turn-off-auto-filter-and-then-turn-it-on-again/
LikeLike
Wow this was just my coworker’s story! Thanks for the help.
LikeLike
Welcome. Glad It helps! 😃
LikeLike
Thank you. We have a spreadsheet with 100s of hidden rows. This saved us ALOT of time.
LikeLike
Welcome. Glad it helped. 😀
LikeLike
this did not work for me i have tried everything on this page also to go to the format page and change row height that way but my cells are still hidden
LikeLike
Do you have filter on?
LikeLike
I had the same result – changing the row height manually or using Format row height, Autofit row height – neither worked. So I checked in View Code to see if there were any VBA bits and pieces that might be affecting which rows can be seen – nothing. I highlighted the whole sheet and in Data, Sort & Filter, I hit Clear. This un’hid’ all those rows that otherwise previously didn’t respond to the Unhide instruction. I know this is a long time after your previous message but it might help someone else as it’s ok with a small worksheet to expand them one at a time, but no good if you’ve got a monster sheet with many rows like this.
LikeLike
Thanks for sharing your story! 😄
LikeLike
Wow thank you for this comment! I have tried everything to get the rows unhidden (over 400 rows) and nothing worked — until I saw this comment and this work! thank you!
LikeLiked by 1 person
You are welcome. Glad it helped! 😀
LikeLike
Really great. It helped me a lot ..
LikeLike
Thanks. Glad It helps! 😀
LikeLike