How to Unhide Rows & Columns in Google Sheets

How to Unhide Rows in Google Sheets

Computer users have been using spreadsheets to organize and manipulate information ever since the appearance of the first personal desktop computers. Information is represented in a two dimensional matrix, with the axes commonly referred to as rows and columns.

Each piece of information is contained in a cell, and its coordinates are given as the row and column at which the cell resides. Almost any type of information can be entered in a cell, including numeric, currency, date, time, and of course various text formats.

It is customary to give columns labels or titles for easy identification, usually in the first row. The same is also possible with rows, but it is not as common, it simply depends on what information will be saved in the spreadsheet and how to best organize it.

Spreadsheets comprise sheets, each sheet acting as a compartment for a particular collection of information. Sheets can be independent of each other, or they can help to break down complex information structures into manageable, related components.

Why Hide Rows or Columns in Sheets?

Despite the flexibility offered by spreadsheets, no matter how well organized the information in the spreadsheet may be, large amounts of information can sometimes make it difficult to display everything on a computer screen. Almost always, the user will need to scroll vertically to view all rows.

In some cases, it may also be necessary to scroll horizontally, when there are more columns than can fit in the width of the screen. Scrolling up and down, or left and right, can make it very inconvenient when trying to compare entries, especially when there are hundreds of rows of information.

Google Sheets, a web based spreadsheet application, allows the user to temporarily hide unwanted rows (or columns), so that the desired bits of information appear in consecutive rows or in adjacent columns.

While this article is about how to unhide rows and columns in a sheet, knowing how rows and columns are hidden will make it easier to understand and find hidden rows and columns.

HowTo Hide Rows and Columns Tutorial

We’ll start by seeing how to hide a single row in our test spreadsheet. As can be seen in the following figure, the sheet requires us to scroll both vertically and horizontally in order to view all information.

Assuming we want to hide the fourth row, select that row and right mouse click anywhere in the selected row.

In the context menu that pops up, select Hide row. And that’s it. It was that simple.

To confirm that the row is truly hidden, check the row numbering on the very left of the sheet on the row grid, looking for row 4. The row numbering shows row 3 and then row 5 immediately below, but no row 4.

There are also an up arrow on the left of row number 3 and a down arrow on the left of row number 5. Both the missing row number and up/down arrows are an indication that a row has been hidden.

While the above process has hidden a single row, the same procedure holds true for multiple rows.

To select contiguous, successive rows, select the first of the desired rows, hold down the shift key, and select the last row in the group. Any rows in between, will also have been selected. As with the single row, right mouse click on any of the selected rows to bring up the context menu.

This time, there will be an option Hide rows 5 – 11 (the row numbers listed will depend on which rows have been selected). Left mouse click this to hide the selected rows.

Just as with the single hidden row, the row numbering will be missing for the previously selected (and now hidden) multiple rows, and once again there are up and down arrows to the left of the row numbers in the row grid.

To hide non-contiguous rows, hold the control key down while selecting each row. Right mouse click in any selected row to bring up the context menu, which this time will have the option Hide rows. Selecting this will hide each of the selected rows.

Now, there will be multiple up and down arrows displayed, as well as the missing row numbers.

Finally, the same procedures can be applied to columns. To hide a single column, either right mouse click the column letter, or hover the mouse over the column letter and click the down arrow that appears.

In both cases, a context menu will appear with the option Hide column. To hide multiple columns, select the desired columns to be hidden, right mouse click to bring up the context menu, and select the Hide columns option.

Since columns are referenced by letters, hidden columns will show up as missing letters, along with left and right arrows in the column grid.

There is one more way to hide rows (and only rows, not columns), and that is to use filters. Filters allow a user to select which rows to display (or hide) based on some criteria.

Start by selecting the range of cells to which the filter will be applied.

Then look for the funnel icon in the tool bar. If it isn’t visible, click on the ellipsis on the far right hand side of the tool bar to reveal more tools. Now click the funnel icon. This will put the selected cells in filter mode.

This will be confirmed by the change in the color of the column letter and row number grids, while the first row (column labels or titles) will now have down arrows. At this point, any filters created will apply to the selected range.

Filters are created based on the values and conditions contained in each column. In the example spreadsheet used in this article, there is a column titled Account, which contains the name of the expense accounts used to pay for various items.

An example filter would be one that hid all expense accounts except for one, say Alaska Airlines Visa.

To create a filter that would only show rows where the Account is set to Alaska Airlines Visa, left mouse click on the down arrow in the Account title cell to bring up the context menu. At the bottom of the context menu are the distinct values from all the cells in that column.

Select which choices should be included (in this case, the Alsaka Airlines Visa will be the only option selected) and click the OK button.

The sheet will update to now show only rows that match the filter settings. The row numbering will show which rows are hidden, but unlike previously, when we manually selected which rows to hide, there are no up and down arrows next to the row numbers.

This is one sign that rows are hidden, but through an applied filter.

Finally, in place of the down arrow in the Account column title, there is now a funnel to indicate that a filter has been applied.

How to unHide Rows and Columns Google Sheets

Having seen how to hide rows and columns, now gives us a better understanding of how to find those hidden rows and columns, and subsequently unhide them. The telltale signs are, missing row numbers, the up and down arrows to the left of row numbers, missing letters in the columns grid, and funnels in the column titles.

There are a couple of ways to reveal hidden rows.

Firstly, clicking on the up and down arrows to the left of rows above and below hidden rows in the row grid. This works fine when you only have a few hidden groups of rows to unhide, but how about when there are a lot more, especially on a very large dataset, and you want to unhide all of them.

In this situation, there is an easier and quicker way to reveal all hidden rows. Right mouse click in the top left corner of the sheet, where the column and row grids meet.

This will bring up the context menu. If there are any hidden rows, the context menu will have an Unhide rows option. You can also use this to confirm whether there are any hidden rows or not. Left mouse clicking this option will unhide all hidden rows.

To unhide columns, look for missing letters in the column grid and the left and right arrows. Left mouse clicking on the arrows will unhide the hidden columns.

And finally, if there are missing row numbers but no up and down arrows, look for the funnel icon in any of the cells in the first (titles) row.

To clear the filter, click on the funnel icon to bring up the context menu, and change or remove the filter criteria.

If multiple filters have been applied, to remove all filters, find the funnel icon in the tool bar (remember, if it is not showing, click the ellipsis to reveal more tools). If the funnel icon is highlighted, it means there are active filters. Click it to deactivate filtering.

Filtering can also be turned off by selecting Data > Turn off filter from the main menu.

Hiding rows and columns can help hone in on only the information of interest. But when multiple users are working on the same spreadsheet, hidden cells can be confusing for other users when information suddenly seems to have disappeared.

Unless the cells have been deleted, more than likely, they’ve been hidden. Knowing how to unhide rows and columns, or turn off filters, can save a lot headaches and wasted time.

Denise Elizabeth
Denise is a Financial Controller with experience in Office Software (Excel & Word), Accounting, Bookkeeping, Business Incorporation/LLC, Taxes and IRS Issues and more. She Graduated from Portland State University in Business Management and went onto Seattle University to complete her Graduate (Masters) Degree in Organizational Systems Renewal. She contributes to with her knowledge of Business Software, in particularly Microsoft Excel and Google Sheets.