How To Hide Columns & Rows In Google Sheets

Kieran Dixon Updated:  June 18, 2021

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:

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

You can also learn how to:

• Hide unused columns and rows (quickly)
• Conditionally hide columns and rows based on cell value
• Hide columns and rows from specific users (kind of)

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:

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

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

Here's a clip of the whole process:

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

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:

It's that easy.

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.

Alternative STEP 2: Right click on the selection you've made and choose Unhide column/s or Unhide row/s from the 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:

//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:

• A custom menu to make running the script easy from the sheet
• A function to get the current sheet and its contents
• A function for each menu item
• The functions that actually find and hide the unused columns and rows

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:

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:

//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:

• Creates a custom menu so you can run the script easily from the sheet interface
• Hides columns or rows when the value in the first column or row is TRUE (which in this example is a ticked checkbox)

Here's what it looks like in action:

How To Hide Columns And Rows From Specific Users

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

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:

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 File ➜ Make 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:

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:

• Pull in all of the 'public' data using IMPORTRANGE
• Add in the 'private' data

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

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:

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:

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

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:

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:

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:

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

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:

It's that easy.

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

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.

You might like:

Line Breaks In Google Sheets [In Cells & Formulas]

Need multiple lines in a Google Sheets cell? Learn every way to add line breaks and new lines on desktop, iPhone, iPad, and Android.

Google Sheets Colors [Hexadecimal & RGB]

Want the details of the colors in Google Sheets, Docs, and Slides? Here are the names, hexadecimal codes, and RGB for them all.

How To Round In Google Sheets

Need to round up and down in Google Sheets? Find out how to round using both formulas and formatting on both desktop and mobile.