Do you know how to unhide rows in Google Sheets? It’s a great question, and you’ll find that there are several ways to solve this puzzle. The great thing about most spreadsheet software, including Google Sheets, is that you can examine your data differently and from different angles.
A handy feature of Google Sheets is that it allows you to hide certain rows so that you can focus only on the ones that matter or in case of empty rows. However, there’s a little problem with that – forgetting to unhide the hidden rows.
It’s also possible to forget which rows you had hidden in the first place! This often happens when you’re working with large data sets.
If you share a Google spreadsheet with someone who has hidden some rows (perhaps for their convenience), it can cause problems. Furthermore, it can be even more frustrating since it’s sometimes difficult to know how the rows are hidden. But every problem has a solution, and that’s why we’re here!
In this tutorial, we will look at how to unhide rows in Google Sheets.
How to Unhide Rows in Google Sheets
- Select all the row numbers and column letters by clicking on the gray block at the top left of your spreadsheet.
- Right-click on your selection.
- Choose Unhide rows.
How to Unhide Rows in Google Sheets (for Smaller Data Sets)
If you’re looking at a spreadsheet and feel like some data is missing, it’s possible that some rows are hidden. So it’s good to unhide the hidden rows in Google Sheets.
Here are some telltale signs that there are hidden rows in your Google spreadsheet:
-
The most obvious indicator is the absence of certain consecutive row numbers. As you can see in the image below, you have rows 1 and 2, then it jumps to row 5. This means that rows 3 and 4 are not visible.
-
Secondly, if you look closely at the rows in your spreadsheet, you should notice two arrows on consecutive rows (like the ones shown below) to signify the presence of hidden rows between them.
-
To unhide the rows, simply click on one of the two arrows. Your hidden rows should now be visible again.
How to Unhide Rows in Google Sheets (for Larger Data Sets)
The previous method works well if you can clearly see which rows are hidden.
If you have a larger database, however, it may be more difficult to spot the arrows. In such cases, here’s how you can tell if there are hidden rows in the data set. To help you understand the concepts more easily, we have used the same small data set for our images. But this method also works with larger sets.
Here’s how to unhide all the rows in Google Sheets:
- Select all the row numbers and column letters by clicking on the gray block at the top left of your spreadsheet.
- Right-click on your selection.
- If there are hidden rows in the spreadsheet, you should see a menu option that says “Unhide rows“. If you can’t find this option, it means that your spreadsheet doesn’t have any hidden rows.
While you would expect to be able to click on the “Unhide” option (in step 3 above), which should reveal all the rows in the spreadsheet, in practice, it doesn’t work that way.
Instead, you will see the “Unhide rows” option, but you can’t click on it. All it does is inform you that there are hidden rows in the spreadsheet.
Once you know that there are hidden rows in your spreadsheet, here’s a quick way to unhide all the hidden rows at once:
- Select the row headers of all the rows in the range.
- Right-click and select the option “Unhide rows“.
- All the previously hidden rows should now become visible at once.
Shortcut to Unhide Rows in Google Sheets
Fortunately, like most functions in Google Sheets, you can use a keyboard shortcut to unhide rows in Google Sheets.
To unhide rows in Google Sheets using the Windows keyboard shortcut, you can press Ctrl + Shift + 9. To unhide rows in Google Sheets on macOS, you can use the keyboard shortcut Cmd + Shift + 9.
How to Group Rows to Hide Them
When you group rows in Google Sheets, it becomes easier to hide and unhide them. Your rows usually hide automatically when they are grouped, and a plus (+) control icon appears next to the row numbers.
You can click on the minus (-) icon to hide them and the plus (+) icon to unhide them.
How to Check for Hidden Rows
Usually, you should be able to tell if you have hidden rows by checking the row numbers to see if any numbers have been skipped. However, this can be tedious if you have a large volume of data. Another way to show hidden rows in Google Sheets is to:
- Select the entire spreadsheet by clicking on the square in the bottom-left corner of the formula bar.
- Right-click anywhere on the spreadsheet.
- Check the “Unhide rows” option.
If the option is available, it means you have hidden rows. Otherwise, it means you don’t have any hidden rows.
If you want to find hidden information in Google Sheets, you can also use the “Find and Replace” function in the “Edit” menu.
How to Find Rows Hidden by Filters
Often, what you perceive as “hidden rows” can actually be the result of applied filters.
This is the most common scenario when users complain about missing a few rows but can’t find “hidden” rows they can unhide.
So here’s how to display hidden rows in Google Sheets if there’s a filter hiding your rows:
- You will see a filter icon next to some of the column headers.
- You will see the row numbers and column headers colored differently (in a light blue shade).
- You will notice that some row numbers are missing.
- If you click on the “Data” menu in the menu bar, you should see an option that says “Turn off filter“.
If you see one or all of these telltale signs, it’s evident that you’re dealing with a filter here.
How to Disable Filters
If you have 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 to do it:
- Click on the “Data” menu in the menu bar.
- Select the option that says “Turn off filter“.
This should remove the 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 directly clicking on the “Turn off filter” icon from the toolbar.
Hidden Rows Due to Filter View
Another common scenario is when you’re using the “Filter View” option.
It’s possible that someone has created a “Filter View,” resulting in the hiding of some of the rows from the view. Here’s how to know if the sheet is in filter view:
- The row numbers and column letters will be in a different color (dark gray).
- You will see filter information above the column headers. These will also be colored in dark gray.
- You will notice that some row numbers are missing.
If you disable the filter view, you should be able to regain all your hidden rows. Here are two ways to do it:
-
When you’re in “Filter View,” there is usually an ‘x’ on the right side of the bar that contains filter information. Simply click on it to remove the filter.
-
Alternatively, you can click on the “Filter Views” button in the toolbar and select “None” from the drop-down menu that appears.
Either way, you should return to the original spreadsheet view, and you should now be able to see all the previously hidden rows.
Frequently Asked Questions
Why aren’t my hidden rows unhide?
If our methods for unhiding a row in Google Sheets don’t work for you, then there might be a few reasons for that.
One of them is that you have rows hidden by a filter. Try removing the filters and unhiding the rows. Go to “Data” > “Turn off filter“.
It’s also possible that you don’t have access to the file or have been granted access only as a viewer, which means you can’t edit the spreadsheet. If that’s the case, contact the owner of the Google Sheets and ask them to update your access.
Conclusion
In this tutorial, we showed you how to determine if there are hidden rows in your sheet and how to unhide rows in Google Sheets. We also showed you how to find the reason for missing rows and gave you examples of why some rows in your sheet may be missing due to one of the following three reasons:
- Rows were previously hidden.
- There may be a filter in place.
- The sheet may be in filter view.
We hope you found this tutorial helpful and that you can now easily find your “missing” rows without any issues. If you found this article useful, you may also want to check out our guide on how to hide gridlines in Google Sheets or download some of our premium templates to save time. Don’t forget to use the code SSP to save 50% on your purchases.
