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

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 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.

156 Responses to When unhide row doesn’t work…

  1. Patrick says:

    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!

    Like

  2. Julie says:

    Thank you so much! This worked great for me!

    Like

  3. Joni Javier-Guevara says:

    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.

    Like

  4. Mary says:

    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.

    Like

  5. Notsohelpless says:

    Wow this was just my coworker’s story! Thanks for the help.

    Like

  6. BF says:

    Thank you. We have a spreadsheet with 100s of hidden rows. This saved us ALOT of time.

    Like

  7. ronda peyton says:

    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

    Like

    • MF says:

      Do you have filter on?

      Like

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

        Like

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