3 Ways to Hide and Unhide Rows or Columns in Google Sheets

Do you need to hide or unhide rows and columns in your Google Sheets?

This post will show you how to do just that. Whether you want to keep certain data hidden from view or make it available again, we’ll walk you through it step by step.

Hiding rows in Google Sheets can be useful for a variety of reasons. If you have a large dataset and only want to focus on particular rows or columns, hiding the data that isn’t relevant can help to declutter your sheet.

When the data is needed, it’s easy to recover those hidden rows and columns by making them visible again.

This post will show you how to hide and unhide rows and columns by using the right-click menu, keyboard shortcuts, and app scripts.

Get your copy of the example workbook and follow this post to see how easy it is to hide or unhide rows and columns in Google Sheets!

Hide Rows or Columns using the Right-Click Menu

This sample data contains information about the number of trips taken between January and June. Let’s say you only want to show the trip information for the first three months, here’s how to do it.

Follow these steps to hide rows.

  1. Select the rows you want to hide.

Place your mouse on the row number and use the left button on your mouse to select it.

Press and hold the Ctrl key while using the left button of your mouse to select multiple rows. You can also select multiple non-consecutive rows this way.

  1. Right-click your mouse on any cell in the last row you selected.
  2. Click on Hide rows from the options.

Now you can only see the trip information for the first three months.

When you hide rows in a sheet, you will get two arrays facing opposite directions. This informs you that you have hidden data in your sheet in between those visible rows.

When using the right-click menu to hide rows, you must make certain that you only right-click on the last one you selected. Doing otherwise will remove every other row from the selection.

If you have a long list of selected rows, just go to the cell reference bar to find the row number of the currently active cell.

The steps for hiding columns are the same for hiding rows.

Here, the trip information has been split into two groups. We only want to see information about domestic trips.

Follow these steps to hide columns.

  1. Select the columns you want to hide.
  1. Right-click on the selected column, or the last column selected if you selected multiple columns.
  2. Click on the Hide column option.

Now your columns are hidden!

You will also see the two arrows facing opposite directions to indicate hidden columns.

Unhide Rows or Columns using the Right-Click Menu

Unhiding rows or columns is as easy as hiding them.

Simply click on the arrows that appear when you hide rows/columns, and the hidden data will appear back on the spreadsheet.

You can also select the rows or columns containing the arrow icons then right click and select the Unhide rows option from the menu.

Rows are easily viewable again unlike when you delete rows in Google Sheets.

Hide Rows or Columns with a Keyboard Shortcut

You can use keyboard shortcuts to hide or unhide rows or columns in your spreadsheet.

The keyboard shortcuts are a great alternative to the right-click method because it removes the chances of getting highlighted rows or columns deselected.

The process is very simple.

Select the rows you want to hide, then press Ctrl + 9 to hide those rows.

You can also hide columns with a keyboard shortcut.

Select the columns you want to hide, then press Ctrl + 0 to hide those rows.

There are other shortcut combinations, but these are the simplest to remember and use.

Unhide Rows or Columns with a Keyboard Shortcut

Using a keyboard shortcut to unhide rows or columns is a bit tricky compared to using the right-click option.

You still have the hidden row or column indicator, but if you want to use the keyboard shortcut, then you should take note of the steps detailed below.

Select the range containing the hidden rows by selecting the rows containing the arrows icons.

Press and hold the Shift key while using the left-click mouse button to select the rows to hide.

Note, you must select the rows with the Shift key, as selection with the Ctrl key will not work. The same thing goes for unhiding columns with a keyboard shortcut.

When you’ve made the selection, use the Ctrl + Shift + 9 keyboard combination to unhide the rows.

To unhide columns, use the Alt, H, O, U, L keyboard shortcut sequence.

Hide Rows or Columns with an Apps Script

You can create an app script to help you hide columns or rows based on a particular action.

For this example, we’ve added tick boxes to our sample data. Say you want to automatically hide any row with a checkmark, you can do that with an app script.

Go to the Extensions tab and select the Apps Script option to open the app script editor.

function onEdit(e){
  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var myRange = e.range;
  var mySheetName = mySheet.getSheetName();
  var myStatus = myRange.getValue();
  var currRow = myRange.getRow();
  var currCol = myRange.getColumn();
  if (
    mySheetName === 'Hide rows with Apps Script'
    && currCol === 1
  ) {
    if (
      myStatus
    ) {
      mySheet.hideRows(currRow, 1);
    }
  }
  else if (
    mySheetName === 'Hide columns with Apps Script'
    && currRow === 1
    ) {
    if (
      myStatus
    ) {
      mySheet.hideColumns(currCol, 1);
    }
  }
}

Copy and paste this script into your script editor and press the Save button. Click on Run and grant the necessary permissions. Then go to your spreadsheet and refresh it.

The script will help will hide any row when you add a checkmark to its corresponding checkbox.

It will also hide columns when you tick a checkbox.

You can change the sheet name appropriately by altering the highlighted portion of the script.

Unhide Rows or Columns with an Apps Script

You can also create a script that will unhide all the rows and columns in your sheet.

This way you don’t need to select any rows or columns to unhide.

In your script editor window, click on the cross icon and select Script to create a new file. This will create a new script window where you can paste this new script.

function unhideRowsOrColumns() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var sheetName = sheet.getSheetName()

  if (
    sheetName === 'Hide columns with Apps Script'
    ) {
    sheet.showColumns(1,26)
  }
  else if (
    sheetName === 'Hide rows with Apps Script'
    ) {
    sheet.showRows(1,1000)
  }
}
function onOpen(){
  SpreadsheetApp.getUi()
  .createMenu('View Rows/Columns')
  .addItem('Unhide Rows/Columns', 'unhideRowsOrColumns')
  .addToUi();
}

After pasting this script and you’ve saved it, it will create a new menu in your spreadsheet when you refresh it.

This new menu item called View Rows/Columns will unhide rows or columns in the given sheet.

You can use the new menu option to show all hidden rows and columns in a sheet.

Conclusions

While you might be tempted to delete redundant data, hiding the data is usually a better idea. That way it’s easy to recall them if you need them later.

There are several easy ways to hide both rows and columns, and it’s just as easy to later unhide them.

This is possible from the right-click menu, keyboard shortcut, or an apps script.

What other ways do you use to hide or unhide rows and columns in Google Sheets? Let me know in the comments below!

About the Author

Oluwaseun Olatoye

Oluwaseun Olatoye

Oluwaseun is a business intelligence analyst with expertise in Google Sheets and SQL programming language. He has worked with various businesses to make data-driven decisions. He enjoys helping others learn and grow.

Related Articles

Comments

0 Comments

Submit a Comment

Your email address will not be published.

Get the Latest Google Sheets Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in Google Sheets!