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.
How to Fix When It Is Unable to Unhide Rows in Excel: 4 Effective Solutions
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.
1. Check Row Height to Unhide Rows in Excel
There may be cases where some rows have their heights too 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 OK.
- Select rows 3-9 (rows 4-8 are hidden).
- Right-click to open the context menu.
- Choose the Unhide
Now, the hidden rows are visible.
Read More: Excel Hide Rows Based on Cell Value with Conditional Formatting
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 Freeze Panes.
- Choose either the Freeze Panes or Freeze Top Row
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 feature.
- Choose Unfreeze Panes.
- 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
Now,0 the hidden rows are visible.
Similar Readings
- How to Hide the Same Rows Across Multiple Excel Worksheets
- How to Automatically Hide Rows with Zero Values in Excel
- How to Unhide Rows in Excel
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: How to Unhide Top Rows in Excel
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: Unhide All Rows Not Working in Excel
Things to Remember
We should make sure the worksheet is not protected and then apply all these described methods.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
