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?


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.

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

54 Responses to When unhide row doesn’t work…

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


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


  2. Helen says:

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


  3. Kristina W says:

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


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


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


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


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

    • MF says:

      Hi Gerry,
      You are right. Filter could be a case for that. Thanks for sharing.


      • Carla Cooper says:

        HOLY CATS! Thank you both!!! Mine was a filter issue and I thought I was going to have to BREAK the computer! Going to have to keep this little nugget on file because it happens all the time with this particular person, sharing files. Don’t know why I haven’t retained the info yet though! We shall see. Thanks again!


    • jenlhandy says:

      OMG! THANK YOU!!!! I was seriously going crazy and thought I would have to manually unhide 200 rows. You are a lifesaver!!

      Liked by 1 person

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


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


  10. 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!”


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


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

      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.


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


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


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


  14. G says:

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


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


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


  17. jas says:

    it worked by auto adjustments of rows and column thanks bhai


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


  19. Pingback: Can a row be very hidden?

  20. QuasarH says:

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


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


  22. Arend says:

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


  23. Orkney says:

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


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


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


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