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 accidentally deleting or editing 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.


Download Practice Book 

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


4 Methods to Lock Columns in Excel

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 with 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 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, summed subtotal, the tax rate, and the final total price. This column contains all the formulas required for calculation.

It may require 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.

Read More: How to Freeze Columns in Excel (5 Methods)

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 on 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.

Read More: How to Select an Entire Column in Excel (5 Quick Methods)


Similar Readings


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  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.
  • Password can contain numbers, strings, special characters and have no criteria.

Conclusion

Now, 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.


Further Readings

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo