When unhide row doesn’t work…

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?

Image

Then I tried the normal ways to unhide the rows, but failed.

Image

  • 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:

Image

Strange enough, rows 18:19 are back but not rows 2:15.

Image

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:

Image

All the hidden rows are back now!

Image

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.

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

Free Excel Dashboard Webinar

Excel Expert Course

Advertisements

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Basic, Excel Tips and tagged , . Bookmark the permalink.

135 Responses to When unhide row doesn’t work…

  1. Ritwik says:

    Really great. It helped me a lot ..

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.