When unhide row doesn’t work…

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.

Power BI Webinar

Advertisements
This entry was posted in Excel Basic, Excel Tips and tagged , . Bookmark the permalink.

79 Responses to When unhide row doesn’t work…

  1. kaf says:

    greatttttttttttttttttttttttttttt than you so much for this great ans

    Like

  2. Janey says:

    THANK YOU!!!!!

    Like

  3. Betsy Garcia says:

    Thank you! Your advice fixed my problem!

    Like

  4. Jack Soberly says:

    In my case i forgot i had filters on. Dumb moment.

    Like

  5. Shashank says:

    Thanks a lot mate 😉 very helpfull

    Like

  6. Malin says:

    Ahh, row height, of course! I hade a very strange behaviour: could not create new rows, could not set filter. Tried all kinds od unhide. For some reason the last rows + second row hade no height! Thanks!

    Like

  7. rharris_jr@hotmail.com says:

    I also noticed on a sheet that i just had the freeze panes was on (Go Figure)

    Like

  8. Kim says:

    I am also really really grateful! In my case, highlighting the whole spreadsheet and setting the row height didn’t make the hidden rows reappear, although I could do them one at a time by going to the first cell in a hidden row and setting the row height for just that row. Unfortunately I had hidden a lot of rows and the thought of having to do each one individually was unpleasant, but I came up with another solution: I saved the file in csv format, and closed it, then reopened. All of the hidden rows had reappeared.

    On another note, I want to congratulate you on the clarity of your instructions and great use of screen captures. I used to do a lot of that and it is not trivial.

    Like

    • MF says:

      Thank you Kim for your kind words.
      And thanks for sharing your experience with us. It’s a bit strange as i tried and i can unhide multiple hidden rows by setting the row height of the entire sheet. 🤔

      Like

  9. Helen says:

    thank you so much. I had the same problem and it worked when I followed your suggestion.

    Like

  10. Kristina W says:

    Thank you so much for this! Row Height was the problem (although I certainly never changed that – just hid the rows).

    Like

  11. Amit Thakur says:

    faced the same issue just now. tried every thing.. and then at the end out of lazyness selected the hidden columns and double clicked .. it came right up.. 🙂

    Like

    • Designer says:

      THANK YOU! None of the other suggestions were working for me! Setting Row Height by page, or by row was not unhiding anything. I was getting very frustrated – your laziness has saved my sanity! What a simple little trick. Thanks again!

      Like

    • Karla says:

      This was driving me nuts; I don’t know what’s causing it! But when the other solutions didn’t work, this one did. I’m grateful!

      Like

    • Emily says:

      OMG I tried everything including all of the above and just double clicked and it all appeared… Thank you

      Like

  12. Bruce Conn says:

    Found you on Google (“cannot unhide”); CTRL A entire worksheet then set all row heights at 20. …and there are the once hidden rows. Hero points for you! I will follow you, oh Master…

    Like

  13. JL says:

    Once I read “It’s the row height” I exclaimed “OMG column A is there”. I went back to my Excel sheet and there was Column A, not missing but just too small to be seen. Thank you!!!

    Like

  14. Gerry valle says:

    Adjusting the height did not help my worksheet, however, I followed Microsoft Excel suggestion: “…Remove the filter by selecting a cell within the filtered range (in my case – the whole sheet), and on the Home tab, in the Editing group, click Sort & Filter, and then click Clear…” That helped me find my data.

    Liked by 1 person

  15. Sharlene says:

    I JUST had this exact problem. Excel 2007. I tried all of the above to no avail. It was the “filter” put on it. Once I took the filters off, BAM, my hidden rows reappeared.

    Like

  16. guri says:

    another reason is cell lock. U need to unlock them first. it will work. select all cells right click it is under format cells/protection. Good luck

    Like

  17. Jeremy Dascardus says:

    Fastest way to unhide those hidden panes? Select the entire page, click on print area and then click set print area and voila! All hidden columns and rows appear.

    Try it out…

    “There are many ways to the number 9. All of them are correct!”

    Like

  18. Mrrrr says:

    For me nothing worked. The hidden row wouldn’t unhide, I had tried with row height, filters, unhide, unfreeze. Nothing. But if nothing works, here is what you have to do:
    Tested for Excel 2007, 2010 or Excel 2013.
    1. If you use Excel 2007, please click Office Button on the top left corner, and then click Prepare > Inspect Document.
    If you use Excel 2010/2013, please click File > Info > Check for Issues > Inspect Document.
    2. Then a Document Inspector dialog box will appear, click Inspect button.
    3. The “hidden rows or columns” in the whole workbook have been inspected and if there are any, there is a “REMOVE ALL” button next to them.
    4. Click Remove All, then click Close button. And all of the hidden rows and columns in the workbook will be removed.
    Note:
    A. If there are formulas to manipulate the data in your workbook and the formulas call for data from hidden rows and columns, then deleting that information will cause incorrect result.
    B. With this function, you can delete all of the hidden rows and columns in the whole workbook.

    I don’t take any credits for this (just for finding maybe), credits go to the guys from extendoffice.com

    Like

    • MF says:

      Hi Mrrrr,
      To my surprise that nothing mentioned works for you.
      Nevertheless, thanks for sharing another approach to unhide all hidden rows and columns through Inspect Document.
      I did test that. It worked for hidden rows or rows with height less than 0.08. However for rows heights between 0.08 and 0.67, as I described in my findings, the Inspect Document method failed to “unhide” the rows.
      I am using Excel 2010 btw.
      Regards,

      Liked by 1 person

      • Mrrrr says:

        Was using 2010 also, but didn’t test for row heights. Just my hidden ones which were impossible to “unhide”, delete or increase height. Oh well, the more methods the better.

        Like

    • David Hill says:

      Unfreezing panes is what finally worked for me. Just wanted throw yet another possible resolution out there for those who encounter this issue.

      Like

  19. Corrie says:

    Thank you!! In my case I had filters that were preventing my hidden rows from showing up when I tried to unhide them. You saved me from a LOT of frustration 🙂

    Like

  20. Mira says:

    Thank you! I’ve been trying to “unhide” a column for months and thought it was a lost cause, but I tried autofitting column width just now and the missing column appeared!

    Like

  21. G says:

    Thank you. This helped me unhide columns by checking the column width.

    Like

  22. Amy says:

    OMG! Thank you! Such a simple fix. Ended up being exactly my problem. Way to think outside the box in your problem solving. It never occurred to me to check the row height.

    Like

  23. Kristan says:

    Good point! However this trick did not work for me, but I figured out my problem. I had a filter on one of the columns so the program automatically hid the other rows. I was in the program and turned on a filter, then saved and closed until the next day…I had forgotten the filter was still on.

    Like

  24. jas says:

    it worked by auto adjustments of rows and column thanks bhai

    Like

  25. Doug Holmes says:

    Thank you so much! I was scratching my head, too, and couldn’t figure out how to get the missing row back!! Your fix worked perfectly

    Like

  26. Pingback: Can a row be very hidden?

  27. QuasarH says:

    Precisely what I was looking for! In my case, the top 150 rows had vanished!

    Like

  28. Chris says:

    Thank you! I’ve had exactly this problem (well, columns in my case) and your trick worked. I don’t know why, but it did.

    Like

  29. Arend says:

    And ofcourse remove the filter of the column, i forgot that

    Like

  30. Orkney says:

    Mark the row over and under the hidden rows, right click “unhide”.

    Like

    • MF says:

      Hi Orkney,
      Thanks for your comment.
      You are right for a normal circumstance of hidden rows. However that does work in my example… And that’s why I wanna share that strange experience here to anyone with the same experience.

      Like

  31. Lindsey says:

    This did not work for me! Any other ideas? I am having the exact same problem- I created a spreadsheet with a row for each day of the year. I filtered out all dates except January dates to focus on those. When I go back to bring in February so I can work on that, it doesn’t show up in the filter, and I can’t unhide those rows in any of the above ways. I am trying to avoid individually pulling out each date (I have this problem on several other spreadsheets, as well.) No one around here has been able to figure it out, including my IT guys.

    Like

  32. Pingback: Unhiding Rows

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s