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.
Now, when you try the Unhide option from the context menu for unhiding rows, you may get surprised that the option is not working.
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.
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.
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?
Actually, here the row is not hidden at all, rather the height is 0. That’s why the method of unhiding rows is inoperative.
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.
After increasing the row height, you’ll get the output where row 8 is visible.
- Hidden Rows in Excel: How to Unhide or Delete Them?
- How to Unhide Multiple Rows in Excel (9 Methods)
- Shortcut to Unhide Rows in Excel (3 Different Methods)
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.
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.
Immediately, you’ll get the following output where no hidden rows exist.
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.
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.
➯ Secondly, go to Insert > Module.
➯ 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
➯ Next, run the code (the keyboard shortcut is F5 or Fn + F5), you’ll get that “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.
➯ 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.
Finally, you’ll get the following output.
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.
Such as if you try to unhide the first row using the context menu, you’ll find that unhiding the row is not working.
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.
➯ Now, input A1 as the Reference and press OK.
➯ After that click on the Unhide option from the context menu.
Lastly, you’ll get the hidden first row as illustrated in the following screenshot.
Read More: How to Unhide Top Rows in Excel (7 Methods)
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.
- VBA to Hide Rows Based on Cell Value in Excel (14 Examples)
- Hide Duplicate Rows Based on One Column in Excel (4 Methods)
- VBA to Hide Rows Based on Criteria in Excel (15 Useful Examples)
- How to Hide Blank Rows in Excel VBA (4 Useful Methods)
- Formula to Hide Rows in Excel (7 Methods)
- How to Hide Rows Based on Cell Value in Excel (5 Methods)