Google Sheets Cheat Sheet

How To Hide Columns & Rows In Google Sheets

Select the column/s or row/s you want to hide. Right click on this selection and choose Hide column/s or Hide row/s from the menu that appears.

To unhide, click on the arrows the appear where there are hidden columns or rows:

the arrows that appear in column and row labels to indicate the presence of hidden columns or rows

Things get messy sometimes. Learn how to hide the mess in your spreadsheet:

ABC
1HideUnhide
2DesktopViewView
3iOSViewView
4AndroidViewView

You can also learn how to:

If you want a more user-friendly way to hide and reveal rows and columns you should consider 'grouping' them instead.

How To Hide Columns & Rows In Google Sheets

Not on desktop? Click for mobile instructions: iPhone/iPad and Android.

STEP 1: Select the column/s or row/s you want to hide by clicking and dragging on the relevant column/row labels:

shows how to select columns or rows to be hidden in google sheets

STEP 2: Right click on the selection you've made and choose Hide column/s or Hide row/s from the menu:

shows the location of the hide columns and rows buttons in the right click menu

You can also use the following keyboard shortcuts:

ABC
1ShortcutWindowsMac
2Hide columnsCtrl+Alt+0
Ctrl+9 or Alt+H,O,U,R or Alt+O,R,H
⌘+Option+0
⌘+0
3Hide rowsCtrl+Alt+9
Ctrl+9 or Alt+H,O,U,R or Alt+O,R,H
⌘+Option+9
⌘+9

When you've hidden the column/s or row/s you'll notice arrows in the labels where they are now hidden:

the arrows that appear in column and row labels to indicate the presence of hidden columns or rows

Here's a clip of the whole process:

shows the whole process of hiding columns and rows using the right click menu in google sheets

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

How To Unhide Columns & Rows In Google Sheets

Not on desktop? Click for mobile instructions: iPhone/iPad and Android.

STEP 1: Click on the arrows in the labels where the column/s or row/s are hidden:

shows how to unhide columns and rows in google sheets by clicking on the arrows in the column and row labels that indicate hidden columns and rows are present

It's that easy.

You can also use the following keyboard shortcuts:

ABC
1ShortcutWindowsMac
2Unhide columnsCtrl+Shift+0
Alt+H,O,U,L or Alt+O,C,U
⌘+Shift+0
3Unhide rowsCtrl+Shift+9
Alt+H,O,U,O or Alt+O,R,U
⌘+Shift+9

Alternative STEP 1: Select the columns or rows either side of the hidden column/s or row/s by clicking and dragging on the relevant column/row labels.

shows how to select columns and rows in google sheets to unhide them using the right click menu

Alternative STEP 2: Right click on the selection you've made and choose Unhide column/s or Unhide row/s from the menu:

shows the location of the unhide columns and rows buttons in the right click menu

How To Hide Unused Columns And Rows

Hiding unused columns and rows can clean up the look of a spreadsheet.

It would be painful to find, highlight, and hide each column and/or row in a sheet that doesn't contain data. Plus, you might miss some or hide some that do actually contain data.

Google Apps Script to the rescue!

Here's a script that will do all of the hard work for you:

Dark theme
Copy code
//Custom menu
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Hide Unused')
    .addItem('Columns', 'hideCols')
    .addItem('Rows', 'hideRows')
    .addItem('Both', 'hideBoth')
    .addToUi();
}

//Returns object with sheet and its content
function getSheetData() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const contents = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).getValues();

  return {
    sheet: sheet,
    contents: contents,
  }

}

function hideBoth() {
  const sheetData = getSheetData();
  hideUnusedRows(sheetData.sheet, sheetData.contents);
  hideUnusedCols(sheetData.sheet, sheetData.contents);
}

function hideRows() {
  const sheetData = getSheetData();
  hideUnusedRows(sheetData.sheet, sheetData.contents);
}

function hideCols() {
  const sheetData = getSheetData();
  hideUnusedCols(sheetData.sheet, sheetData.contents);
}

function hideUnusedRows(sheet, contents) {

  contents.forEach((row, i) => {

    //Join row into a single string
    const rowString = row.join('');

    //If this string is empty, so is the row (which should be hidden)
    if (rowString === '') sheet.hideRows(i + 1);

  });

}

function hideUnusedCols(sheet, contents) {

  contents[0].forEach((col, i) => {

    //Get column and join it into a single string
    const colString = contents.map(x => x[i]).join('');

    //If this string is empty, so is the column (which should be hidden)
    if (colString === '') sheet.hideColumns(i + 1);

  });

}

In this script you've got:

Let's take a closer look at the functions that actually do the hard work hideUnusedCols(sheet, contents) and hideUnusedRows(sheet, contents).

In each of these functions we isolate a single column (contents.map(x => x[i])) or row and .join('') every 'cell' in the column or row into one (potentially long) piece of text (known as a 'string').

