I recently came across an issue of Hidden Rows in Excel.  I simply could NOT unhide certain rows! Here is my journey to find the solution.

I checked a few things: (1) Confirmed the Sheet was NOT protected (2) Tried to pull up and down to expand the rows to see if the hidden rows would come into view (3) Inquired with another Workmate via Teams if he knew of some reason that would prevent me from unhiding the rows.  He confirmed NO, but noted that sometimes when that happens, he has to position the cursor on the far left at the line between row numbers then drag up or down to manually resize the row to get it to show up.

I ran across this issue because I was comparing totals in a couple of columns, which appeared to have identical derived values, however, the Total was different.  The assumption, as you can see in the screenshot above, is that there appears to be a 0.1 value in either cells B16/B17/B18.  I just needed to remove that hidden value to confirm it.  However, I could NOT get to it.

What Didn’t Work

I decided to try to hide another row and unhide it.  However, after I hid it, once again, Excel did NOT allow me to unhide it.  Hmmm.. what is the deal here.  Let’s figure this mystery out!

First of all, let me just see if this is possible via a new spreadsheet.  I know recently I received an automatic Excel update, which was after the spreadsheet above was initiated.

Just hid Row 7 as shown below:

I was able to Unhide Row 7 as shown below:

I just need to figure out why my current spreadsheet will NOT allow me to unhide the rows.  This is a KEY Estimation spreadsheet, with a lot of vested time already put into it.

Tried several things, including the following:

  • Select whole worksheet in upper left corner
  • Selected the Cells icon on the Home page tool bar

  • Selected Visibility/Hide & Unhide/Unhide Rows

It didn’t work.

  • Selected the row before and after the ones I wanted to unhide.
  • Selected the Cells icon again from the Home tab.
  • Selected Visibility/Hide & Unhide/Unhide Rows

It still did NOT work!

Looked at my version of Excel

Found something on Google that said you may also have to ensure you have NO frozen panes to Unhide your rows.

Of course, I do need to Unfreeze Panes as shown below.

Tried to Unhide the Rows after unfreezing the panes.  It still didn’t work as shown below.

I did one other thing.  I decided to also unhide the columns that were on the sheet. Then proceeded with trying to Unhide the Rows.  It still did NOT work.

The Mystery continues.  Have got to get some sleep.  It’s 2:00AM now…

What Didn’t Work – Round 2

Back to it.  Hopefully with a refreshed perspective.  Let’s get this resolved today. I think I am going to, first of all, add a new sheet to the existing spreadsheet, to confirm whether the issue exists there.

As shown above there is NO issue using Unhide Rows in the same spreadsheet I am currently experiencing this problem with.

The problem appears to be limited just to the existing Sheet I am currently experiencing the problem with.  Let me do one more thing.  I am simply going to Copy the problematic ‘Timeline’ sheet to a new sheet, within the same spreadsheet, to see if the problem exists.

OK, so it wasn’t as simple as that.  As shown below, I was NOT able to unhide the rows, even on this copied ‘Timeline (2)’ sheet.

The Solution to Hidden Rows in Excel

Hmm, why does it say Filter Mode?  I’m not filtering anything on this sheet.  Let me look at something else.  I see it does appear that something is being filtered.  Let me press the Clear Filter icon shown below.

OK, Filter Mode has been removed.  Now let me see if I am able to Unhide the Rows.

Wow, it worked!  I see the Rows (B16-B18) now!!!  The mystery of hidden rows in Excel is solved! 

Now to figuring out why those two Totals don’t match, that’s another story. Tune back in to see what the solution is for that.  Have a Good Day. And if you’ve noticed that excel just isn’t cutting it for your organization’s needs anymore, check out this post on Outgrowing Microsoft Excel.

Share and Enjoy !

Related Content: