How do you unlock hidden rows in Google Sheets?

The great thing with most spreadsheet software, including Google Sheets is that you get to examine your data in different ways and from different perspectives.

One such convenient feature of Google Sheets is that it lets you hide certain rows so you can concentrate on only those that matter.

There’s one small problem with this though. There’s a possibility of forgetting to unhide the hidden rows.

There’s also a possibility of forgetting which rows you had hidden in the first place! This often happens when dealing with large data sets.

If you’re sharing a Google sheet with someone who has hidden some rows [maybe for their convenience], this might be a problem.

The problem can be even more frustrating since it is sometimes difficult to tell how the rows were hidden. But every problem has a solution, and that’s what we’re here for!

In this tutorial, we will take a look at how you can unhide hidden rows in Google Sheets.

• How to Unhide Rows in Google Sheets [for smaller datasets]
• How to Unhide Rows in Google Sheets [for larger datasets]
• How to Find Rows Hidden by Filters
• Hidden rows Because of Filter View

How to Unhide Rows in Google Sheets [for smaller datasets]

If you’re looking at a worksheet and you feel like some data is amiss, there’s a possibility that some rows are hidden. So, it’s a good idea to check for hidden rows in the sheet.

Here are some tell-tale signs that there are hidden rows in your Google Sheet:

1. The obvious giveaway is the absence of certain consecutive row numbers. As you can see in the image below, you get rows 1, 2, and then it skips to row 5. That means rows 3 and 4 are not visible.
2. Secondly, if you look at the rows in your sheet carefully, you should notice two arrows on consecutive rows [as the ones are shown below] to signify that there are hidden rows in between.
3. To unhide the rows, all you need to do is click on one of the two arrows. Your hidden rows should now become visible again.

How to Unhide Rows in Google Sheets [for larger datasets]

The above method is fine if you can clearly see which rows are hidden.

If you have a larger database, however, it may be harder to spot the arrows.

In such cases, here’s how you can find out if there are hidden rows in the dataset [to help you understand the concepts easily, we have kept the dataset in our images small. But this method works with larger sets too]:

1. Select all the row numbers and columns letters by clicking on the grey block on the top left of your spreadsheet.
2. Next, right-click on your selection.
3. If you have any rows hidden in the sheet, you should see a menu option that says “Unhide Rows”.  If you cannot find this option, it means your worksheet does not have any hidden rows.

While you may expect to be able to click on the Unhide option [in step 3 above] and that should unhide all the rows in the worksheet.

Well, it doesn’t work that way. You can see the Unhide Rows option, but you can’t click on it.

All it does it let you know that there are some hidden rows in the sheet.

Once you know that there are hidden rows in your sheet, here’s a quick way to unhide all hidden rows in one go:

1. Select the row headers for all the rows in the range.
2. Right-click and select the “Unhide Rows” option.
3. All the rows which were hidden should now become visible at the same time.

How to Find Rows Hidden by Filters

Oftentimes what you perceive as ‘hidden rows’ may actually be the result of filters being applied.

This is most commonly the case when users complain that they know a few rows are missing, but cannot seem to find any ‘hidden’ rows that they can unhide.

So here’s how to tell if there’s a filter hiding your rows:

1. You will see a filter icon next to some of the column headers
2. You will see the row numbers and column headers colored in a different color [somewhat light-bluish].
3. You will notice a few row numbers missing.
4. If you click on the Data menu from the menu bar, you should see an option that says ‘Turn off filter’.

If you see any or all of the above tell-tale signs, then it’s obvious that you’re dealing with a filter here.

How to Turn Off Filters

If you’ve diagnosed the cause of the missing rows to be the presence of filters, you need to remove the filters to get back your missing rows. Here’s how:

1. Click the Data menu from the menu bar.
2. Select the option that says ‘Turn off filter’.
3. This should remove filters from your sheet and you should be able to see all your previously hidden rows again.

Note: Alternatively, you can also remove the filter by clicking on the ‘Turn off filter’ icon directly from the toolbar.

Hidden rows Because of Filter View

Another common scenario is when you are in the ‘Filter view’.

It is possible that someone might have created a ‘filter view’, causing some of the rows to be hidden from view. Here’s how to tell if the sheet is in filter view:

1. The row numbers and column letters will be a different color [somewhat dark greyish].
2. You will see filter information above the column headers. This will also be colored somewhat dark greyish.
3. You will notice a few row numbers missing.

If you disable the filter view, you should be able to get back all your hidden rows. Here are two ways to do this:

1. When in ‘filter view’, there’s usually a cross on the right-hand side of the bar that contains information about the filter. Simply click on it to remove the filter.
2. Alternatively, you can click on the ‘Filter views’ button on the toolbar and select ‘None’ from the dropdown menu that appears.

Either way, you should be back to the original spreadsheet view and you should now be able to see all the previously hidden rows.

In this tutorial, we showed you how to find out if there are hidden rows in your sheet.

We also showed you how to find the reason for the missing rows. Rows in your sheet could be missing due to one of three reasons:

• The rows were previously hidden
• There might be a filter in place
• The worksheet might be in the filter view.

For each of the above cases, we showed the steps you should follow in order to unhide the rows and see the full worksheet’s data.

We hope you found the tutorial helpful and that you can now find your ‘missing’ rows easily with no problems.

Other Google Sheets tutorials you may like:

• How to Hide Columns In Google Sheets
• How to Hide Gridlines in Google Sheets
• How to Group Rows & Columns In Google Sheets
• How to Delete Empty Rows in Google Sheets