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.
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.