How To Freeze A Row In Google Sheets

In this tutorial, you’ll learn how to:

  • freeze a row in Google Sheets
  • freeze a column in Google Sheets
  • unfreeze rows and columns in Google Sheets
  • use an incredible freeze rows shortcut, and
  • use Apps Script to work with frozen rows/columns

Freezing rows and/or columns is a useful and simple technique to lock rows (or columns) of your spreadsheet, so that they remain in view even as you scroll down your datasets. You’re anchoring them in place.

It’s really useful because you can keep your row and column headings in view as you move about your data, as illustrated in this image:

Freeze Top Row Google Sheets

How To Freeze A Row In Google Sheets

Menu Method

Go to the menu: View > Freeze

Choose how many rows or columns you want to freeze:

How to lock a row in Google Sheets

Google Sheets Freeze Row Shortcut Trick

Hover over the bottom of the column heading letters until you see the hand icon, like this:

How to freeze a row in Google Sheets

Here’s a close up of that hand for you:

Freeze rows shortcut trick in Google Sheets

Click and hold your mouse button down, and drag the thick line down:

Google Sheets Freeze Row

Let go when you’ve reached the row level you want to freeze (in this case, just the top row):

Google Sheets freeze rows and columns

This technique also works for freezing columns!

How To Freeze A Row In Google Sheets Using Apps Script

If you use freeze rows/columns often and know basic Apps Script, it’s easy to create a simple frozen rows/columns helper tool.

(If you’re new to Apps Script, check out: Google Apps Script: A Beginner’s Guide.)

Identify Frozen Rows And Columns

Use the following getter methods to retrieve information about any frozen rows or columns in your Sheets:

getFrozenRows()

getFrozenColumns()

Here’s a simple script to find all frozen rows and columns across all the sheets in your Google Sheet:

// find all frozen rows or columns across all Sheets
function identifyAllFrozenRowsColumns() {
  
  // get array of Sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();

  // variable to hold freeze rows info
  let freezeInfo = '';

  // find merged cells in each
  sheets.forEach(sheet => {

    // find merged cell info for this sheet
    const newFreezeInfo = findFrozenRowsColumnsInSheet(sheet);
    
    // add new merged cell info to existing info
    freezeInfo = freezeInfo + newFreezeInfo;

  });

  // output summary pane of merged cells info
  SpreadsheetApp.getUi().alert(freezeInfo);
}

// find frozen rows and columns in specific sheet
function findFrozenRowsColumnsInSheet(sheet) {
  
  // get the merged ranges
  const sheetName = sheet.getName();
  const frozenRows = sheet.getFrozenRows();
  const frozenCols = sheet.getFrozenColumns();

  // string variable to hold merged cell info
  let freezeString = '';

  freezeString = freezeString + 'Sheet: ' + sheetName + '\n'; // which Sheet
  freezeString = freezeString + 'Frozen rows: ' + frozenRows + '\n'; // how many frozen rows
  freezeString = freezeString + 'Frozen columns: ' + frozenCols + '\n\n'; // how many frozen columns

  // return merged cell info to main function
  return freezeString;

}

What does this script do?

It’s very simple. It gets all your sheets in a Google Sheet, loops over all these sheets and gets the frozen row and column information to display in an alert box:

Apps Script Freeze Rows

Remove All Frozen Rows And Columns

You have the following setter methods to set the frozen rows/columns in your Sheets:

setFrozenRows(Integer)

setFrozenColumns(Integer)

You can use these last two setters to remove all the frozen rows and columns in your Google Sheet, potentially a big time saver if you have a lot of them you need to remove.

Simply set the frozen rows and columns to 0 in all the sheets in your Google Sheet:

// remove all frozen rows and columns
function removeAllFrozenRowsColumns() {

  // get array of Sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();

  // find merged cells in each
  sheets.forEach(sheet => {

    sheet.setFrozenRows(0);
    sheet.setFrozenColumns(0);

  });

}

Finally, you’ll want to add a menu to your Google Sheet to make it easy to run these helper functions:

// custom menu to operate frozen rows helper from Sheet
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Freeze Rows Helper')
      .addItem('Identify Freeze Rows or Columns', 'identifyAllFrozenRowsColumns')
      .addItem('Remove All Freeze Rows or Columns', 'removeAllFrozenRowsColumns')
      .addToUi();
}

This function will create a new custom menu in your Google Sheet, giving you quick access to the two freeze rows helper functions you created:

Apps Script Freeze Rows Menu

All the Google Sheets freeze rows code is available here on GitHub.

Leave a Reply

Your email address will not be published. Required fields are marked *