HomeGoogle DocsSpreadsheetHide and Unhide Rows or Columns in Google Sheets - Different Methods

Hide and Unhide Rows or Columns in Google Sheets – Different Methods

Published on

There are different ways to hide and unhide rows or columns in Google Sheets.

The unhiding of rows or columns depends on different aspects. I mean, first, you must know the way the rows or columns are got hidden. Then you can think about unhiding.

There are three different methods actually. I’ll elaborate on each one of them.

This tutorial is especially for beginners. I may write a few more tutorials in this kind. Here we go!

Three Options to Hide and Unhide Rows or Columns and Benefits

When you simply want to hide rows or columns in Google Sheets, use option # 1 below.

If you want to hide a bunch of rows or columns that need to hide and unhide often, then the better method in Google Sheets is option # 2 below.

Option # 3 is for conditionally hiding rows (not columns).

Option 1 – Hide and Unhide Rows and Columns in the Normal Way in Google Sheets

How to Hide and Unhide Adjoining and Distant Columns in Google Sheets

Hide Columns:

This is the simplest and widely popular method to hide rows or columns in Google Sheets. If you are using a desktop or laptop computer, you can follow the below method to hide columns in Google Sheets.

Assume you want to hide the adjoining columns F, G, and H. Then follow the below two steps.

  1. Select the columns to hide. How?
    • First click on column letter F, press and hold the Shift key and click on column letter H.
  2. Then right-click and select ‘Hide columns F – H’.

If you want to hide distant columns, for example, column F and H only, the use Ctrl key instead of Shift.

I mean, select the first column, then press and hold the Ctrl key and click on another column.

Unhide Columns:

To unhide adjoining columns, a single column, or distant columns in Google Sheets refer to the below screenshot. Please pay your attention to the arrow key which used for unhiding the columns.

Hide and Unhide Columns in Google Sheets

How to Unhide All the Hidden Columns in a Flash in Google Sheets?

First and foremost, select the entire columns. How?

To do that click on the first column letter A, then press Ctrl+Shift key multiple (twice) times. Then right-click and select ‘Unhide columns’.

This way you can hide/unhide columns in Google Sheets.

Hide Adjoining and Distant Rows

Hide Rows:

As an example, to hide the adjoining rows 3 to 5, click on row # 3, press and hold the shift key, and then click on row # 4.

Right-click and choose ‘Hide rows 3 – 5’ to hide the adjoining rows 3 to 5 in Google Sheets.

To hide distant rows, use the Ctrl key instead of the Shift key.

Unhide Rows:

Follow the below method (screenshot) to unhide, adjoining rows, a single row, or distant rows in Google Sheets.

Hide and Unhide Rows in Google Sheets

How to Unhide All the Hidden Rows in a Flash in Google Sheets?

To unhide all the hidden rows in a flash, select all the rows in the sheet and then right-click and choose ‘Unhide rows’.

Note

After selecting the columns or rows, you can also perform the above actions (hiding/unhiding of rows/columns), using shortcut keys.

Go to the Help menu within your sheet and select ‘Keyboard shortcuts. Search ‘hide’ and hit enter to find the supporting shortcuts.

If you ask me about the benefit/pros of this method (Option # 1), let me say, it is the simplest method to hide/unhide random rows and columns in Google Sheets.

Option 2 – Hide and Unhide Rows or Columns Using Group Command in Google Sheets

Here is another way to hide/unhide rows or columns in Google Sheets.

Grouping is the best way to hide and unhide a bunch of rows in Google Sheets. That’s the benefit of using this option.

I’ll explain where you will find this option useful in real life.

Columns

Countif Attendances in Sheets

In the above example, column A contains names of students, and columns B to K contain their present and absent status.

Using the below COUNTIF in cell L2 which then dragged to cell L8, I have counted the total “P” (present) of each student.

=countif(B2:K2,"P")

I just don’t want to see column B to K always. How to hide and unhide the columns B to K using the Data menu Group feature? Here are the steps.

  1. Select columns to B to K.
  2. Right-click and select ‘Group columns B – K.
  3. Then click on the - button to hide the columns and + button to unhide the columns. It’s actually called collapse and expand column group.
Group Columns Instead of Hiding Columns

To entirely remove the grouping, right-click anywhere on the grouping bar (the straight line on the top) and select the ‘Remove group’ command.

Rows

Here I am not repeating the whole steps again. Just follow the above column grouping to hide and unhide a bunch of rows.

Instead of columns, here select rows, and group rows. Further here are some advanced tutorial related grouping of rows.

  1. How to Group Rows and Columns in Google Sheets.
  2. Get Total Only When Group | Subgroup Is Collapsed in Google Sheets.
  3. Grouping and Subtotal in Google Sheets and Excel.

Option 3 – Using Create a Filter Command

Using the data menu ‘Create a filter’ we can conditionally hide rows in Google Sheets (benefit/pros). It’s just for rows (cons).

In the below example, I want to hide and unhide the rows containing ‘Tulips’. How to do that using the ‘Create a filter’ menu command in Google Sheets?

Steps:

  • Select the range A1:A13 or in case you want to include future rows in the filter, select entire column A.
  • Go to Data > Create a filter.
  • It’ll leave a drop-down menu icon in the very first cell in the range, here it is in cell A1.
  • Click it and remove the tick mark against ‘Tulips’ and click OK.

This will hide the rows containing ‘Tulips’.

To unhide go to the drop-down again and make a tick mark against ‘Tulips’ or click on Data > turn off the filter.

Filter Menu – Resources

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here