Google Sheets offers a few ways to unhide rows in a spreadsheet.
If you’re new to Google Sheets or you’ve never relied on the “hide rows” feature, you may be a little confused looking at a workbook where rows are missing from the display.
Hiding rows in Google Sheets is very helpful for showing off the data you want someone to look at in a spreadsheet.
However, if you are trying to look at all the data in a spreadsheet, you need to unhide the data found in hidden rows.
This tutorial covers a few ways to control the “show rows” features in Google Sheets.
Let’s get started!
- Unhide Rows by Clicking the Up/Down Caret Icon
- Unhide Selection Range
- Set Groups for Fast Unhide/Hide of Rows
- Unhide Filtered Rows in Google Sheets
- Sumit
Unhide Rows by Clicking the Up/Down Caret Icon
Google Sheets displays upward and downward-facing caret icons on the rows above and below hidden row ranges.
The following spreadsheet is hiding rows 4 through 6. You can tell those rows are missing because the numbering jumps from 3 to 7 and there are up/down caret icons next to the range.
If you left-click on either of the caret icons, Google Sheets will show the data in the hidden rows again.
The below image shows how Google Sheets will automatically select the data in the formerly hidden row range upon reveal.
Unhide Selection Range
Clicking the icons is straight-forward for showing individually hidden row groupings. However, you may encounter more complicated use cases where you want to unhide multiple row groups or all the rows on a page.
In the following example, Google Sheets is hiding nonconsecutive rows in two groups.
We can’t see the information in rows 4, 5, 8, and 9. The hidden rows are designated by the caret icons above and below the neighboring row numbers. The process of unhiding two or more groups of hidden rows is the same.
The following steps demonstrate how to unhide multiple hidden row groupings simultaneously.
- Highlight the desired range of rows you want to show. Manually select the range by left-clicking a row above the first hidden group. Then hold the “shift” key and select a row below the hidden group
range.
- The example shows clicking on rows 3 and 10. This will highlight the entire range of hidden rows. Alternatively, you can select the entire sheet if you want to Ctrl+A on Windows/ChromeOS or Command+A on
MacOS.
- Right-click somewhere within the selected range and choose“Unhide rows” from the menu. Alternatively, you can use the keyboard shortcut Ctrl+Shift+9 on Windows/ChromeOS or Cmd+Shift+9 on
MacOS.
Google Sheets will now show and select all the rows within the formerly hidden range.
Set Groups for Fast Unhide/Hide of Rows
Google Sheets has a built-in feature for easy row hiding and showing that doesn’t go away when you choose to unhide data. The “group” function assigns a “+/-” icon to a row range that toggles show/hide functionality.
You may encounter workbooks that are already using “group” to hide data. The following example shows two hidden groups designed by a “+” icon to the left of the hidden row range.
Just left-click on the icon to show the hidden rows. If you’re working on a shared spreadsheet, adding group-based hiding is a great way to preserve the hidden row feature someone else added to the sheet while still being able to view content in hidden rows.
Assigning a group is a straightforward process.
- Select
the rows you want to group by clicking the first row and holding shift while you click the last row. The example shows highlighting rows 7 through
9.
- Right-click on the highlighted data range
and select “Group rows” from the
menu.
- Toggle hide/show by left-clicking the +/- symbol at the top of each
group.
Google Sheets will then hide that data range, but leave the control icon to the side.
Unhide Filtered Rows in Google Sheets
If you’re looking at a spreadsheet Google Sheets with hidden rows, but you can’t find any visual indication of a method to show the missing data, you’re probably looking at a filter that hides content.
You can tell if a spreadsheet is using filters by the filter icon appearing in cells. This example shows filters in the header row.
Fortunately, you can disable filters easily by opening the “Data” drop menu and selecting the “Turn off filter” option.
Note: All these methods also work for unhiding columns.
These methods cover the different ways you can unhide hidden rows in Google Sheets.
I hope you found this tutorial useful!
Spreadsheet Expert at Productivity Spot | Website | + posts
Google Sheets and Microsoft Excel Expert.