How to Lock Columns in Excel (4 Methods)

The dataset below will be used for illustration.

Lock Columns in Excel

By default, an Excel worksheet has all its cells locked. But, it has no effect until the sheet is protected. To check this:

  • Click the Select All button at the top-left corner of the spreadsheet.

Lock Columns in Excel

  • Right-click to open up the context menu and choose the Format Cells.

Lock Columns in Excel

  • In the Format Cells window, the Protection tab shows the Locked Checkbox selected by default.


Method 1 – Lock All Columns with Protect Sheet in Excel

  • In the Excel Ribbon, navigate to the Review Tab to select Protect Sheet.

Lock Columns in Excel

  • Protect with Password: In the Protect Sheet window put a suitable password that will be required to unprotect the sheet.

Lock Columns in Excel

  • Another window will pop up to confirm the password. Put the same password from the previous step and hit OK.

Lock Columns in Excel

  • Click on any cell on the spreadsheet, it will show a warning.

  • Protect without Password: Leave the password input box and just click OK.


Method 2 – Lock Specific Columns by Using Home Tab 

  • Click the upper-left button to select the whole worksheet.

Lock Columns in Excel

  • In the Home Tab click the arrow from the Alignment.

Lock Columns in Excel

Another Way: From the Home Tab click the Orientation and choose Format Cells Alignment from the dropdown.

Lock Columns in Excel

  • In the Format  Cells window, uncheck the Locked checkbox from the Protection Tab and hit OK.

Lock Columns in Excel

  • Select the Price column.

  • In the Home Tab click the arrow from the Alignment. In the Format Cells window check the Locked checkbox from the Protection Tab and hit OK. It’ll lock the selected Price column.

Lock Columns in Excel

  • Protect with Password: In the Protect Sheet window put a suitable password that will be required to unprotect the sheet. You can also select different options to allow user actions to the worksheet by checking the square boxes.

Lock Columns in Excel

  • A window will pop up to confirm the password. Put the same password from the previous step and hit OK.

Lock Columns in Excel

  • When any cell is clicked on the Price column, it will show a warning.

  • Protect without Password: Leave the password input box and click OK.

  • If any value in the Unit Price or Quantity column is changed, it will make adjustments in the Price column accordingly.

Method 3 – Using Context Menu to Lock Selected Columns in Excel

  • Click the upper-left button to select the whole worksheet. Use right-click to select the Format Cells

Lock Columns in Excel

  • In the Format Cells window, uncheck the Locked checkbox from the Protection Tab and hit OK. This unlocks the whole worksheet.

Lock Columns in Excel

  • Select the Price column of the dataset and right-click to select the Format Cells option.

Lock Columns in Excel

  • To lock the selected column, check the Locked checkbox from the Protection Tab and click OK.

Lock Columns in Excel

  • Choose the Protect Sheet option of the Review Tab.

To complete the process, follow either of the two steps described in the previous method:


Method 4 – Find and Lock the Formula Contained Columns

  • Follow steps AB, and of the 2nd Method to unlock the worksheet.
  • Go to the Home Tab that provides Find & Select options and click the Go to Special.

Lock Columns in Excel

  • From the Go to Special window select Formulas options and click OK.

Lock Columns in Excel

  • All the cells with formulas are selected (the Price column in our example).

Lock Columns in Excel

  • To make the column locked, follow the steps E to from Method 2.

Download Practice Book 


Related Articles


<< Go Back to Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo