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?


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.

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

154 Responses to When unhide row doesn’t work…

  1. Julie says:

    Thank you so much! This worked great for me!


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


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


  4. Notsohelpless says:

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


  5. BF says:

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


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


    • MF says:

      Do you have filter on?


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


  7. Ritwik says:

    Really great. It helped me a lot ..


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.