Al Arafat Siddique
This article illustrates 4 different solutions to being unable to unhide rows in Excel. We need to hide rows while working on a large dataset for easy maneuvering. Some frequently used features like filtering and freeze panes also hide rows based on specified criteria. Sometimes all the techniques to unhide these rows don’t work. Let’s find out the reasons and solutions for this issue.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Solutions to Unable to Unhide Rows in Excel
To illustrate different solutions to the problem of not being able to unhide rows in Excel, we’ll use the following dataset. The dataset shows a list of sales data for a super shop for different city branches.
Try to Unhide the Hidden Rows
The dataset has some rows (rows 4-8) hidden. Let’s try to unhide them by using the following methods.
Method 1
Steps:
- Select rows 3-9 (rows 4-8 are hidden).
- Right-click to open the context menu.
- Choose the Unhide
Read More: How to Unhide Multiple Rows in Excel (9 Methods)
Method 2
Steps:
- Click the button at the left top corner of the spreadsheet to select all cells.
- Go to the Format tab from the Home tab.
- Choose the Unhide Rows from the Hide & Unhide
Nothing happened, right! There are some more methods described in the article linked below. If you are still unable to unhide the hidden rows then try out the solutions below.
Read More: Excel VBA: Unhide All Rows in Excel (5 Practical Examples)
1. Check Row Height to Unhide Rows in Excel
There may be cases where some rows have their heights so small to be noticeable. We cannot unhide them by using the usual methods. There can be several cases based on the row height.
Case 1: Row Height <= .07
We can unhide them using any simple method.
Case 2: .08 < Row Height < .67
In this case, follow the steps below.
- Select the hidden rows (here rows 3-9).
- In the Row Height window, set the height as a visible number (20 in this example) and hit
- The above steps would unhide the hidden rows successfully.
Read More: How to Hide and Unhide Rows in Excel (6 Easiest Ways)
2. Unfreeze Panes First If Unable to Unhide Rows in Excel
Why this Happens
We frequently use the freeze panes feature of Excel to easily maneuver on a large dataset. Let’s say, the worksheet is scrolled up which makes some of the rows off the screen. Here is a screenshot where the first 6 rows are off the screen.
If we use the Freeze Panes feature of Excel, it will hide those 6 rows. To freeze panes,
- Go to the View tab of the Excel Ribbon.
- Click the Freeze Panes
- Choose either the Freeze Panes or Freeze Top Row
These hidden rows cannot be made unhidden using the usual methods.
Solution
The only solution to this is to unlock all the rows using the Unfreeze Panes option. To do that-
- Go to the View tab of the Excel Ribbon.
- Click the Freeze Panes
- Choose the Unfreeze Panes
The above steps would unhide the hidden rows again.
Read More: [Fixed!] Excel Rows Not Showing but Not Hidden (3 Reasons & Solutions)
Similar Readings
3. Active Filter Option- Reason to Unable to Unhide Rows in Excel
Why this Happens
When we add a filter to a dataset, it hides the rows that don’t belong to the filter criteria. See the following screenshot where rows 4-6, 12-13, and 18-19 are hidden. The products that are shown in the dataset, are filtered for the Bars, Crackers, and Snacks categories.
The following dataset shows the filtering criteria for the above dataset. The products that belong to the Cookies category are hidden.
We cannot unhide them using conventional methods.
Solution
We need to deactivate the filter feature to unhide the hidden rows. Let’s see how we can do this.
Steps:
- Go to the Data tab of the Excel Ribbon.
- Click the Filter tab to deactivate
Another way
We can also make the hidden rows visible by clicking Select All.
Read More: Hidden Rows in Excel: How to Unhide or Delete Them?
4. Double Click the Double Line to Unhide Hidden Rows in Excel
If the cause of the hiding of rows in a dataset is either row height or filtering, we can unhide them by using another trick. Let’s talk about this in the following steps.
- We can see a double line where rows are hidden.
- Hover on the double line.
- Double–clicking once made row 8 unhidden.
- Keep clicking the double line to unhide all the hidden rows.
Read More: Shortcut to Unhide Rows in Excel (3 Different Methods)
Things to Remember
- We should make sure the worksheet is not protected and then apply all these described methods.
Conclusion
Now, we know how different solutions to being unable to unhide rows in Excel. Hopefully, it would encourage you to use these techniques to solve your issues more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
Related Articles
- Hide Rows Based on Cell Value with Conditional Formatting in Excel
- Formula to Hide Rows in Excel (7 Methods)
- Excel Macro: Hide Rows Based on Cell Text in Excel (3 Simple Methods)
- How to Hide Blank Rows in Excel VBA (4 Useful Methods)
- Hide Duplicate Rows Based on One Column in Excel (4 Methods)
- VBA to Hide Rows Based on Cell Value in Excel (14 Examples)
