Unhide All Rows Not Working in Excel (5 Issues & Solutions)

In many cases, you have to unhide unnecessary rows to make particular rows more presentable. Unfortunately, you may get troubled if unhiding all rows are not working. In this article, I’ll show you 5 issues with their solution for why unhide all rows are not working in Excel.


Download Practice Workbook


5 Issues and Solutions to Unhide All Rows Not Working in Excel


1. Unhide All Rows Not Working If Freezing Pans Option is Available

In the beginning method, I’ll show the reason behind unhiding all rows is not working if you lock some specific rows.

If you look closely at the following picture, you’ll find that rows 1-5 are not visible.

Freeze Panes

Now, when you try the Unhide option from the context menu for unhiding rows, you may get surprised that the option is not working.

Freeze Panes

The reason behind this issue is the existence of Freeze Panes. Ultimately, you need to remove the Freeze Panes as all possible ways of unhiding all rows will not work.

In such a situation, follow the steps below to unhide all rows.

➯ Initially, go to the View tab.

➯ Then click on the drop-down list of Freeze Panes and choose the Unfreeze Panes option.

Freeze Panes

Note. The keyboard shortcut to unfreeze the panes: ALT + W + F  + F.

After doing that you’ll get the unhidden rows. Moreover, the methods of hiding and unhiding rows will work from now.

Freeze Panes


2. When the Row Height is Very Small or Zero

Here, if you try, still you cannot unhide row 8 in the below using the conventional methods of unhiding rows.

Could you imagine the cause?

When the Row Height is Very Small or Zero

Actually, here the row is not hidden at all, rather the height is 0. That’s why the method of unhiding rows is inoperative.

When the Row Height is Very Small or Zero

Moreover, the same situation happens again if the row height is minuscule (between 0.08 and 0.67).

Let’s solve the issue.

In fact, you need to increase the row height using the Row Height option from the Format menu (e.g. 20) as shown in the below screenshot.

When the Row Height is Very Small or Zero

After increasing the row height, you’ll get the output where row 8 is visible.

When the Row Height is Very Small or Zero


Similar Readings


3. If the Filter Mode is Active

In the following screenshot, you see the Filter mode is active and product id of 1004 & 1005 is filtered. As a result, rows 8-9 are not visible.

If the Filter Mode is Active

The only solution to unhide the hidden rows is to deactivate the Filter mode.

➯ Firstly, go to the Data tab.

➯ Again, choose the Clear option from the Filter option in the Sort & Filter ribbon.

If the Filter Mode is Active

Immediately, you’ll get the following output where no hidden rows exist.

If the Filter Mode is Active

Read More: [Fix]: Unable to Unhide Rows in Excel (4 Solutions)


4. Unhide All Rows Not Working When the Sheet is Protected

Sometimes, you may find that the Unhide option is inoperative. For example, rows 7-10 in the following figure are not visible and you cannot unhide the rows utilizing the popular methods of unhiding the rows.

Excel Unhide All Rows Not Working When the Sheet is Protected

One possible reason may be that the sheet protection is active.

Let’s check whether it is protected or not using the VBA.

To use VBA, you need to create a module in the following ways.

➯ Firstly, open a module by clicking Developer > Visual Basic.

How to Insert VBA Code

➯ Secondly, go to Insert > Module.

How to Insert VBA Code

➯ Now, copy the following code into the newly created module.

Sub SheetProtection()
If ActiveSheet.ProtectContents = True Then
MsgBox "The Sheet is Protected"
Else
MsgBox "The Sheet is Not protected"
End If
End Sub

Excel Unhide All Rows Not Working When the Sheet is Protected

➯ Next, run the code (the keyboard shortcut is F5 or Fn + F5), you’ll get that “The Sheet is Protected”.

Excel Unhide All Rows Not Working When the Sheet is Protected

So, we need to unprotect the sheet.

➯ At the outset, go to the Review tab.

➯ And, click on the Unprotect Sheet from the Protect ribbon.

Excel Unhide All Rows Not Working When the Sheet is Protected

➯ Subsequently, you’ll get that the Unhide option is in the operative mode.

➯ Just click on the option, before that you need to select the dataset.

Excel Unhide All Rows Not Working When the Sheet is Protected

Finally, you’ll get the following output.

Excel Unhide All Rows Not Working When the Sheet is Protected

Related Content: [Fixed!] Excel Rows Not Showing but Not Hidden (3 Reasons & Solutions)


5. Can’t Unhide Top Rows in Excel

Often you may see that the first or top rows are not visible. Though unhiding the top rows is the same as the method of unhiding other rows in Excel. Unluckily, the process of unhiding the top or first row is not the same.

Can’t Unhide Top Rows in Excel

Such as if you try to unhide the first row using the context menu, you’ll find that unhiding the row is not working.

Can’t Unhide Top Rows in Excel

However, if you want to unhide the first row, follow the steps below.

➯ In the beginning, move the cursor on the Home tab

➯ Therefore, click on the Go To option from the Find & Select option in the Editing ribbon.

Can’t Unhide Top Rows in Excel

➯ Now, input A1 as the Reference and press OK.

Go To Reference

➯ After that click on the Unhide option from the context menu.

Can’t Unhide Top Rows in Excel

Lastly, you’ll get the hidden first row as illustrated in the following screenshot.

Can’t Unhide Top Rows in Excel

Read More: How to Unhide Top Rows in Excel (7 Methods)


Conclusion

In short, you may unhide all rows in excel utilizing the above methods. Hence, I hope that the article might be highly beneficial for you. However, if you have any queries and suggestions, share them below in the comments section.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo