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

Get FREE Advanced Excel Exercises with Solutions!

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.

Fix- Unable to Unhide Rows in Excel


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).

Fix- Unable to Unhide Rows in Excel

  • In the Row Height window, set the height as a visible number (20 in this example) and hit OK.

Fix- Unable to Unhide Rows in Excel

  • Select rows 3-9 (rows 4-8 are hidden).
  • Right-click to open the context menu.
  • Choose the Unhide option.

Fix- Unable to Unhide Rows in Excel

Now, the hidden rows are visible.

Read More: VBA to Hide Rows Based on Cell Value in Excel


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.

Fix- Unable to Unhide Rows in Excel

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

Fix- Unable to Unhide Rows in Excel


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.

Fix- Unable to Unhide Rows in Excel

  • 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

Fix- Unable to Unhide Rows in Excel

Now,0 the hidden rows are visible.

Read More: 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.

Fix- Unable to Unhide Rows in Excel

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.

Fix- Unable to Unhide Rows in Excel

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.

Fix- Unable to Unhide Rows in Excel

  • Double-clicking once made row 8 unhidden.

Fix- Unable to Unhide Rows in Excel

  • Keep clicking the double line to unhide all the hidden rows.

Fix- Unable to Unhide Rows in Excel

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.


Related Articles


<< Go Back to Hide Rows | Rows in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

2 Comments
  1. Thank you for this article. I had been stumped on why Excel wouldn’t unhide my rows. Solution #3, removing the filter solved my problem.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo