Al Arafat Siddique
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.
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.
- Then right-click your mouse to open up the context menu and choose the Format Cells option.
- 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
- 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.
- Another window will pop up to confirm the password. Put the same password from the previous step and hit OK.
- 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.
B. Then in the Home Tab click the arrow from the Alignment
Another Way: From the Home Tab click the Orientation and choose Format Cells Alignment from the dropdown.
C. In the Format Cells window uncheck the Locked checkbox from the Protection Tab and hit OK. It’ll unlock the whole worksheet.
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.
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.
G. Another window will pop up to confirm the password. Put the same password from the previous step and hit OK.
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
- How to Select Every Other Column in Excel (3 Methods)
- Set Column Width in Inches in Excel (with Easy Steps)
- How to Set Excel Column Width to cm Unit (with Easy Steps)
- AutoFit All Columns in Excel (3 Easy Ways)
- How to Change Excel Column Width in Pixels (3 Easy Methods)
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.
- In the Format Cells window uncheck the Locked checkbox from the Protection Tab and hit OK. It’ll unlock the whole worksheet.
- In this step, select the Price column of our dataset and then right-click the mouse to select the Format Cells option.
- To lock the selected column we need to check the Locked checkbox from the Protection Tab and click OK.
- 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.
- From the Go to Special window select Formulas options and click
- As a result, we see all the cells with formulas are being selected (the Price column in our example)
- 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.
