When rows in a spreadsheet with similar information occupy contiguous positions, it is easy to compare the information they contain.
Even if they are a few rows apart, it is still possible to do a quick comparison.
But as a spreadsheet grows and more rows are added, the distance between rows with information that is of interest, can increase to the point where you need to scroll a fair way up or down in an attempt to compare two or more rows.
Obviously, this is highly inefficient, not to mention annoying and a nuisance.
You could sort the rows, but that may not always be desirable and there’s no guarantee that the rows would end up in adjacent positions anyway.
As it turns out, Google sheets allows you to selectively hide rows so that only those of interest are visible.
These hidden rows aren’t deleted, they just dot’s show up when viewing the spreadsheet.
There are a few ways of doing this, and we’ll be looking at how to identify which of these methods has been used, and how to undo them so that all rows are visible again.
How to Unhide Rows
So you’ve been asked to do some work on a spreadsheet and suddenly notice that perhaps not all the information is there, it looks like some rows are missing.
If rows have been hidden, there are two telltale signs to look for.
The first is the numbering in the row count grid on the very left of the sheet.
Looking at the row numbering in the snapshot above, we see that it skips from row 5 to row 11, then further down from row 16 row 22, and finally from row 28 to row 34.
The second telltale sign is the up and down carets to the left of row numbers in the row grid.
To the left of row numbers 5 and 11, we see the up and down carets, just as they are to the left of row numbers 16 and 22, and 28 and 34.
Having confirmed that there are indeed hidden rows, how do we go about unhiding them?
If you wanted to unhide only the hidden rows between rows 5 and 11, left mouse clicking on either the up caret to the left of row 5, or the down caret to the left of row 11, will unhide rows 6 to 10 inclusive.
To unhide further rows, we continue on, doing the same with rows 16 to 22 and 28 to 34.
We can also traverse down the entire sheet doing this for any other hidden rows.
However, it can take some time to cover an entire sheet, especially if it is a large one.
You can unhide more than one set of hidden rows by first selecting them.
In the snapshot above, our selection covers the hidden rows between 5 and 11, and 16 and 22.
It doesn’t matter that our selection also encompasses already unhidden rows, as long as we include the hidden rows we want to unhide.
Next, right mouse click inside the selected region to bring up the context menu, and select the option Unhide rows.
Now, the previously hidden rows 6 to 10 and 17 to 21 will be unhidden.
We can apply this principle further, to unhide all hidden rows in the sheet, by selecting the entire row grid, as shown above.
To do this quickly, select the first row, then use the keyboard shortcut Shift-Ctrl-down arrow.
All rows in the sheet should now be selected.
Then, as before, right mouse click inside the selected region to bring up the context menu, and select the option Unhide rows.
This will unhide every hidden row in the sheet.
Grouped Rows
Another way to hide rows is to group them.
You can discern grouped rows from the extra grid to the left of the row number grid.
This extra grid will have plus and/or minus icons to indicate where the grouped rows are.
The plus icon indicates a collapsed group with hidden rows.
Clicking on the plus icon will expand the group revealing any hidden rows, while the minus icon indicates an expanded group and clicking on the icon will collapse the group, thus hiding the rows.
The above snapshot shows three sets of grouped rows – 6 to 10, 17 to 21, and 29 to 33.
While rows 6 to 10 and 29 to 33 are hidden, the group of rows 17 to 21 have been expanded and so are visible.
Clicking on the plus icon will expand the group, thus unhiding any hidden rows, while clicking on the minus icon will collapse the group and hide the rows.
You can go through a sheet, clicking on the plus icons to expand each group in turn, or, to expand all grouped rows in the sheet, right mouse click anywhere in the group grid to bring up the context menu and select the option Expand all row groups.
Finally, to remove all grouping from the sheet, select all the rows in the sheet (click on the first row in the row grid to select it, then use the keyboard shortcut Shift-Ctrl-down arrow), then right mouse click in the row grid to bring up the context menu, and select the option Ungroup rows 1 – 501, where 501 will indicate the total number of rows in the sheet.
Unhide Filtered Rows
The final way that rows may be hidden is through the use of a Google sheets filter.
Filters allow rows to be hidden from view based on a condition (such as whether a cell is empty or not), on whether a cell contains a specific value, or even the background or text color of the cell.
As with hidden and grouped rows discussed earlier, filtered hidden rows will also show up in the row grid count as skipped rows.
The snapshot above shows a skip from row 5 to row 11.
The way to determine that the hidden rows are part of a filter, is to look at the cells along the row, in this case row 5.
On the right hand side, inside each cell is a filter icon
.
Additionally, the cell along row 5 and under column B, has a funnel icon, meaning that the filter is based on the conditions defined in this cell.
A further indicator that a filter exists, is that the affected row numbers in the row grid, and the column grid across the top of the sheet will be color filled to highlight them.
In these snapshots, notice how row number 5 in the row grid and the column grid at the top have a light green background instead of the standard light gray.
You can left mouse click on the funnel icon to view the filter conditions that apply.
In this instance, the filter will only display any cells in rows 6 to 10 inclusive, that are empty.
You can remove the condition to reveal the cells again (assuming no other conditions exist in any of the other cells).
The filter option will still be active (all cells will still have the filter icon), but since no conditions are in force, the cells will all be visible.
To completely remove the filter, open the Data menu and select the option Remove filter.
It’s that simple.
In fact, if you don’t want to go through the trouble of looking to see whether any filters exist anywhere on the sheet and just want to remove any filters should they exist, open the Data menu and select the option Remove filter.
If Remove filter isn’t available and instead the option Create a filter is there, it means no filters have been created for this sheet.
Alternatively, the same functionality is also available on the far right of the tool ribbon, where a color-filled funnel icon is available when a filter exists.
Clicking the icon will remove the filter.
Otherwise, an empty funnel indicates that no filter has been created.
Conclusion
As we’ve seen, there are a few ways with which rows can be hidden in Google sheets.
Looking for skipped row numbers in the row grid is the first indication that there are indeed hidden rows in a sheet.
From there, we need to look for up/down carets, plus/minus icons, or filter/funnel icons, to determine which method or methods to use to unhide the rows.
