How to Lock and Unlock Certain/Specific Cells in Excel

Last updated on July 30th, 2018

Locking cells, columns, and rows in excel give you the freedom to protect your data. After protecting a worksheet, by default, each and every cell are locked. It means that they can not be edited. But protecting different cells gives you the freedom to work on the unprotected cells. This article provides you the different techniques of how to lock cells in Excel. We will also see the other techniques of locking and unlocking columns and rows in Excel.

How to lock certain cells in Excel

  • At First, we will need to select the cells we want to lock.
  • After selecting the cells press on the right click button of the mouse.
  • Press on the Format cells option.
  • After the Format cells window open, press on the Protection option and click on the Locked option.

  • After clicking on the Review option on top, click on the Protect Sheet option.
  • A Protect Sheet window will come up. Tick on Select locked Cells and Select unlocked cells. Select the password on Password to unprotect sheet. Another dialogue box of Confirm Password will come up. Reenter the password. Here the password we select was 1234.

Locking cells

  • After this whenever you want to edit the desired cells of the excel you will see a warning box indicating that you can not edit the cells as it is protected.

  • For locking rows and columns just move the mouse courser to the row heading or column heading. For columns a downward arrow and for the row, a rightward arrow will come up. By clicking them on we can select the entire row or column. The rest procedure is same as before.

locking column

locking row

How to unlock cells in excel

Unlocking the cells in excel is much easier. Just click on the Review option on top, click on the Unprotect Sheet. Enter the password that you gave while protecting the cells. Unlocking the rows and columns of excel follows the same rule.

Note that Password protects your sheet effectively. If you don’t want to give any password while protecting a sheet it`s completely your choice.

Unlocking Cells

Locking cells with Excel VBA

There is another way of protecting cells, rows, and columns of excel using the VBA code. It`s a long process but it`s easy. The procedure is given below.

  • Select all the cells in excel.
  • On the Home tab under the Cells, section click on the Format option and then select format cells
  • A dialogue box of Format Cells will come up. On the Protection, option unselect the Locked

VBA lock cells

  • Down below the excel file where the name of your file is written, click on the right button of your mouse and select the View Code.

  • A new dialogue box of Microsoft Visual Basic for Applications will come up. In the white sheet associated with the dialogue box, write down the below code.
VBA code Lock

Click to view full image

  • In the code, we select the range of data from C7 to E11 and set the password 1234.
  • After this, Under the Debug option, you will find a Run Sub/UserForm (F5). Click on it.

  • Your Excel worksheet will come up along with a password window. Enter the password you wrote on the VBA code.
  • After this whenever you want to edit the desired cells of the excel you will see a warning box indicating that you cannot edit the cells as it is protected.

Note: Unlocking the cells after applying the VBA code follows the same unlocking method explained earlier.

How to freeze rows and columns in excel

Freezing is not a lock option but when there is a big data that you are analyzing and you want to work in a specific location of your excel file freezing the data is an effective way of doing that. The procedure is given below.

  • Let’s say you want to freeze your data so that it will show only the columns heading.
  • Just click any cells in row 4 as your headings are in row 3.
  • We select A4 The freezing will happen in the upper and lower side of the A4 cell.
  • On the View option at the top click on the Freeze Spans.

freeze cells

  • Different options will come up. You will select the Freeze Spans only.
  • After scrolling the excel sheet you will see only the headings of your columns will remain in the same position.

  • If you want to unfreeze it just go to the view option again and under the Freeze Spans click on the Unfreeze Spans.

  • If you want to freeze the first column only, click on the Freeze Spans under the View option in the top.
  • It will freeze the top first column.

freeze top column

  • If you want to freeze the top row only, click on the Freeze Spans under the View option in the top.
  • It will freeze the top row.

Freeze top row

Note: Unfreeze span will unfreeze all the freezing of the excel sheet.

Read More: How to Multiply Columns, Cells, Rows, & Numbers in Excel

Print frozen panes

Let’s say you have a big excel file where the excel file contains more than 1 page. You want to print the same column headings for each of the pages. The question arises how you do it. Right? The following procedure gives you the answer.

  • Click on the Page Layout option on top.
  • Just clicking the down arrow beside the Page Setup option a dialogue box of Page Setup will come up.
  • On the Sheet option of Page Setup, write $3:$3 on the Rows to repeat at top.

Locking head

  • If you click on the Print Preview option you will see that the headings will come up on both the pages.

How to Protect Cells in a Shared Worksheet

Microsoft Excel 2016 edition gives you an easy and effective way to share your protected workbook. You need to add some options to excel if you don’t have them. The procedure of adding them is given below.

  • Go to Quick Access Toolbar by navigating to File and then Option.
  • Select All Commands from Choose commands from list.
  • When you observe Share Workbook (Legacy), choose this click Add.
  • By following the same process when you find Track Changes (Legacy), choose this and click Add
  • Find Protect Sharing (Legacy), choose this and click Add.
  • Find Compare and Merge Workbooks, select this click Add.
  • Now, press OK.
  • After this, in Excel window, this four options will appear.

Access toolbar to add share workbook

After you do this you have the options to share your protected workbook with others providing a password. The procedure is simple. Click on top of the excel file where Protect and Share Workbook (Legacy) is written. Click on it and you will see a dialogue box Protect Shared Workbook will come up. Tick on the Sharing with track changes and give a password. Another dialogue box of Confirm Password will come up. Reenter the password. A warning box saying This action will save the workbook. Do you want to continue? Will come up. Press OK on it.  The shared protecting procedure is done.

If you want to undo the shared option, click on top of the excel file where Protect and Share Workbook (Legacy) is written. After clicking on it a dialogue box of Unprotect sharing will come up. Enter the password you gave earlier to protect it. After clicking OK you undo the sharing protection option.

Protect and sharing

If you want to undo the shared option, click on top of the excel file where Protect and Share Workbook (Legacy) is written. After clicking on it a dialogue box of ‘unprotect sharing’ will come up. Enter the password you gave earlier to protect it. After clicking OK you undo the sharing protection option.

Unlock sharing

Conclusion

Locking certain cells in excel gives you the freedom of securing your data from others. It helps you to prevent accidental deletion or to stop someone else from wrecking your data. As the amount of file transferring among different companies is huge, it is important to secure your data.

We hope this article about locking and unlocking certain/specific cells in Excel may help you. Cheers!

Read More…


Siam Hasan Khan on FacebookSiam Hasan Khan on Linkedin
Hello!

Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto.

We will be happy to hear your thoughts

      Leave a reply