If every cell in a column or row is blank (and therefore unused) the string made will be empty (if (colString === '')) and that column can be hidden.

The script is doing a lot but it works fast. It only takes a couple of seconds to find and hide the unused columns and rows:

shows how some google apps script triggered by a custom menu can quickly find and hide unused rows and columns in google sheets

How To Conditionally Hide Columns And Rows Based On Cell Value

Hiding columns and rows conditionally (based on cell value, color, formula…) can be done using Google Apps Script.

I can't write a script for every possible situation, but here's one to give you an idea of how to make it happen:

Dark theme
Copy code
//Custom menu
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Conditional Hide')
    .addItem('Columns', 'hideTrueColumns')
    .addItem('Rows', 'hideTrueRows')
    .addToUi();
}

function hideTrueColumns() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const contents = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).getValues();

  contents[0].forEach((col, i) => {

    if (col === true) sheet.hideColumns(i + 1);

  });
  
}

function hideTrueRows() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const contents = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).getValues();

  contents.forEach((row, i) => {

    const firstCol = row[0];
    if (firstCol === true) sheet.hideRows(i + 1);

  });

}

This script:

Here's what it looks like in action:

shows how some google apps script triggered by a custom menu can quickly conditionally hide rows and columns with specific cell values in google sheets

How To Hide Columns And Rows From Specific Users

If a user has 'Viewer' or 'Commenter' permissions:

shows the three permission levels when sharing a google sheet - editor, viewer, and commenter

They cannot unhide hidden columns and rows.

If the user has 'Editor' permissions, hiding columns from them isn't easily done. In fact, you could say it can't be done.

Google Sheets was designed for collaboration and connectivity, not secrecy.

Even when you think your sheet is protected it isn't.

When you protect a sheet and then hide it, a user who the sheet is protected from won't be able to unhide it to see its content:

shows the warning message that appears when a user tries to unhide a sheet that is protected from them

However, they can still:

  • Use a formula like ={'Sheet Name'!A:Z} to view its content in a sheet they do have access to, or
  • Create a copy using FileMake a copy as they will be the owner of the copy and protections don't carry over.

But you're here for answers so let's think about a way to get around this.

What if instead of hiding columns from specific users, we simply give them a near identical sheet without the data we don't want them to see.

The IMPORTRANGE function will get this done:

=IMPORTRANGE(spreadsheet_url, range_string)

First you make a Google Sheet with all of the 'public' data that anyone can see.

Then you make a second Google Sheet in which you:

Share the 'public' sheet with those who can only see that data and the 'private' sheet with those who can see it all.

Don't try this the other way around.

If you put both public and hidden data in the first sheet and pull the public data using IMPORTRANGE into the second sheet a savvy Google Sheets user will be able to access the hidden data.

Once again, this isn't what spreadsheets are designed for.

How To Hide Columns & Rows In The Google Sheets iPhone & iPad App

STEP 1: Select a single column or row by tapping on the row label. If you want to hide more than one column or row, select as many as you'd like by dragging your selection using the round handles on the edge of your selection:

shows how to select columns and rows to hide in the google sheets iphone and ipad app

STEP 2: Tap on the selection and scroll to the right on the menu that appears. When you see Hide column/s or Hide row/s tap them:

shows how to hide columns and rows by tapping on a selection and scrolling through the menu that appears in the google sheets iphone and ipad app

When you've hidden the column/s or row/s you'll notice arrows in the labels where they are hidden:

the arrows that appear in column and row labels to indicate the presence of hidden columns or rows in the iphone and ipad app

How To Unhide Columns & Rows In The Google Sheets iPhone & iPad App

STEP 1: Tap on the arrows in the labels where the column/s or row/s are hidden:

shows how to unhide columns and rows by tapping on the arrows in the row labels when columns and rows are hidden in the google sheets iphone and ipad app

It's that easy.

How To Hide Columns & Rows In The Google Sheets Android App

STEP 1: Select a single column or row by tapping on the row label. If you want to hide more than one column or row, select as many as you'd like by dragging your selection using the round handles on the edge of your selection:

shows how to select columns and rows to hide in the google sheets android app

STEP 2: Tap on the selection and tap on the stacked dots on the menu that appears. In this extended menu, scroll to find either Hide column/s or Hide row/s and tap on that option:

shows how to hide columns and rows by tapping on a selection and scrolling through the menu that appears in the google sheets android app

When you've hidden the column/s or row/s you'll notice arrows in the labels where they are hidden:

the arrows that appear in column and row labels to indicate the presence of hidden columns or rows in the android app

How To Unhide Columns & Rows In The Google Sheets Android App

STEP 1: Tap on the arrows in the labels where the column/s or row/s are hidden:

shows how to unhide columns and rows by tapping on the arrows in the row labels when columns and rows are hidden in the google sheets android app

It's that easy.


FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.