How to Lock Columns in Excel (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

The feature in Excel to lock columns is very useful to protect a confidential file from an unauthorized person while sharing information. Even working by yourself it may happen to accidentally delete or edit a formula that is linked to a couple of other formulas. That’s why protecting your formulas or sensitive information locking feature in MS Excel is tremendously helpful.


How to Lock Columns in Excel (4 Easy Methods)

In this article, we’ll describe 4 easy methods step by step to lock columns in Excel. In our dataset, there is a list of products with their Unit Price and Quantity sold. The price of each of the items is calculated using the SUMPRODUCT function as the product of unit price and quantity. Moreover, the AutoSum function gives the Subtotal which is then added to the Tax Rate to get the Total price.

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, let’s follow the steps:

  • Click the Select All button at the top-left corner of the spreadsheet. It’ll select all the cells together.

Lock Columns in Excel

  • Then right-click your mouse to open up the context menu and choose the Format Cells option.

Lock Columns in Excel

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


1. Lock All Columns with Protect Sheet in Excel

We’ve already seen in the previous section that the Excel spreadsheet by default has all its cells locked with no effect. To make it active we need to follow the steps below:

  • 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. Then hit You can also select different options to allow user actions to the worksheet by checking the square boxes.

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

  • Now we click on any cell on the spreadsheet, it will show a warning.

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


Lock Specific Columns

In our dataset, we are going to lock the Price column that contains the individual price of each product sold, the summed subtotal, the tax rate, and the final total price. This column contains all the formulas required for calculation.

It may be required in many applications where certain columns need to be unchanged that perform the calculations with formulas. But still, users are allowed to put their values as input data for the formulas. The below methods are going to illustrate how to lock certain columns.


2. Lock Specific Columns by Using Home Tab 

We can lock selected columns using Home Tab options. Before that, we need to unlock all other columns in the worksheet. Let’s follow the steps below:

     A. First, click the upper-left button to select the whole worksheet.

Lock Columns in Excel

    B. Then 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

     C. In the Format  Cells window uncheck the Locked checkbox from the Protection Tab and hit OK. It’ll unlock the whole worksheet.

Lock Columns in Excel

     D. Then select the Price column of our dataset.

     E. Then 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

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

Lock Columns in Excel

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

Lock Columns in Excel

     H. Now we click on any cell in the Price column, it will show a warning.

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

     J. If we change any value in the Unit Price or Quantity column, it will make adjustments in the Price column accordingly.


3. Using Context Menu to Lock Selected Columns in Excel

Using the context menu is another way to lock certain columns. Here is an example below:

  • In the first step, click the upper-left button to select the whole worksheet. Then using right-click select the Format Cells option.

Lock Columns in Excel

  • In the Format  Cells window uncheck the Locked checkbox from the Protection Tab and hit OK. It’ll unlock the whole worksheet.

Lock Columns in Excel

  • In this step, select the Price column of our dataset and then right-click the mouse to select the Format Cells option.

Lock Columns in Excel

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

Lock Columns in Excel

  • In the final step, choose the Protect Sheet option of the Review Tab.

Now to complete the process, follow either of the two steps–

described in the previous method


4. Find and Lock the Formula Contained Columns

Using the Go to Special option of the Find & Select feature of Excel we can first select the columns with formulas and then lock them following any of the above two methods. Let’s follow:

  • Follow steps A, B, and C of the 2nd Method first to make the worksheet unlock.
  • 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

Lock Columns in Excel

  • As a result, we see all the cells with formulas are being selected (the Price column in our example)

Lock Columns in Excel

  • Now to make the column locked you can follow the steps E to J from Method 2.

Things to Remember

  • Don’t forget to preserve the password for unlocking.
  • Passwords can contain numbers, strings, and special characters and have no criteria.

Download Practice Book 

Download this practice workbook to exercise while you are reading this article.


Conclusion

Now, that we know the methods to lock columns in Excel, it would encourage you to use this feature more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles


<< Go Back to Columns in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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