Why wont excel let me unhide sheets?

Question

We have received a number of queries saying the unhide rows is not working in Excel. There are generally a few reasons why the rows won’t unhide.

Table of Contents Show

Frozen Panes and unhiding rows
Row height is very small
Subscribe to our YouTube channel- Today’s Excel Tip for the day
Why can't I unhide my sheets in Excel?
How do you unlock hidden sheets in Excel?

If rows are hidden from row 1, it is important to correctly highlight the rows and right click on the correct row.

In the example below, you need to highlight all the rows above 9. You do this by left clicking on the number 9 and dragging up, BUTthen you right click while hovering over the 9 again. If you right click in the top left corner it highlights everything again and you can’t find the UNHIDE option.

Frozen Panes and unhiding rows

If you try the above and it does not work, it is possible that you have frozen panes which make it look like the row is hidden but it is frozen. Go to the VIEW and FREEZE PANES buttons. If it says ‘Unfreeze Panes’ then this is the problem. Once you unfreeze them you will be able to scroll upwards and see the hidden rows.

Row height is very small

If neither of these work, it may just be that the row heights are so small it looks like it is hidden. So when you unhide or unfreeze rows it has no effect.

In this case, highlight the rows (in the case below highlight rows 14 to 22) and double click on one of the row lines, or change the row height (HOME TABE, FORMAT, Row Height) to be a bigger number.

Unhide multiple sheets in Excel- updated Excel feature

Viewing different parts of spreadsheets at the same time

One worksheet doesn't show the first 15 rows. I tried the followings, neither worked, even in full-screen mode

Step 1: F5 (Ctrl+G) to define & select 1:15, set row height to 20 and "unhide row", they are still hiding;
Step 2: Put cursor in A16, then up.. till A1 (invisible), select entire row (Ctrl+Shift+→), hold on Shift key, press ↓, till all 15 rows selected, set height and unhide row, still doesn't work!

How can I get them back?

asked Jun 25, 2010 at 13:13

5

Select the Sort & filter icon from the home menu bar, uncheck the Filter button.

To me, it looks like bug in Excel filters.

Jens Erat

16.9k14 gold badges58 silver badges73 bronze badges

answered Aug 5, 2014 at 7:58

I had the issue you were describing and found that the rows were unhidden, but the row height was zero. So, unhide the rows as you describe and then hover over the row symbols on the side (in between the two rows where your rows are hidden) and then click and drag to expand the row height manually.

There's probably a visual basic script to do this that you could code up as well.

answered Sep 21, 2012 at 20:40

Also, be careful that you haven't got a frozen pane. This can cause all sort of problems.

From View, look under Window and for Freeze Pane, there's an Unfreeze Pane option.

This one drove me nuts for half an hour until I realized what was going on. Unhide on its own didn't work. Neither did changing row height. Frozen panes are very subtle.

answered Jun 24, 2011 at 14:17

If you select all the rows and click 'unhide' and they do not show up, then they are filtered and not hidden. Click the Sort & Filter button on the Home tab of the ribbon and then click 'clear'.

answered Sep 20, 2016 at 12:27

Try the following:

Type the first cell reference A1 in the Name Box and press enter. On the Home tab, click on the Format icon Choose Hide & Unhide from the dropdown menu then select Unhide Rows.

You should be able to unhide all of the rows inbetween after that.

answered Jun 25, 2010 at 13:34

2

I removed my filter from the whole worksheet and everything appeared. Then I highlighted everything I wanted filtered and re-applied the filter.

answered Apr 7, 2011 at 22:06

I know it's an old question, but there is a way to get the rows back.

use ALT+F11

If you can't see a window labelled Immediate Window then press CTRL+G to get there.

In that window, type (or copy) the following code, adjusting for the rows you want unhidden, and the sheetname

for x=1 to 15:sheets("WorkSheetName").range("A"&x).entirerow.rowheight=17:next

This sets the height of all 15 rows to a height of 17, and you can then manipulate them how you wish.

answered Oct 27, 2014 at 17:05

SeanCSeanC

3,6192 gold badges19 silver badges26 bronze badges

Highlight the rows you want unhidden. Double click on the space between the rows in the numbers column, (on the left side) your mouse pointer icon will change from a pointing right symbol to an expansion symbol. The hidden rows will show themselves.

answered Feb 23, 2012 at 20:58

Select GOTO (CTRL+G)
Select the hidden rows only... if hidden rows are 3 to 100, then 3:100
In the menu bar select Format > Row Height The hidden rows should all have a value of "0".
Change and set the height of the rows to what you want... ie 15
All of the hidden rows should now be visible

answered Jun 21, 2017 at 21:53

If the unhide doesn't work or the unhide option doens't show, you can put the mouse between the column where the diveder is and drag right and you can unhide each column one by one unwarapping it :)

Not ideal if you have many columns hidden there but useful if you have a few.

answered Sep 22 at 21:14

zarzar

1,6215 gold badges21 silver badges40 bronze badges

Why can't I unhide my sheets in Excel?

First ensure that the Show sheet tabs is enabled. To do this, For all other Excel versions, click File > Options > Advanced—in under Display options for this workbook—and then ensure that there is a check in the Show sheet tabs box.