/ How to

How to Freeze and Lock Columns in Microsoft Excel Sheet

Introduction

When working with large Excel worksheets containing a lot of data, it can be difficult to keep track of what the columns contain when scrolling horizontally across the sheet. Freezing columns allows you to lock specific columns in place so they remain visible when scrolling to other parts of the worksheet.

Locking columns prevents accidental edits to important data columns. This guide will cover multiple methods to freeze and lock columns in Excel.

Freeze Columns Using Freeze Panes

The easiest way to freeze columns is using the Freeze Panes feature:

  1. Select the cell immediately to the right of the column(s) you want to freeze. For example, to freeze columns A, B and C, click cell D1.
  2. Go to the View tab > Windows group and click Freeze Panes.
  3. Select Freeze Panes from the drop-down menu.

This will freeze all columns to the left of the selected cell. You can now scroll horizontally and the frozen columns will remain visible.

Freeze First Column

To quickly freeze only the first column:

  1. Go to View tab > Windows group
  2. Click the Freeze Panes drop-down menu
  3. Select Freeze First Column

Freeze Multiple Columns with a Keyboard Shortcut

You can also freeze multiple columns using a handy keyboard shortcut:

  1. Select the cell immediately to the right of the columns you want to freeze
  2. Press the keyboard shortcut:
    • Windows: Alt + W + F + R
    • Mac: Option + W + F + R

This will freeze all columns to the left of the selected cell.

Lock Columns

Freezing columns only locks them visually on the screen. To prevent editing the columns, you need to lock them:

  1. Select the column(s) you want to lock
  2. Right click and select Format Cells
  3. Go to the Protection tab
  4. Check the Locked checkbox
  5. Click OK

Now the selected columns cannot be edited. However, this only works when sheet protection is enabled:

  1. Go to Review tab
  2. Click Protect Sheet
  3. Enter a password (optional), click OK

With sheet protection enabled, users will be unable to edit the locked columns without the password.

Unlock and Unfreeze Columns

  1. Go to Review tab and click Unprotect Sheet if sheet protection is enabled
  2. Enter the password (if required)
  3. Select the locked columns
  4. Open the Format Cells dialog > Protection tab
  5. Uncheck the Locked checkbox and click OK
  6. Go to View tab > Freeze Panes > Unfreeze Panes

The columns are now unlocked and unfrozen, allowing editing again.

Conclusion

Freezing and locking columns in Excel ensures important data remains visible while scrolling and prevents accidental edits. This helps organize large datasets and maintain data integrity.

The freeze panes and sheet protection features offer flexible options to manage locked and frozen columns. Use these tips to optimize large Excel worksheets.

Related Posts