Annyca Tabassum
In Google Sheets, the hidden row feature is very useful when you have a huge dataset and you have to deal with a few of them. But after hiding data sometimes we can’t find the hidden rows which also bothers our analysis. So, in this article, we will explore how to find hidden rows in Google Sheets.
A Sample of Practice Spreadsheet
You can download Google Sheets from here and practice very quickly.
2 Simple Ways to Find Hidden Rows in Google Sheets
In google sheets, when we want to find any values we simply use the keyboard shortcut CTRL+F. But for hidden rows, we can not use this shortcut because this shortcut is only applicable to visible data.
So we have the following solutions:
1. Clicking Up/Down Caret Icon (Useful for Smaller Datasets)
This is the easiest way to find hidden rows from the dataset. If any hidden row exists in the dataset, you will find an up/down caret icon in the row number bar. By clicking on it you can find those rows again. To go for the method,
Steps:
- In the beginning, go to the dataset and find the up/down caret icon on the leftmost side where the row numbers are located.
- Here, we find the up/down caret icon between row number 6 and 10.
- That means rows 7-9 are hidden.
- Now click on the caret icon to find the hidden rows.
- After clicking on the icon you will find the hidden rows in the dataset.
Read More: How to Find P-Value in Google Sheets (With Quick Steps)
2. Applying Find and Replace Feature to Find Hidden Information in Rows
In Google Sheets when you deal with a range of data, it is difficult to use the up/down caret icon to find your desired one. In that case the find and replace feature can be a lifesaver for you. You can use this feature to find values that are even hidden in the dataset.
Steps:
- In the beginning, create a dataset containing data with hidden rows.
- Now go to the Edit bar and select the Find and replace feature. You can also add this feature by keyboard shortcut key: CTRL+H.
- Then insert a value in the Find bar. Here, we input “Sofia” as a find value.
- Now click on the Find option.
- After clicking on the Find option you will find a message above the Find bar stating that “A match was found in hidden cell B9 with value ”Sofia”.”.
- Unfortunately, it does not find the exact cell “Sofia” as the feature is not able to unhide cells. But it mentions the hidden cell number as well as a close location of the value.
- But on the plus side, it does bring the active cell adjacent to the hidden result.
- You can see the whole process at a glance.
Read More: How to Use Find and Replace in Column in Google Sheets
Similar Readings
- How to Use FIND Function in Google Sheets (5 Useful Examples)
- Find and Replace with Wildcard in Google Sheets
- How to Remove Numbers from a String in Google Sheets
- Find and Replace in Google Sheets (3 Ways)
- How to Find and Delete in Google Sheets (An Easy Guide)
2 Useful Ways to Unhide Hidden Rows in Google Sheets
So far, we’ve looked at ways to find hidden rows in Google Sheets. But what if we wanted to unhide them?
If you want to unhide the hidden rows, you can also apply several techniques. Here, we discuss the two most popular and quickest methods to unhide hidden rows.
I. Find and Click the Caret Icon
When you find the up/down caret icon in the row number, it is certain to find hidden rows there. You can also know the hidden row number by seeing the number differences between the up and down caret icon.
Steps:
- First, check the up/down caret icon in the row numbers.
- Here, we find the up/down caret icon between row number 6 and 10.
- That means rows 7-9 are hidden.
- Then click the caret icon to unhide the rows.
- Finally, you will find the hidden 7 to 9 rows in the dataset.
Read More: How to Find Median in Google Sheets (2 Easy Ways)
II. Unhide All Rows at Once
The above method of unhiding rows is most applicable but if you have a large range of data and you are not sure about whether there are some hidden rows or not. Then the method we will discuss below will be helpful for you. Here, we use a small dataset for better understanding but this method is preferable for a larger dataset.
Steps:
- First, click on the box located in the top leftmost side of the spreadsheet and select all the rows and columns of the spreadsheet.
- Now click on the right and you will find several options there. If you find the Unhide rows option, then your dataset has hidden rows and if you do not find this option then there are no hidden rows in the dataset.
- Here, in our dataset, as we find this option, that means we have hidden rows in our dataset.
- After knowing that there are some hidden rows in the dataset you can apply the same process that we discussed immediately before this method.
- For the hidden row, there must be the up/down caret icon. Find the icon and click on it.
- Finally, you unhide the hidden rows.
Things to Remember
- To make sure about hidden rows for large datasets, always go to the top leftmost corner to select all columns and rows. Then clicking right to see the option Unhide Rows. The visibility of this option will indicate the presence of hidden rows.
- Be careful about applying conditional formatting.
Conclusion
After going through this article, we hope you can build a clear concept about how to find hidden rows in Google Sheets. If you have further curiosity about Google Sheets’ other functions you can visit the OfficeWheel website.
Related Articles
- How to Find Slope of Trendline in Google Sheets (4 Simple Ways)
- Find Uncertainty of Slope in Google Sheets (3 Quick Steps)
- How to Find Frequency in Google Sheets (2 Easy Methods)
- Find Slope of Graph in Google Sheets (With Easy Steps)
- How to Find Linear Regression in Google Sheets (3 Methods)
- Find Largest Value in Column in Google Sheets (7 Ways)
- How to Find Trash in Google Sheets (with Quick Steps)
