Document Editing Problems

How to Lock Specific Columns in Excel

John Wilson Oct 20, 2026 868 views

Question details

The user wants to protect specific columns in an Excel worksheet from being edited while allowing the rest of the spreadsheet to remain available for modifications.

How to Lock Specific Columns in an Excel Worksheet

Product: Excel
Device & OS: not provided
Scenario: Setting up a shared or restricted worksheet where only certain columns containing sensitive data or formulas need to be locked.
Observed behavior: The target columns are locked and protected against changes, whereas all other cells in the worksheet can be edited freely by users.

Before you start

Ensure your worksheet is currently unprotected before you begin. If the sheet is already protected, you will need to go to the Review tab and click Unprotect Sheet first.

Solution 1Recommended

Lock Specific Columns Using Format Cells and Protect Sheet

This method involves unlocking all cells in the worksheet first, locking only the specific columns you want to protect, and then applying worksheet protection.

By default, every cell in a new Excel worksheet has its 'Locked' property enabled. However, this property does nothing until you protect the worksheet. To lock only specific columns, you must first clear the locked status for the entire sheet.

Select the entire worksheet

Click the triangle icon in the top-left corner of the worksheet (where the row and column headers meet), or simply press Ctrl+A on your keyboard to select all cells.

Unlock all cells

Right-click anywhere on the selected cells and choose 'Format Cells'. In the dialog box, go to the 'Protection' tab, uncheck the 'Locked' checkbox, and click 'OK'.

Select the specific columns to protect

Click the column letters at the top (e.g., Column B or C) to highlight the specific columns you want to prevent users from editing.

Lock the chosen columns

Right-click anywhere within the selected columns and select 'Format Cells' again. Navigate back to the 'Protection' tab, check the 'Locked' box, and click 'OK'.

Enable worksheet protection

Go to the 'Review' tab on the Excel ribbon and click 'Protect Sheet'. In the prompt, enter a password if desired, configure the permissions you want to grant to users, and click 'OK'.

Lock Specific Columns Using Format Cells and Protect Sheet

Password Protection: If you choose to set a password during the Protect Sheet step, make sure to remember it or store it safely. Without the password, you will not be able to unprotect the sheet to edit the locked columns in the future.

Lock columns effortlessly with WPS Spreadsheet

Easily Lock and Protect Columns Using WPS Office

WPS Spreadsheet provides a highly compatible and user-friendly interface to manage worksheet protection. You can lock specific columns just as you would in Microsoft Excel, with full support for Excel file formats.

1. Unlock all cells: Open your file in WPS Spreadsheet, press Ctrl+A to select all cells, right-click to choose 'Format Cells', go to the 'Protection' tab, and uncheck 'Locked'.
2. Lock target columns: Highlight the specific columns you want to protect, right-click and open 'Format Cells' again, check the 'Locked' box, and click 'OK'.
3. Protect the sheet: Navigate to the 'Review' tab, click 'Protect Sheet', set a password if needed, and confirm to enforce the column protection.

Fully compatible with Microsoft Excel file formats (.xls, .xlsx).Intuitive interface for locking cells, hiding formulas, and protecting worksheets.Free, lightweight, and fast-loading office suite for Windows, Mac, and Linux.Familiar ribbon layout ensures a seamless transition with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I lock specific rows instead of columns?

Yes. The process is exactly the same. After unlocking all cells in the worksheet, simply select the specific rows you want to protect, lock them via the 'Format Cells' menu, and then protect the sheet from the Review tab.

How do I edit a locked column after the sheet is protected?

To edit a locked column, you must temporarily remove the worksheet protection. Go to the Review tab, click 'Unprotect Sheet', and enter your password if you set one. Once unprotected, you can edit the columns freely.

Why is the 'Locked' option in Format Cells greyed out?

If the 'Locked' checkbox is greyed out and cannot be clicked, it means the worksheet is currently protected. You must unprotect the worksheet from the Review tab before you can modify any cell protection settings.

Can I allow users to format cells even if the column is locked?

Yes. When you click 'Protect Sheet' in the Review tab, a dialog box will appear showing a list of actions you can allow users to perform. Check the box for 'Format cells' or 'Format columns' before clicking OK to permit these actions on locked cells.

Document & File Issues